0

Good day I would like to transfer my excel table directly to ms access with vba.

I encountered this solution Using Excel VBA to export data to MS Access table

however it only adds the range of data. Is it possible to export excel table to ms access table directly?

Edit: I used this code but every time I run the macro, it duplicates the data. I cant 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
Pᴇʜ
  • 53,845
  • 9
  • 46
  • 68
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