I have a linked table to an ODBC data source. This table is very large, 112k records and and 68 fields (7m data cells).
Currently I have a job that deletes local copy of table, and inserts all data from linked table to local table. This job takes over an hour to run. This makes me think there could be a more efficient way.
I am working around by connecting the table into an Excel file, refreshing the Excel file (about 5 minutes), and importing the table into the local table.
What is the difference in the querying between the Excel file and the complete insert? Is there a way to configure the update query to only look for changes or new rows? It seems like the ODBC has to download the full table before running any queries on it in Access.
Thanks for your help!