SQLTeam.com | Weblogs | Forums

ODBC Connection from Access to SQL Server

My company maintains a database in MS Access. For various reasons, I need to link certain tables from MS Access to SQL Server so that any updates in the Access database will be reflected in the linked table within SQL Server. I have used ODBC connection to link a table in Access to SQL Server. However, making any changes to the Access table takes much too long. For example, trying to append 300k rows of data to the linked table within Access takes 1-2 hours. Doing this same operation to a table which isn't linked takes about 5 minutes.

I would appreciate any help on improving the speed of this connection.

I have used the following steps:

  1. Ms Access External Data Tab --> ODBC connection --> "Link to the data source..."
  2. Machine Data Source --> New --> ODBC Driver 17 for SQL Server
  3. Select Target server and Target database --> Create connection --> Successful
  4. Append data from Source Ms Access Table to linked Table --> LONG wait time to append.

300k is a lot. Sowhy are some linked and not others?

If the linked tables are only for pushing data maybe you should look into ssis

300k is quite typical and does not usually cause any issue. As explained, current database is maintained in Access with no linked tables. I am wanting to link a table in Access to SQL Server.

SSIS would be good however I do not have licensing.

I have successfully linked Access to SQL Server but it is very slow. My question to you is how may I speed up the connection for append operations etc?

Are you trying to update that data from SQL Server? If so - there really isn't anything you can do to speed up the process.

If you are saying that updating the data in Access is now slow because you created the linked server, I am not sure how that would cause the issue.

Do you really need a real-time view of the data in Access? If not - then create a process in SQL to extract the data you need into local tables and schedule that to run once a day.

The data is updated regularly on Access, and I would like these updates to feed through to SQL Server.

A real time view would be ideal but a process which runs once a day would be good for now. Would you be able to provide any guidance on how to set up this scheduled process?

A daily update would be simple - I would use OPENROWSET. Truncate the destination table - and then insert into the local table from the remote table in Access.

For a better process - I would create a staging table, truncate the staging table - load data from Access. If that is successful, the next step/process would be to load the data from stage to final table. This allows for further processing if needed - for example, transforming data, validating data - incremental loads, etc...

Now that I think about this a bit more - is the table in Access, or is it in SQL Server and just available in Access as a linked table?

If the latter - then performing the update in Access is going to always take time. More information on how that is being done and what is being updated would be needed.

the "linked table" which is also the destination table is in SQL Server lets called "Table-B".
And the source table exist in Ms Access lets called "Table-A".

When I use the simple SQL 'INSERT' statement to append the rows from "A" to --> "B" it takes 1 to 2 hr.
Not sure if you any simple or easy to make this quicker.
We already applied the INDEXEX (P.K) on both tables

What process populates 300k rows in access? External process that does a huge data dump ? Doesnt seem to be a manual process

Where is the sql server located in relation to the the access ? Local network? Another city? Cloud solution?

I don't think there is any way to speed up this process in Access. The process is inserting a single row at a time - and that insert operation is sent across the network to SQL Server.

If the data is in Access - it might be better/easier to export the data to a file and use BCP to load the data to SQL Server. Or - setup a linked server in SQL Server to the Access database and pull the data from Access.