Linked tables

[Guest]

I have created a database application. I am an intermediate level user of
Access. Here is the scoop. We enter program data into very large statewide
database on a daily basis. the statewide database is managed by the state.
The state has developed an Access template to help local sites view the data.
The process to get the data into the template goes like this...we go the the
state database and create a zipped export file. We extract the files and
then open the local Access template and load the database with our data. Now
we have about 10 users who need to run reports from this Access template
database. I created a separate custom database for our program users,
linked all the tables from the loaded Access template, and created custom
queries and reports - very simple to use. Now here's the problems..my custom
database opens and the linked tables do not update automatically. We get a
message that the path is incorrect. We have changed nothing except reload
the state Access template with fresh data (daily). Must we update the links
( Tools, Database Utilities, Linked Tables manager) every time we open the
database? this may be several times in one day for each of the 10 users!
Second question, we have our custom database on a server and it seems to run
slow - any suggestions? that's all for now - thanks for your help - Becky

 

[Guest]

There are a few issues to deal with here. First, this Access "Templete" you
describe. Is this the one on the server that runs slowly? Is it also the
one you load the data into, or is it the front end that has to be relinked
each time?

The other issue regarding running slowly has to do with it being on a server
with 10 users sharing it. That is putting one huge load on your network
traffic. Here is a suggestion on how you might reconfigure it to make it
easier to use.

First, put a copy of the template (assuming that is the mdb with the queries
and reports) on each user's computer.
Put the data mdb that you import that state data into in a folder all the
users have permissions to.

As to getting the data into the mdb, I am unclear. Is the zipped file an
mdb file? If so, how do you move the data from the zipped file into the back
end (data) mdb? Is the old data deleted and the new data replacing it or is
the new data appended to the old data?

What needs to happen is that each user's copy should be linked to the mdb
with the data. The data in the zipped file should be imported into your back
end. If you can give more detail on the data import process, I can give you
a better answer.

 

[Guest]

To answer your question - i created a custom template for our users - you are
right, it acts like a front end that has to be relinked each time! also,
when we relink the tables on the server and another user opens the database,
we get a message that the path is incorrect. We haven't moved anything all
we did was relink the tables. Please help. Thanks, Becky

 

[Guest]

Your problem is how you are setting the path. What I mean is, on your
computer, the folder the data is in may start with E:\ and on Fred's machine
it may be G:\
and Nancy (she's weird anyway) is mapped as R:\

So when you save you links, the front end is looking for E:\ . That is fine
until old weird Nancy opens the mdb and remapps to R:\. So if you exit the
database and come back in you think it has lost it's mind again.

This is part of the reason each user should have their own copy. It won't
happen in this case. There is a way; however, to accomplish this mapping so
it doesn't matter what the user's drive mappings are. It is called UNC
paths. That is, you don't use a drive letter, you use the network mapping.
It will look something like this:
\\MyServerName\SomeFolder\ALowerFolder

All the computers on the network can understand this, so if you do your
mapping this way, all the computers will find the data with no problem. If
you don't understand this or you don't know your network paths, see your
network admin person and they can give you the UNC path.