General Networking/Lan/Wan/access datbase
Hi Scott, thanks for your reply to my previous question; I can't seem to be able to add a follow up question so have started a new one - apologies.
Well first I would not do it the way you are doing it. The reason is because you are storing redundant data in your table. You can use a composite key so you aren't storing the same info multiple times.
So let me review the process. The records are created by the paralegals in their local Access databases. They export to Excel and send the sheets to you. The Excel sheets can contain new records, updated records or existing records that have not been updated. You want to only import the new records, plus update existing records that have been changed since the last update.
Is that the process?
Let me know and I have some ideas for you.
Yes - that is exactly what the process is. I would be glad for some advice. Many thanks, Kary
Yeah, that's a limit for All Experts, that I hate.
Ok, so if you have the same Primary key set on your tables, then when you append, it will only append new records. So The first thing you do is run an update query. Here are the steps:
1) Create a query that filters for all records where the Update date is after the last Update date. (you will probably need to keep a record of the transmittal dates to be able to tell which are newly changed records)
2) Create an Update query that filters for only the records in the query in step 1 and updates each field
3) Run the Update query
4) Run an Append query
This will update only those records that have been updated and then append just the new records.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA