前台資料庫 (frontBase.mdb) 連結至後台資料庫 (backData.mdb)。當後台檔案移動或重命名,連結失效需重新導向。標準作法分兩階段:
在啟動表單 Form_Load 事件呼叫檢查函數:
Public Function CheckLinks() As Boolean
Dim dbs As Database, rst As DAO.Recordset
Set dbs = CurrentDb()
On Error Resume Next
Set rst = dbs.OpenRecordset("tbl1") ' 任一連結資料表
rst.Close
If Err = 0 Then CheckLinks = True Else CheckLinks = False
End Function
Private Sub Form_Load()
If CheckLinks = False Then DoCmd.OpenForm "frmConnect"
End Sub
表單含檔案選取文字框 FileName、瀏覽按鈕、確定按鈕。
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Boolean
Type OPENFILENAME
lStructSize As Long, hwndOwner As Long, hInstance As Long
lpstrFilter As String, lpstrCustomFilter As String, nMaxCustFilter As Long
nFilterIndex As Long, lpstrFile As String, nMaxFile As Long
lpstrFileTitle As String, nMaxFileTitle As Long, lpstrInitialDir As String
lpstrTitle As String, flags As Long, nFileOffset As Integer
nFileExtension As Integer, lpstrDefExt As String, lCustData As Long
lpfnHook As Long, lpTemplateName As String
End Type
Private Sub FileOpen_Click()
Dim ofn As OPENFILENAME, rtn As Boolean
ofn.lStructSize = Len(ofn)
ofn.hwndOwner = Me.hwnd
ofn.lpstrFilter = "資料庫檔案 (*.mdb)" & vbNullChar & "*.mdb"
ofn.lpstrFile = Space(254): ofn.nMaxFile = 255
ofn.lpstrFileTitle = Space(254): ofn.nMaxFileTitle = 255
ofn.lpstrInitialDir = CurrentProject.Path
ofn.lpstrTitle = "選擇後台資料檔案"
ofn.flags = 6148
rtn = GetOpenFileName(ofn)
If rtn Then
FileName.Text = ofn.lpstrFile
OK.Enabled = True
Else: FileName.Text = "": OK.Enabled = False
End If
End Sub
Private Sub OK_Click()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & Me.FileName.Text & ";PWD=後台資料庫密碼"
tdf.RefreshLink
End If
Next
MsgBox "連結成功!"
DoCmd.Close acForm, Me.Name
End Sub
核心邏輯:遍歷 TableDefs 集合,針對有 Connect 屬性的資料表(連結表),更新連接字串並呼叫 RefreshLink。簡單兩步驟解決連結遷移問題。