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
– Erik A, Commented May 22, 2018 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
– ccarpenter32, Commented May 22, 2018 at 16:16
Won't work means what - error message, wrong results, nothing happens? — June7
– June7, Commented May 22, 2018 at 18:33

Muse Horizon · Accepted Answer · 2019-02-22 12:56:41Z

0

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

answered Feb 22, 2019 at 12:56

Muse Horizon

161 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

June7 · Accepted Answer · 2018-05-22 18:32:37Z

0

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

answered May 22, 2018 at 18:32

June7

21.7k8 gold badges28 silver badges36 bronze badges

Collectives™ on Stack Overflow

VBA excel export to access

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related