Import from CSV to a SQL table on a remote server using VBA Access (1 Viewer)

NewbieA

New member
Local time
Today, 09:53
Joined
Jun 10, 2019
Messages
2
Hello All,

We are migrating to Access 2013 from 2010 and from SQL 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 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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. Just curious... Where did you use this code? In Access or SSMS?
 

NewbieA

New member
Local time
Today, 09:53
Joined
Jun 10, 2019
Messages
2
HI I used the bulk insert statement on SSMS to see if it works.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:53
Joined
Sep 12, 2006
Messages
15,613
I would import to a local access table as before, and then append the data to the sql server table (which I presume is linked to your database). Probably no different to your current process.
 

Users who are viewing this thread

Top Bottom