Microsoft KB Archive/240222

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

Article ID: 240222

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5

-



This article was previously published under Q240222



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.



Step-by-Step Example
 Paste the following code in a new Visual Basic Standard EXE project Form General Declarations section. Set a reference to Microsoft ActiveX Data Objects Library and Microsoft ADO Ext for DDL and Security. Add two command buttons to the Form.  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 

Keywords: kbhowto kbjet kbado210sp2fix kbmdacnosweep KB240222

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.