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!
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!