VBA excel export to access

Question

Hi I am trying to write a macro that takes the user input from an excel form and adds it to a access table. Using the following code:

   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim wsh As Excel.Application
   Set cnn = "db.accdb.connection"
   Set rst = New ADODB.Recordset
   rst.Open "table", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
   With rst
   .AddNew
   .Fields("column1").Value = textboxvar
   .Update
   End With

with textboxvar previously defined. But it wont work and I don't know why.

This line: `Set cnn = "db.accdb.connection"` makes no sense. Do you have valid code there and are you trying to hide private information (like the location and password of your accdb file), or are you actually trying to go with that? — Erik A, May 22 '18 at 16:08
As @ErikvonAsmuth mentioned `Set cnn = "db.accdb.connection"` basically means nothing in this scenario. You most likely want to change that like to `Set cnn = CreateObject("ADODB.Connection")`. For future reference, the `.Open` method requires the second input to be a variable command object. — ccarpenter32, May 22 '18 at 16:16
Won't work means what - error message, wrong results, nothing happens? — June7, May 22 '18 at 18:33

score 0 · Answer 1 · answered May 22 '18 at 18:32

Example that declares, sets, opens connection:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; DataSource='C:\path\databasename.accdb'"
rs.Open "SELECT * FROM MyTable", cn, adOpenKeyset, adLockOptimistic

score 0 · Accepted Answer · answered Feb 22 '19 at 12:56

after searching for a long time. The top voted post in this thread already answers the question: Using Excel VBA to export data to MS Access table

This just has to be updated to work with Access 2016:

Public Sub UploadExcel()

Set cn = CreateObject("ADODB.Connection")
dbPath = 'type your database path in here

dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name

scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
cn.Open scn

ssql = "INSERT INTO TableName ([Field1], [Field2], [Field3]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

cn.Execute ssql

End Sub

VBA excel export to access

2 Answers2