Semi Automatic linking of front end to back end

[Ken Smith]

I am the administrator of a frontend backend application uisng Access 2003
that is installed in 45 locations. In most cases, the application is run from
the server rather than having the app on the end user desktop. Each
deployment of an upgrade, however, means linking the front to the back end.

I would like to move to a scenario where the application is on individual
PCs that are then linked to the back end automatically by the end users each
time they launch their application. I already have a table of offices with a
field detailing the path to the data file and envision the application
opening to a Startup Screen which asks the end user to identify his office
before he proceeds. The Command button to proceed would trigger the function
that does the relinking, reading the path from a field completed on the form
when the office is selected.

I will greatly appreciate help from a more exalted Access guru with the code
as my VBA is terribly weak and my managerial duties don’t allow me the time
or practice to really grasp and maintain sufficient knowledge to use that
great tool.

 

[Tony Toews [MVP]]

I am the administrator of a frontend backend application uisng Access 2003
that is installed in 45 locations. In most cases, the application is run from
the server rather than having the app on the end user desktop. Each
deployment of an upgrade, however, means linking the front to the back end.

I would like to move to a scenario where the application is on individual
PCs that are then linked to the back end automatically by the end users each
time they launch their application. I already have a table of offices with a
field detailing the path to the data file and envision the application
opening to a Startup Screen which asks the end user to identify his office
before he proceeds. The Command button to proceed would trigger the function
that does the relinking, reading the path from a field completed on the form
when the office is selected.

I will greatly appreciate help from a more exalted Access guru with the code
as my VBA is terribly weak and my managerial duties don’t allow me the time
or practice to really grasp and maintain sufficient knowledge to use that
great tool.

I will give you my solution but not the detailed code. In your case I
would keep a separate MDB on each users PC that stores the location of
the BE MDB. (I use an INI file for this purpose but for you an MDB
would work as well and be easier to use.) You can use the IN clause
in a query to fetch the value from the table. You don't want to
store this in your FE MDB as copying down a new one will overwrite the
data.

You could use the FileOpen API call to allow the user to choose where
the backend is the very first time the user runs the app. You would
then store the BE Server name, or drive letter name and path and BE
MDB name in the above mentioned MDB again using the SQL IN clause.

Then you can relink the tables using the code as at
Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

You'd also put code in that would check to see if a recordset can be
successfully opened against a table in the linked back end. If
successful then you assume that all the tables will work and you don't
bother relinking. If it fails then you need to do the relinking.

Finally you can use the Auto FE Updater to distribute the updates of
the FE at your 45 locations.
http://www.granite.ab.ca/access/autofe.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/