Solved Replace Table but maintain Queries

[prich0905]

Team,

I get a lot of data from a different system, and update weekly. It comes to me in an excel sheet. I put this into Access and build queries to isolate the information I want, but when I replace the table with the updated information, the queries and reports do not work anymore. What is the work around to this?
Thanks,

-Phil

 

[plog]

Don't delete your tables--delete the data.

Split out a copy of a good working database into 3 new blank databases:
1- Data. Copy in the tables.
2- Reporting. Link to the tables in 1 and then copy in queries, reports, code
3-Processing. Link to the tables in 1.

Now, take 3 and build a process to replace the data in the tables. Build TRUNCATE queries to clear out the data. Build a process to import your spreadsheet data into temporary tables. Build queries to validate the imported data (right counts, right dates, test for invalid records, etc.). Build APPEND queries to move the data from the temporary tables to the actual tables.

Once that's done. 2 will work with the new data. Also, when you want to archive it, just copy 1 to a folder. If you ever need it, just move it back and 2 will be ready to go on that data.

 

[prich0905]

Plog,

That's great,
Thanks!!

Don't delete your tables--delete the data.

Split out a copy of a good working database into 3 new blank databases:
1- Data. Copy in the tables.
2- Reporting. Link to the tables in 1 and then copy in queries, reports, code
3-Processing. Link to the tables in 1.

Now, take 3 and build a process to replace the data in the tables. Build TRUNCATE queries to clear out the data. Build a process to import your spreadsheet data into temporary tables. Build queries to validate the imported data (right counts, right dates, test for invalid records, etc.). Build APPEND queries to move the data from the temporary tables to the actual tables.

Once that's done. 2 will work with the new data. Also, when you want to archive it, just copy 1 to a folder. If you ever need it, just move it back and 2 will be ready to go on that data.