Dynamically Link Back End Database

[wirelessben]

How can I dynamically link the back end of an Access 2000 database?

The database has one front end and two back ends.

I want to give the user the option of choosing between two data sets residing on the same computer.

The link table manager will only set up static linkages to external tables.

 

[KenReay]

You will need a reference to DAO to use below code

Set Db = CurrentDb()
DoCmd.Hourglass True
lblProgress.Visible = True
For i = 0 To Db.TableDefs.Count - 1
lblProgress.Caption = "Relinking " & i + 1 & " of " & Db.TableDefs.Count
DoEvents
Set tdf = Db.TableDefs(i)
If tdf.Connect <> "" Then
j = InStr(1, UCase(tdf.Connect), "DATABASE=")
k = InStr(1, UCase(tdf.Connect), "TABLE=")
strConnect = Left(tdf.Connect, j + 8) & txtDataMDB & ";Table=" & tdf.Name
tdf.Connect = strConnect
tdf.RefreshLink

End If
Next i
lblProgress.Visible = False
DoCmd.Hourglass False
Set Db = Nothing

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[wirelessben]

Ken,

You are a super duper VB trooper. It works great. Thanks.

Ben