How To Change Logon Information for Access Linked Tables at Run Time with ADOX (297963)

MORE INFORMATION

Step-by-Step Instructions

1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
2. On the File menu, click References, and the select the Microsoft ADO Ext. 2.5 for DDL and Security and Microsoft ActiveX Data Objects 2.5 check boxes. You can use version 2.5 or later for these references.
3. Add a CommandButton control to Form1.
4. The following code uses the Microsoft OLE DB Provider for Jet version 4.0 to open your Access database and sets the dynamic "Jet OLEDB:Link Provider String" property of the ActiveX Data Objects (ADO) Recordset object. Paste the following code onto the Declarations section of Form1:

   Option Explicit
   Private Sub Command1_Click()
     Dim cn As ADODB.Connection
     Dim rs As ADODB.Recordset
     
     Dim cat As ADOX.Catalog
     Dim tbl As ADOX.Table
     
     Set cn = New ADODB.Connection
     
     cn.Open "provider=microsoft.jet.oledb.4.0;User ID=admin;" & _
        "Password=;data source=linktables.mdb;"
   
     Set cat = New ADOX.Catalog
     
     cat.ActiveConnection = cn
     
     Set tbl = cat.Tables("MyLinkedTable")
    
   ' Append authentication parameters to the end of the existing connection string.
     tbl.Properties("Jet OLEDB:Link Provider String") = _
         tbl.Properties("Jet OLEDB:Link Provider String") & ";UID=Demo;PWD=Demo;"
      
     Set rs = New ADODB.Recordset
   
     rs.Open "Select * from MyLinkedTable", cn
     msgbox rs(0)
     rs.close
     set cat = nothing
     set rs = nothing
     cn.close
     set cn = nothing
   
   end sub
   					

5. Run the project, and click Command1.