Connecting to user-specified backend database

[tommeekers]

I'm currently working on an Access 2000 project which has to allow multiple users to access the same db. I will be using MOD2000 to distribute my frontend (forms, reports, queries, ...) to my users.

However, I want to make it so that when a user starts the application, it checks to see if the path to the database has been entered. If not, it has to open a pop-up which asks to specify the db location. Can this be done ? Can I link the frontend to the backend using VBA ? If yes, how do I do it and where do I store the path to the db so that the users will not have to re-enter it every time they run the application ?

 

[alvechurchdata]

I store the database name and path in a small Config table in the front end. Use the DAO OpenDatabase method to open the database then Connect to link tables into your frontend.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[tommeekers]

Any chance you could show an example ?
I presume I would have to read the config table first, check for the database path and then switch to the backend database. How do I make the switch between the databases ? And what do you mean by "link tables into my frontend" ?

 

[lupins46]

You will find some code to relink tables here:

http://www.mvps.org/access

Search for 'relink tables'.

However, the code is all embracing and I find it very slow in some situations. The code in the Access 97 Developers Handbook is much faster but only deals with a single backend file. However, I don't know of a public source for this code since it was replaced by the current code in the above link.
Unless the user is likely to use a different backend every time they startup, there should be no need to ask them every time. The code I referred to checks to see if the current links are still ok and only asks if they are not.

 

[tommeekers]

I'm getting pretty confused. Right now I'm starting from 2 .mdb files. One has the forms and reports and such, the other one has the tables. Do I need to set the links in my frontend and then use code to check if these links are still valid ? Or do I need to use code to create the links from scratch based on the database path ?

I know how to link the tables, but when I move my database somewhere else, I'm stuck. I don't know how to tell my frontend that the database has moved and the links should be updated.

 

[tommeekers]

I tried this code

http://www.mvps.org/access/tables/tbl0009.htm

But when the database is located at the original location it returns a compiler error and points me to this piece of the code.

Function fIsRemoteTable(dbRemote As DATABASE, strTbl As String) As Boolean
    Dim tdf As TableDef
    On Error Resume Next
    Set tdf = dbRemote.TableDefs(strTbl)
    fIsRemoteTable = (Err = 0)
    Set tdf = Nothing
End Function

When I move the database to a new location, hoping to be asked to specify the new location, I just get a 'database not found' error. Does anyone know what I'm doing wrong here ?

 

[stuandwil]

you have added the getopenfilename code off that link page also. This is required to bring up the browse file and pick BE.