Import txt or xls file to SQL Server via VBA code

[Alika]

Hi all.
How can we import txt (dbf or xls) file to SQL Server from Access application using VBA code?
Thank you

 

[Pat Hartman]

It would be fastest to just run an append query that selects data from your Jet tables and inserts it into the SQL server tables. A VBA code loop would be much slower especially if you have more than a few thousand records.

 

[Alika]

It would be fastest to just run an append query ...

I have about a half of a million records. So you think it would be faster if I will first import data from txt file to Access table with do Cmd.transferText and then do some Append query to SQL Server table.
I'm not exactly sure what Objects do I use. I need to establish Connection:

Set Cnxn = New ADODB.Connection
Cnxn.Open "DRIVER={SQL Server};SERVER=ServerName;UID=AccessAccount;APP=Microsoft® Windows® Operating System;WSID=test PC;DATABASE=DBName", "AccessAccount", "password"

I will have to execute an Append query

strAppend = "INSERT INTO " & strServerTable & "( Field1, Field2, Field3 ) "
strAppend = strAppend & "SELECT F1, F2 FROM " & strAccessTable"

that has tables belonging to 2 different Engines. I'm not sure how to do it so Access will not confuse which table belongs to which object, and also which object/engine should execute the query.