Linked table sync problem

[tjfdownsouth]

I have two access databases one that contains data and another that contains
linked tables and forms. The issue is that I have a service that updates
information in the data tables but it does not show up in the other databases
linked tables. If they enter new information through the linked tables then
the information in the data tables is overwritten with the new data like it
wasn't ever there. Is there a way to get Access to synchronize this data
better?

Tim

 

[Klatuu]

Your post is a little confusing. When you refer to "Linked" tables in the
database with the forms, are the links to the other mdb that has the data?
Linked tables in the database with the forms have no data in them. A link is
nothing more than a pointer to where the table with the data actually is.

How are you doing these updates?

 

[tjfdownsouth]

You are correct one is our data .mdb and one contains the forms and links.
Basically we have two ways to enter data through the forms or through a PDA.
The PDA using a web service enters data into the data mdb and I can open the
the data mdb and see that the record is there. but if I open the link in the
form database the information the pda entered doe not show up. If I enter
information using the form database, after it updates the information from
the pda is no longer in the data mdb.

Tim

 

[Klatuu]

Check to be sure the PDA is upating to the same table names that you are
linked to.
If the data is in the table and you open the table from the front end data
base, you should see it. If you don't see it, it is not there.

 

[tjfdownsouth]

I don't understand why it shows up in the table in database 1 (data) but not
in the linked table for database 2 (forms). I had the pda insert the data
and then went to database 1 and opened the table and the info was there as it
should be but when I open database 2 and looked in the linked table which
should be a view of database 1 the info is not there.

I wasn't sure if the forms may be doing some temporary locking in the
background and not allowing the data to be committed.

Tim

 

[Klatuu]

Did you check the table names the data is in against the table names the
front end is using in it's links?

 

[tjfdownsouth]

Yes the names are the same the only difference is where the info comes from.

Tim

 

[Klatuu]

This doesn't make any sense.
When you try to look at the data from the front end mdb, is the PDA still
attached?
Have you tried closing then reopening the front end?

Sorry to ask such obvious questions, but I am at a loss.

 

[tjfdownsouth]

I have tried it either way, closing one or both. The pda just inserts data
and disconnects and does not keep the connection open. I never noticed a
problem because the PDA had always been entering data into database 1 where
people were working, but recently we started working in a table that was used
with database 2.

The overall process is the PDA contacts a web service that enters data into
a SQL database. Every 15 seconds a windows service takes the qued data and
transfers it into Access. I can watch the service process the data and
insert it into database 1. I can then open database 1 and the row containing
the information is there. But if I open database 2 and open the linked table
for viewing the row with the information is not there. If I proceed to enter
data into database 2 when I open database 1 again the row of information from
the PDA is no longer there it is replaced with the information from database
2.

I know this sounds crazy but this is one of those weird operations that has
to be this way. The other option I looked at was updating database 2 from
the web service but now I have a problem with the table being linked with a
mapped drive and not a unc.

Thanks,
Tim

 

[Klatuu]

I don't know what else I can do to help, but I will be around for at least
moral support. Have you tried refreshing your links before you try to see
the data?

 

[tjfdownsouth]

Well I figured it out. The network administrator has the file replication
system turned on for the share that the mdb files are on. I was looking
through a unc path and the user's are looking through a mapped drive using
dfs. That is why when we were both editting whoever was in the database last
got to keep their changes. At least now I know who to blame.

Thanks for your help Klatuu,
Tim

 

[David W. Fenton]

I don't understand why it shows up in the table in database 1
(data) but not in the linked table for database 2 (forms). I had
the pda insert the data and then went to database 1 and opened the
table and the info was there as it should be but when I open
database 2 and looked in the linked table which should be a view
of database 1 the info is not there.

Are you sure you're actually linked to database 1 and not some other
database, a third one?

It could also be a corrupt index, which if you have a SORT ORDER or
FILTER saved with your tables in the front end, could cause records
to not be displayed.

Try compacting the back end, then compacting the front end. This may
resolve the issue.

If it doesn't, try deleting the links in the front end and recreate
them from scratch.

 

[David W. Fenton]

Well I figured it out. The network administrator has the file
replication system turned on for the share that the mdb files are
on. I was looking through a unc path and the user's are looking
through a mapped drive using dfs. That is why when we were both
editting whoever was in the database last got to keep their
changes. At least now I know who to blame.

That makes no sense to me at all, as the file system resolves the
UNC path for mapped drives when requests are made.

I don't use mapped drives for accessing network resources. It makes
no sense at all to me to do so, and I've trained my clients how to
avoid it since I first started provisioning NT servers back in the
90s.

Drive letters are just a pain, unless you've got some horridly
out-of-date software that can't deal with UNC paths. I'd rather get
rid of such software than implement mapped drives.

 

[tjfdownsouth]

It was actually a team screw up. The drives are mapped throught the dfs but
when I set up my service I used a unc to the computer that is supposed to
contain the database 1. After I changed my service to use the dfs map
everything is working, just after a little research I see everywhere that
databases should not be included in a dfs because of the lack of distributed
locking.

Tim