jerryx
27-10-2004, 12:47
Tôi tạo bằng tay một DSN trong ODBC trong control panel để liên kết với SQL Server 2000. Từ trong access 97 link bằng tay các table của SQL server qua DSN vừa tạo. Ứng dụng access mỗi khi khởi động sau đó dùng thủ tục (RegisterDataba***() mẫu trong help của access 97) để tạo lại DSN trong OSBC như đã tạo trước đó. Tiếp theo dùng phương thức RefreshLink để làm tươi lại các link table của SQL Server đã link bằng tay trước đó. Tuy nhiên thỉnh thoảng cách này không mở được các table đã link bên trong access. Xin cho biết nguyên nhân? Có phương thức nào đọc các key trong "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI" của registry để biết DSN đang tồn tại.
Sub RegisterDataba***()
Dim dbsRegister As Database
Dim strDescription As String
Dim strAttributes As String
Dim errLoop As Error
' Build keywords string.
strDescription = InputBox( "Enter a description " & _
"for the database to be registered.")
strAttributes = "Database=pubs" & _
vbCr & "Description=" & strDescription & _
vbCr & "OemToAnsi=No" & _
vbCr & "Server=Server1"
' Update Windows Registry.
On Error GoTo Err_Register
DBEngine.RegisterDatabase "Publishers", "SQL Server", _
True, strAttributes
On Error GoTo 0
MsgBox "Use regedit.exe to view changes: " & _
"HKEY_CURRENT_USER\" & _
"Software\ODBC\ODBC.INI"
Exit Sub
Err_Register:
' Notify user of any errors that result from
' the invalid data.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & _
vbCr & errLoop.Description
Next errLoop
End If
Resume Next
End Sub
Sub RefreshLinkX()
Dim dbsCurrent As Database
Dim tdfLinked As TableDef
' Open a database to which a linked table can be
' appended.
Set dbsCurrent = OpenDatabase("DB1.mdb")
' Create a linked table that points to a Microsoft
' SQL Server database.
Set tdfLinked = _
dbsCurrent.CreateTableDef("AuthorsTable")
tdfLinked.Connect = _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
tdfLinked.SourceTableName = "authors"
dbsCurrent.TableDefs.Append tdfLinked
' Display contents of linked table.
Debug.Print _
"Data from linked table connected to first source:"
RefreshLinkOutput dbsCurrent
' Change connection information for linked table and
' refresh the connection in order to make the new data
' available.
tdfLinked.Connect = _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=NewPublishers"
tdfLinked.RefreshLink
' Display contents of linked table.
Debug.Print _
"Data from linked table connected to second source:"
RefreshLinkOutput dbsCurrent
' Delete linked table because this is a demonstration.
dbsCurrent.TableDefs.Delete tdfLinked.Name
dbsCurrent.Close
End Sub
Sub RefreshLinkOutput(dbsTemp As Database)
Dim rstRemote As Recordset
Dim intCount As Integer
' Open linked table.
Set rstRemote = _
dbsTemp.OpenRecordset("AuthorsTable")
intCount = 0
' Enumerate Recordset object, but stop at 50 records.
With rstRemote
Do While Not .EOF And intCount < 50
Debug.Print , .Fields(0), .Fields(1)
intCount = intCount + 1
.MoveNext
Loop
If Not .EOF Then Debug.Print , "[more records]"
.Close
End With
End Sub
Sub RegisterDataba***()
Dim dbsRegister As Database
Dim strDescription As String
Dim strAttributes As String
Dim errLoop As Error
' Build keywords string.
strDescription = InputBox( "Enter a description " & _
"for the database to be registered.")
strAttributes = "Database=pubs" & _
vbCr & "Description=" & strDescription & _
vbCr & "OemToAnsi=No" & _
vbCr & "Server=Server1"
' Update Windows Registry.
On Error GoTo Err_Register
DBEngine.RegisterDatabase "Publishers", "SQL Server", _
True, strAttributes
On Error GoTo 0
MsgBox "Use regedit.exe to view changes: " & _
"HKEY_CURRENT_USER\" & _
"Software\ODBC\ODBC.INI"
Exit Sub
Err_Register:
' Notify user of any errors that result from
' the invalid data.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & _
vbCr & errLoop.Description
Next errLoop
End If
Resume Next
End Sub
Sub RefreshLinkX()
Dim dbsCurrent As Database
Dim tdfLinked As TableDef
' Open a database to which a linked table can be
' appended.
Set dbsCurrent = OpenDatabase("DB1.mdb")
' Create a linked table that points to a Microsoft
' SQL Server database.
Set tdfLinked = _
dbsCurrent.CreateTableDef("AuthorsTable")
tdfLinked.Connect = _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
tdfLinked.SourceTableName = "authors"
dbsCurrent.TableDefs.Append tdfLinked
' Display contents of linked table.
Debug.Print _
"Data from linked table connected to first source:"
RefreshLinkOutput dbsCurrent
' Change connection information for linked table and
' refresh the connection in order to make the new data
' available.
tdfLinked.Connect = _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=NewPublishers"
tdfLinked.RefreshLink
' Display contents of linked table.
Debug.Print _
"Data from linked table connected to second source:"
RefreshLinkOutput dbsCurrent
' Delete linked table because this is a demonstration.
dbsCurrent.TableDefs.Delete tdfLinked.Name
dbsCurrent.Close
End Sub
Sub RefreshLinkOutput(dbsTemp As Database)
Dim rstRemote As Recordset
Dim intCount As Integer
' Open linked table.
Set rstRemote = _
dbsTemp.OpenRecordset("AuthorsTable")
intCount = 0
' Enumerate Recordset object, but stop at 50 records.
With rstRemote
Do While Not .EOF And intCount < 50
Debug.Print , .Fields(0), .Fields(1)
intCount = intCount + 1
.MoveNext
Loop
If Not .EOF Then Debug.Print , "[more records]"
.Close
End With
End Sub