How To Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database (240222)

MORE INFORMATION

Step-by-Step Example

1. Paste the following code in a new Visual Basic Standard EXE project Form General Declarations section.
2. Set a reference to Microsoft ActiveX Data Objects Library and Microsoft ADO Ext for DDL and Security.
3. Add two command buttons to the Form.
4. Make the appropriate changes to the code to point to your databases and tables with the correct connection information and password for your environment:

   Dim adoCn As ADODB.Connection
   Dim adoCat As New ADOX.Catalog
   Dim adoTbl As New ADOX.Table
   
   Private Sub Command1_Click()
   
   'Create Link...
   Set adoCat = New ADOX.Catalog
   Set adoCat.ActiveConnection = adoCn
   
   Set adoTbl.ParentCatalog = adoCat
   adoTbl.Name = "LinkTable"
   
   adoTbl.Properties("Jet OLEDB:Link Datasource") = App.Path & "\myLinkDatabase.mdb"
   adoTbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=myLinkPassword"
   adoTbl.Properties("Jet OLEDB:Remote Table Name") = "LinkDatabaseTable"
   adoTbl.Properties("Jet OLEDB:Create Link") = True
   
   'Append the table to the tables collection
   adoCat.Tables.Append adoTbl
   
   Form1.Caption = "Link Created..."
   
   End Sub
   
   Private Sub Command2_Click()
   
   'Refresh Link...
   Set adoCat = New ADOX.Catalog
   Set adoCat.ActiveConnection = adoCn
   
   Set adoTbl.ParentCatalog = adoCat
   
   For Each adoTbl In adoCat.Tables
       If adoTbl.Type = "LINK" And (adoTbl.Name = "LinkTable") Then
           adoTbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=myLinkPassword"
           adoTbl.Properties("Jet OLEDB:Link Datasource") = App.Path & "\myLinkDatabase.mdb"
       End If
   Next
   
   Form1.Caption = "Link Refreshed..."
   
   End Sub
   
   Private Sub Form_Load()
      
   strCn = App.Path & "\myDatabase.mdb"
   Set adoCn = New ADODB.Connection
   With adoCn
       .Provider = "Microsoft.JET.OLEDB.4.0;" & _
                       "Jet OLEDB:Database Password=myPassword"
       .Open strCn
   End With
   
   End Sub