Michel,
Below is some code that I used to switch between some
testing date and the live database. Hopefully you can
find something is this code that will help you. (Watch
out for the word wraping) This was a function that I
could call from the startup of the application or that I
could just run at will to change the connection to the
data.
So here is the code:
******Start of Code******
Function UpdateTableLinks()
Dim AppDB As Database
Dim Tbl As TableDef
Dim rst As Recordset
Dim tblName As String
Dim DbName As String
Dim Indx As Integer
Dim varTblCnt As Integer
Dim varNewDbPath As String
Dim varUserMsgOpen As Boolean
Set AppDB = CurrentDb()
' Uses the global variable "varDevMode" to determine
the location for linked tables
' The global variable "varDevMode" is assigned a
value in the OnOpen event of the first form to open
' To change the global variable "varDevMode" comment
out or un-comment the statement
' "varDevMode = True" near the beginning of the
OnOpen event of the "MainMenu" form
varTblCnt = AppDB.TableDefs.Count
'variable "varDevMode" is set in the Globals Module
'set it to "false" there to go live
' If the global variable "varDevMode" is true use
this path
If varDevMode = True Then
' the next statement is for testing only and
should be deleted in final release
'DataDbPath = "C:\YourDatabase\"
DataDbPath = "\\ftwntfilp003
\FolderName\YourDatabase\"
Else
' If the global variable "varDevMode" is not true use
this path
' Path for testing development
'DataDbPath = "\\ftwntfilp003
\FolderName\YourDatabase\"
' Path to actual live data
DataDbPath = "\\ftwntfilp003
\FolderName\YourDatabase\"
End If
' Get the list of linked tables from the AgSecure.mdb
file
' and for each table, reset the path to the table
For Indx = 0 To AppDB.TableDefs.Count - 1
Set Tbl = AppDB.TableDefs(Indx)
If (Tbl.CONNECT <> "") Then
tblName = Tbl.Name
' Extract the database name from the connection
string using
' the dhRInstr function
DbName = Right(Tbl.CONNECT, Len(Tbl.CONNECT) -
dhRInstr(Tbl.CONNECT, "\"))
' Set the new database path for linked tables
varNewDbPath = ";DATABASE=" & DataDbPath + DbName
& ""
'On Error GoTo PathNotGood
Tbl.CONNECT = varNewDbPath
'Debug.Print "Table: " & Tbl.Name & vbNewLine
& "Connect: " & Tbl.CONNECT
If varUserMsgOpen <> True Then
DoCmd.OpenForm "frmUserMsg"
varUserMsgOpen = True
End If
Forms![frmUserMsg].SetFocus
Call Delay(1)
Forms![frmUserMsg].labDesc.Caption = "Updating
links to table " & Indx + 1 & " of " & varTblCnt & " to:"
Forms![frmUserMsg].labUsrMsg.Caption = "Updating
link to table: " & tblName & " in " & DbName
Forms![frmUserMsg].Refresh
'Call Delay(1)
Tbl.RefreshLink
End If
Next
varUserMsgOpen = False
DoCmd.Close acForm, "frmUserMsg"
AppDB.TableDefs.Refresh
'For Indx = 0 To AppDB.TableDefs.Count - 1
' Debug.Print "Table: " & Tbl.Name & vbNewLine
& "Connect: " & Tbl.CONNECT
'Next
'Exit Function
'PathNotGood:
'DoCmd.Close acForm, "frmUserMsg"
'MsgBox ("The Path to the data cannot be found." & vbCrLf
& vbCrLf & "Be sure that your network connection is
working.")
End Function
******End Of Code******
HTH
Byron
