Consulting

Results 1 to 10 of 10

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

  1. #1
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location

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

    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.


  2. #2
    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
    )

  3. #3
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location
    Quote Originally Posted by arnelgp View Post
    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

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    Sorry but you need to be a little more helpful than that. The image provided is unreadable.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location
    Quote Originally Posted by Aussiebear View Post
    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

  6. #6
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location
    Quote Originally Posted by Aussiebear View Post
    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

  7. #7
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    While we wait for an access guru to turn up, does this https://www.youtube.com/watch?v=ROT1ogVm8a8 give you any hints?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location

    Unhappy

    Quote Originally Posted by Aussiebear View Post
    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.

  10. #10
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location
    Quote Originally Posted by arnelgp View Post
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •