编程管理(添加、删除、刷新) Access 链接表信息
'首先 , 作如下测试工作:
'新建空白 Access 数据库 (.mdb) 文件,
'并添加若干类型链接表:
'dBase 5 (*.dbf)、
'Microsoft Excel (*.xls)、
'Microsoft Access (*.mdb;*.mda;*.mde) 等,
'然后再编写如下 VB6 程序:
'Delphi、VC 等程序同理也可:
'引用 Microsoft ActiveX Data Objects 2.x Library
'引用 Microsoft ADO Ext. 2.x for DDL and Security
'控件: Form1、Command1、Command2、Command3
Private Sub Command1_Click() '测试链接表信息
Dim adoConnection As New ADODB.Connection
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\LnkTbls.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123"
Dim adoCatalog As New ADOX.Catalog
Set adoCatalog.ActiveConnection = adoConnection
Dim adoTable As New ADOX.Table
Set adoTable.ParentCatalog = adoCatalog
Dim i As Integer
For Each adoTable In adoCatalog.Tables
If adoTable.Type = "LINK" Then
Debug.Print adoTable.Name
For i = 0 To adoTable.Properties.Count - 1
Debug.Print " " & adoTable.Properties.Item(i).Name & ": " & adoTable.Properties.Item(i).Value
Next i
Debug.Print VBA.vbCrLf
End If
Next adoTable
End Sub
'编程添加链接表
Private Sub Command2_Click()
Dim adoConnection As New ADODB.Connection
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\LnkTbls.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123"
Dim adoCatalog As New ADOX.Catalog
Dim adoTable As New ADOX.Table
'Access
Set adoCatalog.ActiveConnection = adoConnection
Set adoTable.ParentCatalog = adoCatalog
adoTable.Properties.Item("Jet OLEDB:Link Datasource").Value = "e:\nwind2kpwd.mdb"
adoTable.Properties.Item("Jet OLEDB:Remote Table Name").Value = "产品"
adoTable.Properties.Item("Jet OLEDB:Create Link").Value = True
adoTable.Properties.Item("Jet OLEDB:Link Provider String").Value = "MS Access;Pwd=456"
adoTable.Name = "Access"
adoCatalog.Tables.Append adoTable
adoConnection.Close
'dBase
adoConnection.Open
Set adoCatalog.ActiveConnection = adoConnection
Set adoTable.ParentCatalog = adoCatalog
adoTable.Properties.Item("Jet OLEDB:Link Datasource").Value = "E:\Borland\Shared\Data"
adoTable.Properties.Item("Jet OLEDB:Remote Table Name").Value = "animals#dbf"
adoTable.Properties.Item("Jet OLEDB:Create Link").Value = True
adoTable.Properties.Item("Jet OLEDB:Link Provider String").Value = "dBase 5.0"
adoTable.Name = "dBase5"
adoCatalog.Tables.Append adoTable
adoConnection.Close
'Excel
adoConnection.Open
Set adoCatalog.ActiveConnection = adoConnection
Set adoTable.ParentCatalog = adoCatalog
adoTable.Properties.Item("Jet OLEDB:Link Datasource").Value = "E:\Book97.xls"
adoTable.Properties.Item("Jet OLEDB:Remote Table Name").Value = "Sheet1$"
adoTable.Properties.Item("Jet OLEDB:Create Link").Value = True
adoTable.Properties.Item("Jet OLEDB:Link Provider String").Value = "Excel 5.0;HDR=NO;IMEX=2"
adoTable.Name = "Excel"
adoCatalog.Tables.Append adoTable
adoConnection.Close
'...
End Sub
'编程删除链接表
Private Sub Command3_Click()
Dim adoConnection As New ADODB.Connection
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\LnkTbls.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123"
Dim adoCatalog As New ADOX.Catalog
Set adoCatalog.ActiveConnection = adoConnection
Dim j As Integer
Dim i As Integer
For i = adoCatalog.Tables.Count To 1 Step -1
If adoCatalog.Tables.Item(i - 1).Type = "LINK" Then
Debug.Print adoCatalog.Tables.Item(i - 1).Name
For j = 0 To adoCatalog.Tables.Item(i - 1).Properties.Count - 1
Debug.Print " " & adoCatalog.Tables.Item(i - 1).Properties.Item(j).Name & ": " & adoCatalog.Tables.Item(i - 1).Properties.Item(j).Value
Next j
Debug.Print VBA.vbCrLf
If VBA.MsgBox("Delete link table [" & adoCatalog.Tables.Item(i - 1).Name & "]?", vbYesNo) Then
adoCatalog.Tables.Delete adoCatalog.Tables.Item(i - 1).Name
End If
End If
Next i
End Sub
Private Sub Command4_Click()
Dim adoConnection As New ADODB.Connection
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\LnkTbls.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123"
Dim adoCatalog As New ADOX.Catalog
Set adoCatalog.ActiveConnection = adoConnection
adoCatalog.Tables.Item("Excel").Properties.Item("Jet OLEDB:Link Provider String").Value = "Excel 5.0;HDR=yes;IMEX=2"
End Sub
Private Sub Form_Load()
Command1.Caption = "链接表信息"
Command2.Caption = "添加链接表"
Command3.Caption = "删除链接表"
Command4.Caption = "刷新链接表"
End Sub
