0

I would like to transfer my Excel table directly to MS Access with VBA.

This solution Using Excel VBA to export data to MS Access table only adds the range of data.

Is it possible to export Excel table to MS Access table directly?

Every time I run this code, it duplicates the data. I can't edit the sCommand into Update statement it gives me an error.

Sub test()
dbWb = Application.ActiveWorkbook.FullName
dsh = "[" & Application.ActiveSheet.Name & "$]" & "Data2"  'Data2 is a named range

sdbpath = "C:\Users\myname\Desktop\Database2.mdb"
sCommand = "INSERT INTO [main] ([dte], [test1], [values], [values2]) SELECT [haha],[test1],[values],[values2] FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

Dim dbCon As New ADODB.Connection
Dim dbCommand As New ADODB.Command

dbCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbpath & "; Jet OLEDB:Database Password=;"
dbCommand.ActiveConnection = dbCon

dbCommand.CommandText = sCommand
dbCommand.Execute

dbCon.Close

End Sub
Community
  • 1
  • 1
bertot
  • 17
  • 4
  • Can you add the code you did try. – Nathan_Sav Jan 25 '22 at 11:02
  • In Access, try linking to the table by getting external data. It will launch a wizard dialog to walk you through. No VBA needed. – bugdrown Jan 25 '22 at 11:08
  • @bugdrown The data I needed to be ms access table is being encoded only using excel. So I need to convert it every time to ms access. I need the ms access table format to load it in vb.net application. – bertot Jan 25 '22 at 11:42
  • 1
    What if you ran a `DELETE` query on the target table before you run `dbCommand.Execute`? – bugdrown Jan 25 '22 at 12:26

0 Answers0