萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Access 教學 >> Access 基礎教學

Access 連結資料表重新導向兩步驟:啟動檢查與對話框重新連結

前台資料庫 (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

步驟二:連結重新導向表單 (frmConnect)

表單含檔案選取文字框 FileName、瀏覽按鈕、確定按鈕。

1. 瀏覽按鈕:呼叫 API 開啟檔案對話框

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

2. 確定按鈕:重新整理所有連結資料表

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。簡單兩步驟解決連結遷移問題。

copyright © 萬盛學電腦網 all rights reserved