Reference External DB as variable

[youngsc]

Haven't had any luck searching for a solution but then I'm not sure exactly what to call it.

I have several queries created that reference an external database using the 'IN' statement. That works fine however what I'd like to do is setup the path to that external table in either a global variable or a table that I can then reference in the queries. This would avoid a situation where if the external database moves then I can, in one spot, change the path. Otherwise it'd be a matter of updating it everywhere dozens of times.

I've tried setting the path a in table that I then reference in the query like '[tablename].fieldname' but that threw an error. Any ideas on how to accomplish this or if it's even possible? TIA

 

[bobsan42]

yes it is possible - but not quite like you approach it AFAIK.
to refer to a table you must have a connection to it.
The connection string includes the path to the database file.
I was recently doing something similar.
To accomplish this task you must change the connection string for the tableDef object and refresh the link.
but this must be done in the DB - cannot do it in the query (again - AFAIK).
What I did was: setup a start up procedure to check if all tables can be opened. If any failed - ask for a new location (and possibly password) of the file. Then change the connection string and check again.
the sub is something like:
Checking:

    Dim rsCheckLink As DAO.Recordset
    Dim tdf As TableDef
    Dim db As Database
    For Each tdf In db.TableDefs 'looping through all tables

        If Len(tdf.Connect) > 0 Then 'Linked table
                DoCmd.SetWarnings False
                On Error Resume Next
                Set rsCheckLink = db.OpenRecordset(tdf.Name) ' OPEN TABLE
                If Err Then ' COULD NOT OPEN TABLE - link probably broken
                     ..........

Fixing:

Dim db As Database
Dim strConnect As String
Dim ConnectDb As String
Dim strCon As String

Set tdf = db.CreateTableDef(tdf.Name, dbAttachSavePWD, tdf.Name, strConnect)
db.TableDefs(tdf.Name).Connect = strConnect
db.TableDefs(tdf.Name).RefreshLink


strconnect is something like: MS Access;PWD=;\\10.56.56.1\...\....\xxxxxxxxxxx.accdb

<tbody>
</tbody>

I can try to get in some more details later if you cannot figure the rest.
Cannot post my code completely - it will confuse you and probably will not work without a lot of adjustments.

 

[youngsc]

Ah, that makes sense.

The purpose behind what I'm doing is to save to a backend only storing history, i.e. audit trail. It wouldn't be viable to ask the end user for input if something changes as I'm probably the one that did it. What I think I'll do is revise my thinking and create a single table, rather than a duplicate table, in that dedicated backend that instead saves oldval, newval, form & field names, etc.

 

[bobsan42]

Well, that's just the way I designed it - you can always do it w/o user input - like you said - you can always take the new location from a table or a text file.