Linked table not in sync

[roulette6]

Hello. I'm hoping someone can explain this. I suspect the answer is straight forward but I can't figure it out myself, and I've found no answers online.



Yesterday my work's IT department moved a network drive to another location. The old location still worked as read-only. I have two Access databases: One contains the tables and the other contains the forms and queries, and has links to the tables in the other files. In order to avoid having to use the linked table manager every time I move the databases, I use the \\network\address notation instead of drive letters, such that the database with the linked tables was pointing to the old, read-only location. I deleted the linked tables and relinked to them using the new location, and noticed that there was a discrepancy of 30 records I had entered through the forms not showing up once I linked to the tables in the new location.

It was clear that the database I use to enter data never actually inserted those records into the linked tables, but still acted as though it had in that it showed all records. Once I deleted the linked tables and linked to them again, those records completely disappeared. What's odd to me is that I'd entered these data into the database over a week ago. Why does Access take so long to synchronize the data between the linked tables and the actual tables? Are there any settings I can change to make sure the data are synchronized when I exit the application? Is it the ODBC refresh interval? If the default is 25 minutes (1500 seconds), does it mean that the data are never synchronized if I don't keep the database open for at least that long?? This is really puzzling.

I was able to go back to the old network location and get the old database showing all the records it's supposed to have. I had it import the linked tables as local tables and it looks like the local tables contain all records. This is not an ideal solution, but at least it seems like I recovered all the data.

[ItsMe]

...I was able to go back to the old network location and get the old database showing all the records it's supposed to have...

So the DB you thought you were appending records to was different from the one you appended rerecords to.

[roulette6]

So the DB you thought you were appending records to was different from the one you appended rerecords to.

No, it's the correct database. The problem is that they were out of sync, so when I deleted the linked tables and relinked them using the correct location, the out-of-sync data disappeared completely. Normally I would've been able to just update the path of the linked tables using the linked table manager and probably avoid the problem altogether, but the problem is that the old, read-only location still works so the linked table manager never gave me the option to update the new location. Is this making sense?

[John_G]

When you re-linked the tables, might you have inadvertantly linked to an older backup copy of the data? It's easy to do, I've done it myself.

It's very unlikely, but could the IT department have messed something up in the copy?

[roulette6]

When you re-linked the tables, might you have inadvertantly linked to an older backup copy of the data? It's easy to do, I've done it myself.

It's very unlikely, but could the IT department have messed something up in the copy?

So I'm looking at a snapshot of the file and it looks like I was linking to an old location after all. There have been two name changes to the network folder. It looks like after the first name change, everything worked correctly, so I never updated the location. This time, however, I got an error because the old location was read-only, so I updated it now. So if I was moved from network locations A, B, and C, up until recently I was still linking to location A and didn't realize it until now because I thought I was linking to location B.

I have the database split into different files because I also run Word mail merges using the same queries. I think I'll just use drive letters to link to the data so that if there's a location change the error will be obvious. If IT had disabled the old network locations this never would've happened.

[John_G]

"..the linked table manager never gave me the option to update the new location."

The linked table manager has an option box "Always prompt for new location". You have to select that box if the old location is still valid.

[roulette6]

"..the linked table manager never gave me the option to update the new location."

The linked table manager has an option box "Always prompt for new location". You have to select that box if the old location is still valid.

Thank you. I didn't realize this was the point of that checkbox, although it seems obvious in retrospect.