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:
- Ms Access External Data Tab --> ODBC connection --> "Link to the data source..."
- Machine Data Source --> New --> ODBC Driver 17 for SQL Server
- Select Target server and Target database --> Create connection --> Successful
- Append data from Source Ms Access Table to linked Table --> LONG wait time to append.