Programming Management (add, delete, and refresh) Access chain table information

Source: Internet
Author: User
Tags dbase

'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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.