Import from CSV to a remote SQL server table using VBA Access

Question

We are migrating to Access 2013 from 2010 and from SQL Server 2008 to 2016. To import a CSV file, I used the Docmd.TransferText command and that did the import easily as it was to a local Access table.

Now I have issues when trying to import the data from CSV to a remote SQL Server table. I copied the file to the SQL Server box where the 2016 database is and used the below for the transfer.

str1 = "BULK INSERT Temp3 " & _
          "FROM 'C:\Bulk\FileExchange_Response_49636101_49.csv'" & _
          "WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', " & _
          "ROWTERMINATOR = '\\n', TABLOCK)".

This does not throw any error however, does not import the data.

Could anyone please shed any ideas to import the data from the CSV?

Thanks

The data does not get imported even if there are no errors.

Have you considered using Power Shell to import the CSV file instead of MSAccess? — Mark Kram
– Mark Kram, Commented Jun 10, 2019 at 21:17
So what does it do? I suggest you run it directly in SSMS directly rather than running it in VBA. VBA might be asynchronous. — Nick.Mc
– Nick.Mc, Commented Jun 10, 2019 at 23:03

S. MacKenzie · Accepted Answer · 2019-06-11 00:03:17Z

1

Looks like you got your data into Access. Try this:

Create a "Linked Table" to your SQL Server table. External Data > New Data Source > From Database > SQL Server > Link to the data source by creating a linked table > Create a DSN to your database, choose your table. You'll see a green icon in the tables with your SQL Server table there.
Create an Append Query. Create > Query Design > Close Show Table pop-up > Select SQL view at top left > use something like Insert Into MyNewLinkedTable (Field1, Field2) Select Field1, Field2 From MyAccessTable
Click Run on the ribbon above, then follow the prompts.

No VBA required!

answered Jun 11, 2019 at 0:03

S. MacKenzie

3351 gold badge4 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

Import from CSV to a remote SQL server table using VBA Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related