Automatically Changing Table Links in Access using VBA? 1

[tekvb1977]

I have a front end and back end databases. Is there a way in VBA that would let me automatically change links to the main tables residing in the backend database.

Thanks a lot in advance

J

 

[angler7]

I'm sure there are more elegant ways to do this but I have a form and lookup table in the Client with a list box the source of which is a lookup table of backend paths. The user highlights the backend path they want, they input the backend file they want to link to in a seperate text box, and the combined results are stored in a textbox control called txtFullPath.

Then there's a command button which deletes the existing link (where "lkpBankAccts" is the linked table):
DoCmd.DeleteObject acTable, "lkpBankAccts"

and creates a new link:
DoCmd.TransferDatabase acLink, "Microsoft Access", Me.txtFullPath, acTable, "lkpBankAccts", "lkpBankAccts".

That's one way to do it, I'm sure there's more. Should get you started.

 

[MajP]

If you can get a copy of the A2K Enterprise Developers Handbook (Litwin, Getz, Gunderloy) they have a good function on pages 14-22 and the code. It verifies the links, looks for the database, if it can not find the database it pops up a file browser. The user finds the db and it reestablishes the link. This and the Desktop Developers Handbook are great resources. I do not have a short example on me, sorry.

 

[formerTexan]

Hi,

Following is one way. I think I have copied and pasted the relevant parts. In summary, you use a SQL statement to return invalidly linked tables and then pass the table name to a sub for relinking. Note that you will need to provide a valid path for the variable strNewFilePath. This will be the path to whichever BE database you are attempting to relink to.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLGet As String
Dim strNewFilePath As String ‘ path to BE database
Dim strBETableName As String
Set db = CurrentDb

strSQLGet = "SELECT MSysObjects.Database, MSysObjects.Type, MSysObjects.ForeignName"
strSQLGet = strSQLGet & " FROM MSysObjects"
strSQLGet = strSQLGet & " WHERE ((Len(Dir([Database])) = 0) And ((MSysObjects.Type) = 6))"
strSQLGet = strSQLGet & " ORDER BY MSysObjects.Database;"

Set rs = db.OpenRecordset(strSQLGet)
If Not rs.BOF And Not rs.EOF Then ' there are rows, so there must be invalid paths, otherwise move on

rs.MoveFirst
Do Until rs.EOF
strBETableName = rs!ForeignName
Call RelinkTables(strNewFilePath, strBETableName)
rs.MoveNext
Loop



Private Sub RelinkTables(ByVal strNewFilePath As String, ByVal strBETableName As String)
Dim db As DAO.Database
Dim Tdf As DAO.TableDef
Dim Tdfs As DAO.TableDefs
If Len(strBETableName) > 0 Then 'verify if BE path is valid
Set db = CurrentDb
Set Tdfs = db.TableDefs
For Each Tdf In Tdfs

If Len(Tdf.SourceTableName) <> 0 Then
If Tdf.SourceTableName = strBETableName Then
Tdf.Connect = ";DATABASE=" & strNewFilePath
Tdf.RefreshLink
End If
End If
Next Tdf
End If
End Sub


Cheers,
Bill

 

[tekvb1977]

Bill,

It works perfectly fine. Thanks a lot.

Thanks to angler7 and MajP too.