HOWTO: Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database

• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2

SUMMARY

Using ADO to Refresh or Create an Access linked table to a Jet 4.0 database secured with a database password requires using: tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=myPassword." If you do not specify the correct information for "Link Provider String," you might see the following error:

Error number -2147217843 Not a valid password.

MORE INFORMATION

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 

Additional query words:

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2fix
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto