Most efficient way to send data from Access to SQL Server?

[ry94080]

Hi all,

Every month, I need to archive large datasets (~400,000 records) from Access to SQL Server. Doing an append query via linked table is awfully slow.

I wanted to see what the most efficient / easiest way to do this?

Any help is appreciated.

[arnelgp]

maybe export first your table to a csv and then
create a script in MSSQL server to import the data from the csv file:

BULK INSERT YourTableNameFROM 'C:\YourFilePath\YourFile.csv'
WITH
(
    FIELDTERMINATOR = ',',  -- Specify the field terminator (comma in this case)
    ROWTERMINATOR = '\n',    -- Specify the row terminator (newline character)
    FIRSTROW = 2,            -- Specify the first row to import (if needed)
    DATAFILETYPE = 'char',   -- Specify the type of data in the file
    TABLOCK                -- Acquire a table-level lock
)

[ry94080]

maybe export first your table to a csv and then
create a script in MSSQL server to import the data from the csv file:

BULK INSERT YourTableNameFROM 'C:\YourFilePath\YourFile.csv'
WITH
(
    FIELDTERMINATOR = ',',  -- Specify the field terminator (comma in this case)
    ROWTERMINATOR = '\n',    -- Specify the row terminator (newline character)
    FIRSTROW = 2,            -- Specify the first row to import (if needed)
    DATAFILETYPE = 'char',   -- Specify the type of data in the file
    TABLOCK                -- Acquire a table-level lock
)

Thank you for the reply! I tried your suggestion but am getting this error when running the bulk insert via Pass Through Query.

Any ideas?

Screenshot 2024-06-10 064721.jpg

[Aussiebear]

Sorry but you need to be a little more helpful than that. The image provided is unreadable.

[ry94080]

Sorry but you need to be a little more helpful than that. The image provided is unreadable.

Sorry about that. I'll try attaching the image this time.Screenshot 2024-06-10 064721.jpg

[ry94080]

Sorry but you need to be a little more helpful than that. The image provided is unreadable.

I'm not sure why i can't upload in a readable picture. Looks like error due to type mismatch or invalid characeter for the specified codepage

[Aussiebear]

Images generally don't work, particularly when as in this case where the print is too small. Are you able to provide us with a summary of the error message.

[Aussiebear]

While we wait for an access guru to turn up, does this https://www.youtube.com/watch?v=ROT1ogVm8a8 give you any hints?

[ry94080]

While we wait for an access guru to turn up, does this https://www.youtube.com/watch?v=ROT1ogVm8a8 give you any hints?

Thank you for that. I would like to do everything out of MSAccess which it appears is done in the video via linked tables? However, linked tables are horribly slow with large datasets.

I'm hoping to go with the process @arnelgp suggested. Which is to export, which i was able to do via VBA. Then, run a Bulk Insert within MSAccess via pass through query.

Bulk Insert is bombing out on me, currently.

[ry94080]

maybe export first your table to a csv and then
create a script in MSSQL server to import the data from the csv file:

BULK INSERT YourTableNameFROM 'C:\YourFilePath\YourFile.csv'
WITH
(
    FIELDTERMINATOR = ',',  -- Specify the field terminator (comma in this case)
    ROWTERMINATOR = '\n',    -- Specify the row terminator (newline character)
    FIRSTROW = 2,            -- Specify the first row to import (if needed)
    DATAFILETYPE = 'char',   -- Specify the type of data in the file
    TABLOCK                -- Acquire a table-level lock
)

Are you able to provide a BULK INSERT example that is based on a txt file?