'First, perform the following test:
'Create a blank access database (. mdb) file,
'And add several types of chain tables:
'Dbase 5 (*. DBF ),
'Microsoft Excel (*. xls ),
'Microsoft access (*. mdb; *. MDA; *. MDE,
'Then write the following VB6: Program :
'Delphi, VC and other programs can also be:
'Reference Microsoft ActiveX Data Objects 2.x Library
'Reference Microsoft ADO Ext. 2.x for DDL and security
'Controls: form1, command1, command2, command3
Private sub commandementclick () 'test the chain table information
Dim adoconnection as new ADODB. Connection
Adoconnection. Open "provider = Microsoft. Jet. oledb.4.0; Data Source = E:/lnktworkflow. 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
'Add a chain table programmatically
Private sub command2_click ()
Dim adoconnection as new ADODB. Connection
Adoconnection. Open "provider = Microsoft. Jet. oledb.4.0; Data Source = E:/lnktworkflow. 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 = "product"
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
'Program to delete a chain table
Private sub command3_click ()
Dim adoconnection as new ADODB. Connection
Adoconnection. Open "provider = Microsoft. Jet. oledb.4.0; Data Source = E:/lnktworkflow. 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:/lnktworkflow. 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 = "chain table information"
Command2.caption = "add a chain table"
Command3.caption = "deleting a chain table"
Command4.caption = "refresh the chain table"
End sub