Linking tables using VBA

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I am building a database which can be used stand-alone on
a laptop or linked to a central database when connected to
a network.

I have written code to enable the user to establish a link
when required, as follows:

DoCmd.TransferDatabase acLink, "Microsoft
Access", "D:\Folder\Database.mdb",
acTable, "Logon", "Logon", False

I have also written code to delete the table when the user
needs to log-off the network.

My problem is that I have a number of tables I wish to
link in this way and rather than hard-code the details
separately, I am sure I should be able to record the
relevant details (table names, path etc) in a table and
then have the code loop through, linking each in turn.

Does anyone have any ideas on how I can do this?
 
Here is a function that I've written (using the code example at
www.mvps.org/access as the starting point). It allows you to put multiple
"paths" in a table, and the code reads the first path to see if it can link,
and if not, then it reads the second path, etc.



' ***************************************
' ** Function RelinkToBackendDatabase **
' ***************************************

Public Function RelinkToBackendDatabase() As Boolean
' Kenneth D. Snell 16 April 2004
' *** THIS FUNCTION RELINKS THE FRONT END TO THE APPROPRIATE
' *** BACKEND ACCESS DATABASE, USING A TABLE IN THE FRONT END THAT
' *** CONTAINS VARIOUS PATHS TO A BACKEND DB (LISTED IN A
' *** PRIORITY ORDER). FUNCTION TRIES THE HIGHEST PRIORITY
' *** ONE FIRST, THEN ON TO THE NEXT IF THAT ISN'T SUCCESSFUL.
' *** FUNCTION CONTINUES UNTIL EITHER IT FINDS A BACKEND OR IT
' *** RUNS OUT OF PATHS FROM THE TABLE. FUNCTION IGNORES ANY
' *** ERROR THAT OCCURS IF A TABLE ISN'T FOUND IN THE BACKEND
' *** DATABASE (THIS IS DONE TO AVOID AN ERROR WHEN A FRONT END
' *** HAS A LINK TO A TABLE THAT IS STILL IN DEVELOPMENT AND IS
' *** NOT IN THE ACTUAL BACKEND AT THIS TIME).

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strLink As String, strQryTableLinks As String, strBE As String
Dim tdf As DAO.TableDef

Const strLinkDBString As String = ";DATABASE="
Const strLinkNum As String = "DBPathNum"
Const strLinkPath As String = "DBPathString"
Const strTableLinks As String = "tbl_TableLinkingPaths"


On Error GoTo Err_RelinkToBackendDatabase

strQryTableLinks = "SELECT * FROM " & strTableLinks & " ORDER BY " &
strLinkNum & ";"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strQryTableLinks, dbOpenDynaset, dbReadOnly)
rst.MoveFirst
Do While rst.EOF = False
strLink = rst.Fields(strLinkPath)
If Dir(strLink) <> "" Then
On Error Resume Next
For Each tdf In dbs.TableDefs
If Len(tdf.Connect & "") > 0 And tdf.Connect <> strLinkDBString
& _
rst.Fields(strLinkPath) Then
tdf.Connect = strLinkDBString & rst.Fields(strLinkPath)
tdf.RefreshLink
Err.Clear
End If
Next tdf
Set tdf = Nothing
RelinkToBackendDatabase = True
Exit Do
End If
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
If RelinkToBackendDatabase = False Then _
MsgBox "The database cannot find the 'backend' database file!" & _
vbCrLf & vbCrLf & "Notify the programmer for assistance!", _
vbCritical, "'Backend' Database Cannot Be Found!"

Exit Function


Err_RelinkToBackendDatabase:
MsgBox "An unexpected error has occurred while trying to find the 'backend'
database file!" & _
vbCrLf & vbCrLf & "Notify the programmer for assistance!", _
vbCritical, "Unexpected Error With 'Backend' Database!"
Err.Clear
RelinkToBackendDatabase = False

End Function
 
Back
Top