Copy Data From Linked Tables to Local Tables (1 Viewer)

davidb88

Registered User.
Local time
Today, 12:08
Joined
Sep 23, 2013
Messages
62
I've been struggling with this for some time now and have tried multiple solutions. I have about 20 linked tables to Excel and would like to have those automatically copy over to local tables, replacing the data that existed from the prior month in the local tables. Reason being that I have other calculated fields that are done in Access, so I don't want to make changes to the linked tables or "source data."

I've tried a regular INSERT ITO FROM SQL query which works, however, it takes quite a bit of time for one table, let alone 20 tables, each with about 18K records but that number is expected to grow. I then tried to make a copy of the linked tables and then make a copy of those ,but that takes just as long as the SQL query and it's not as clean of a process. I also tried a DAO recordset, however the VBA I used only would copy the first record from the table.

I suspect that the recordset option is the quickest way to copy all data from one table to another, but can't find any other threads that have any examples so figured I'd start my own. If anyone can provide any simple examples of how I can get a recordset from one table to transfer to a recordset of another table I'd be very appreciative and you'd save me from hours more of trial and error.

Thanks!
 

Ranman256

Well-known member
Local time
Today, 13:08
Joined
Apr 9, 2015
Messages
4,339
you can either run an append query, or a Make Table query.
The append gives you more latitude for altering data before it appends.

append a single record using the record key.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:08
Joined
Oct 29, 2018
Messages
21,358

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:08
Joined
Feb 19, 2002
Messages
42,971
Both the make table and the delete/append methods bloat the BE and so should be avoided whenever possible. If the data is a full replacement, then a better solution is to create a second BE and import the data into that BE. Your import process would delete the previous version of the temp_BE and create a new one. Then it would import the 20 tables. As long as you keep using the same db and table names, your code and queries will continue to work UNLESS you delete the current temp_BE and compact the FE before replacing it. That will break your queries and relinking will not fix them. They would need to be fixed manually.

The temp_BE doesn't suffer from bloat because you create a new one each time you do the import.
 

isladogs

MVP / VIP
Local time
Today, 17:08
Joined
Jan 14, 2017
Messages
18,186
I suspect that the recordset option is the quickest way to copy all data from one table to another
Definitely not true. when you loop through recordsets, updates are done row by agonising row (RBAR) and for large recordsets that can take a very long time indeed. By comparison update queries or SQL statements are effectively all done at once. For large numbers of records and a well designed query, that can be several hundred times faster.

As @theDBguy has mentioned, I have a series of speed comparison tests both on my website and at this forum to back up the comments above.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:08
Joined
Feb 19, 2013
Messages
16,553
the other issue you may have is lack of indexing - linked excel data is not indexed. I would take Pat's advice, import them into a separate temporary BE and index them as required, then use update and append queries (or investigate what is called an upsert query) to update your local tables.
 

Users who are viewing this thread

Top Bottom