Updating Table

[sims.anderson2010]

I have a database which has a table with Patient Information. Another database has a table with the same information, but this table is constantly updated by users. Records changed, records added, records removed. I want to update the first database based on the second. I believe that I can do the update part and the add part, but am having trouble with the remove part. Any help? Unfortunately simply linking the table is not an option (the source is ODBC and extremely slow to load. I want to be able to update the first database based on the source once a day for our users to be able to use)

 

[sims.anderson2010]

Reading that, I realized that I was kinda unclear. Let me clarify.

First Databse:

• This is the source.
• Kept updated by various Users.
• Users add records, delete records, and update records to a table in this database

Second Database

• This is the database I want to be updated based on the first.
• It is linked to the first database table via ODBC (very slow link over WAN)
• I want to update this table once a day to reflect the info in the first database

In the past, this has been done by blowing away the table in the second database and copying the table from the first back into the second. This isn't always practical because if someone is in the database, it will not allow this (very hard to get a time where no one is in).

Any help would be great.

 

[GinaWhipp]

Let me start with the obvious question... Why? Is this your way of making a back-up? Side note... Access on WAN, not a good idea. Have a look at...

http://www.kallal.ca/Wan/Wans.html

I'm also getting the impression this database is not split. Is that so?

 

[sims.anderson2010]

Why?: We have an electronic medical record. The problem is it does not meet all of our needs so we have to improvise. There is a dump daily from the record to an ODBC table. This is access via secure WAN (not an option, no matter how stupid it may seem).

This is not a backup but our solution for filling holes the EMR does not cover. Due to the fact that access the ODBC table is so slow (can sometimes take 30-60 seconds to access tables), I have a table that I copy these records to once a day.

The way it was set up (before I took over) is this: Daily our local table is blown away and replaced by the data in the ODBC table. The problem with this is when someone is in the frontend (oh yeah, it is split also, FYI) the table is locked and cannot be deleted. I can update the table, delete and add records, I am just having trouble with the logic.

• How do I identify and delete records that are not in the ODBC table from the local table?
• How do I identify and add records that are in the ODBC table and not in the local table?
• How to I update differences in records that have changed (I'm pretty sure I can do this, just throwing it out so that if someone has a better idea than me, I can use it).

Hope that clears it up. Thanks

 

[GinaWhipp]

Okay, well let's think about this... Are the records being edited via a Form? If so you can use a Date/Time stamp On_Dirty to see when it was updated last with another Date/Time Field with a Default of Now() to tell when the data was dumped. That should help identify which records are changed.

Next thought, when you dump the data is there a Field that identifies that? Like say, Dump (Yes/No), then you could identify newly added ones from ones that came from the dump which would in turn help you identify which ones are local.

 

[GinaWhipp]

Oops, forgot to say YEP, clears it right up!

 

[sims.anderson2010]

Well, the records are not being updated by a form that I am able to edit (not access but another program). Our EMR is not editable by us (as far as forms). There is a data entry date for the records, I think, but that does not help if a record has been deleted (which happens occasionally). Then the data would not be in the ODBC table, but would linger in the local table.

 

[sims.anderson2010]

Scratch that, there is not a data entry date in the data that I am working with.

 

[GinaWhipp]

Well, I see you're not making this easy... and I am now at a loss for how. You need some field to identify which records come from where and when/if they have been updated in order to institute some sort of *tracking*. Let's hope someone comes along with and *outside of the box* idea! Sorry I couldn't be of more help!

 

[sims.anderson2010]

I had this idea, just not sure how to make it work

• An unmatch query to determine if there are records in the ODBC table (source) that are not in the local.
  • Use this to add to the local table.
  • Problem is there is only one criteria available in the wizard and I would like to check against three criteria (say admit date, patient number, episode)
• An unmatch query to determine if there are records in the local table that are not in the ODBC table.
  • Use this to delete from the local table.
  • Problem is there is only one criteria available in the wizard and I would like to check against three criteria (say admit date, patient number, episode)
• After that, an update query that updates all information that may have changed

 

[GinaWhipp]

The WIZARD only allows one but you can open in Design Mode and add additional Criteria. Have a look at...
http://office.microsoft.com/en-us/a...-records-without-matches-HA010205132.aspx#BM2

Though I would be careful there to double check that it's updating what you want.

Or you could just run three (or more) seperate queries and then run your update query. (Hmm, wonder why I didn't think of that? ) You could apply the same logic to the update queries. But that should work!

Just had another thought... you could run a UNION query and then run a Group By query which should return all the records that do not match. Once you have that subset you could use that as the basis for an UPDATE query to update the records that don't match.

 

[spikepl]

I don't understand the problem. You previously wiped the entire table, and were happy, but got stuck on not being able to delete the table with users in the system. So, what prevents you wiping all the records of the table, and filling it with records from your odbc source? Same end result as accomplished by all the querying ... but simpler.