Using a query with a moving table

  • Thread starter Thread starter glen.welsh
  • Start date Start date
G

glen.welsh

I am a complete SQL novice and I know there has got to be a way to do
this that is probably very simple. I have a database that has a couple
of table/query data sources. If I keep the tables in the same location
on my computer it works beautifully because obviously the path does not
change. I need to create a dynamic path name so when I distribute the
database, other users will have the same functionality. Thanks for any
feedback.
 
I would like to add this for clarification:
I have two database objects, one holds the data (DB1), the other was
implemented as a modifier of the original data to create a more user
friendly format (DB2). DB2 has the table/query data source and calls
DB1 for that information. My plan is to keep these two DB objects in
the same folder and provide instructions for others to do this as well.
 
What you are saying is that DB1 is the backend and DB2 is the front end?

Are you talking about users at different locations not on the same network?
If this is true, then what you are saying is okay with one exception. If
they go in the same folder, it should not be on a network folder. This will
degrade performance.

If you are talking about multiple users sharing the same data (Back End),
then the back end should go on a network server and each user should have a
copy of the front end on their own computer.

As to managing where the back end is. There are several issues. In the
first example above, it is not really a problem. You will have to acquire or
create a relinking routine that will allow the user to tell the app where the
data is and the app will relink to the new location.

If you are using the second case, the same would be true, because each user
has different drive mapping. If you are going to install it on a network for
multiple users and you don't care to have them relink their own, then you
will have to use UNC drive mapping.
 
If I understand your issue, you should probably set up your external data
sources as linked tables.
As long as your queries are based on those linked tables, you only need to
worry about making sure that the links are correct.

You could use the code found at
http://www.mvps.org/access/api/api0001.htm (1-Windows Open/Save File Dialog
API)
and
http://www.mvps.org/access/tables/tbl0009.htm (2-Relink tables via code)
to allow users to (1) "browse" to the external db files and (2) relink to
them when the location changes, or (maybe) even better, check the validity
of table links when the db is opened and only prompt the user for a new
location if the link is bad.

HTH,
 
I would like to add this for clarification:
I have two database objects, one holds the data (DB1), the other was
implemented as a modifier of the original data to create a more user
friendly format (DB2). DB2 has the table/query data source and calls
DB1 for that information. My plan is to keep these two DB objects in
the same folder and provide instructions for others to do this as well.
Click to expand...


If you can determine where the back end mdb file resides,
you can reset the linked table's Connect property to that
path. These may (or may not) be relevant to your situation:

http://www.mvps.org/access/api/api0001.htm
http://www.mvps.org/access/tables/tbl0009.htm
 
Thank you for your feedback. I will see what I can do with the code
examples provided.
 
Back
Top