dynamic linked table manager

[rodneyb]

I have a client who has a front end (access 97) and a backend file which is linked via the linked table manager. He now wants (for various reasons) multiple backend files that he can dynamically link to from the same front end, say using a command button from the front end interface, to display and select the path of where the new backend is located.

How can I code this so that when he clicks on this button the front end will disconnect from the current backend and link to the new backend that he selects?

Thanks.

 

[Oldsoftboss]

The northwind db has a sample of how to link a back end.

 

[rodneyb]

Thanks for the reply OldSoftBoss.

I have had a look at the Northwind db but don't know where to look exactly for this piece of code - can you please help me out by telling me specifically where to look - or just paste the code here.

Much appreciated.

 

[Oldsoftboss]

I did PM you, here is a cut down version of what I use.
Hope it helps.

Dave

 

[rodneyb]

Thanks OldSoftBoss,

the code you provided was spot on - it was just what I needed.

Thank you so much.

 

[Treason]

I use something different... Assuming all backend databases are in the same folder.

I make a table called tblDefs with 2 fields
tblNames & DBName

tblNames should have the table name &
DBName should have the database name

Then I use this code to re link to the backend in a new folder

Public Function RemoveLinks()
On Error Resume Next

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblDefs", dbOpenDynaset)

'Erase all links
rst.MoveFirst
Dim Var1 As String
Do While rst.EOF = False
Var1 = rst("tblNames")
DoCmd.DeleteObject acTable, Var1
rst.MoveNext
Loop

RefreshDatabaseWindow

End Function

Public Function ReLink()
Dim strFolderName As String
Dim rst As Recordset
Dim dbsTemp As Database
Dim tdfLinked As TableDef
Dim strConnect As String
Dim strSourceTable As String
Set dbsTemp = CurrentDb

strFolderName = BrowseFolder("Select the folder on your server where Skedula was installed")

If strFolderName = "" Then
MsgBox "Invalid Entry", vbExclamation
Exit Function
End If

Set rst = dbsTemp.OpenRecordset("tblDefs", dbOpenDynaset)
rst.MoveFirst
Do While rst.EOF = False
'starter:
strConnect = strFolderName & "\" & rst("DBName")
strSourceTable = rst("TblNames")
Set tdfLinked = dbsTemp.CreateTableDef(strSourceTable)
tdfLinked.Connect = ";DATABASE=" & strConnect
tdfLinked.SourceTableName = strSourceTable

dbsTemp.TableDefs.Append tdfLinked
rst.MoveNext
Loop

RefreshDatabaseWindow
End Function

you also need this API to get the browse for folder...

Hope this helps someone out there