General Networking/Lan/Wan/access datbase

Advertisement


Question
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.
You said:
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

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

General Networking/Lan/Wan

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

Most general questions on LANs, especially home networks. Some admin and design issue.

Experience

I've been an IT Professional for more then 16 years. I've accumulated a larege store of knowledge that allows me to answer a wide variety of IT related questions.

©2016 About.com. All rights reserved.