use code to link to external database

[nafr]

I have a MDB front end which contain sall the forms/queries/macros and which
links to another MDB which contains all the tables. Nothing very clever.

I update the front end on my laptop quite frequently and need to then copy
it to the server. I have a backend on my laptop as well to allow me to test
the frontend and because I also link the front end ot some tables on my PC.

Because I need to be able to link to 2 or 3 differnet backend DBs, it is a
real pain having to use the linked table manager to recreate the links to all
the tables. I used to be able to do this in code in Access97 but the code no
longer worked when I upgraded to Access 2003.

Anybody got any clues?

 

[Alex Dybenko]

Hi,
i dont have a sample of such proc around, by idea is simple - you make a
local table, where you list all tables to relink and in other field some
kind of code, showing to which db it should be attached.
then you take a proc to relink tables from www.mvps.org/access and modify it
so it read table list from tis table, and ask new path each time new code
comes with next table.
HTH

 

[Allen Browne]

There is a module in solutions.mdb (sample database) that checks if the data
is available on startup. If not, it pops up the File Open dialog to let you
locate the data.

Download from:
http://msdn.microsoft.com/library/officedev/bapp2000/mdbdownload.htm

 

[Carol Grismore]

Sub AutoReconnect()

Dim db As Database
Dim tdf As TableDef
Dim cnt As String
Dim X As Integer
Dim rtn As Integer

rtn = SysCmd(acSysCmdSetStatus, "Reconnecting database objects...")

Set db = CurrentDb

For X = 0 To db.TableDefs.Count - 1

Set tdf = db.TableDefs(X)
cnt = tdf.Connect

' Already connected to the requested database.
strCurrent = Mid(cnt, 10, 6)
If strCurrent = strDSN Or strCurrent = (strDSN & ";") Then GoTo NextX

' Refresh all connections.
If tdf.NAME Like "dbo_*" Then
cnt = "ODBC;DSN=" & UCase(strDSN) & ";SRVR=FRED;DB=" &
LCase(strDSN) & ";"
tdf.Connect = cnt
tdf.RefreshLink
End If

Set tdf = Nothing
NextX:

Next X

Set db = Nothing

End Sub

 

[nafr]

Carol

Thanks for the code. Not sure how it works but will investigate further. I
dont know if the following will clarify things further or not.

I am hoping to be able to link a numbe rof taboles to differnet backend
MDBs. The location of the seperate MDBs will also change depending on whether
I am on the network at work, a satelite office with its own server or at home
on the laptop.

The front end is the same mdb in all three situations.

I had hoped to be able to offer the user (myself anfd others) the option at
start up to indicate their current location and then use code to refresh the
links as appropriate.

If your code is the right answer then thanks, but if not any other clues?

Thanks

Nick