Microsoft KB Archive/297963

= How To Change Logon Information for Access Linked Tables at Run Time with ADOX =

Article ID: 297963

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q297963



SUMMARY
In some applications, if you use Microsoft Jet databases that contain tables that are linked to external data sources through ODBC, you may want to provide the logon name and password of the current user at run time to connect to the remote data source.

To accomplish this, Microsoft ADO Extensions for DDL and Security (ADOX) exposes a read/write property on Microsoft Access linked tables (&quot;Jet OLEDB:Link Provider String&quot;) that allows you to modify the connection string in code and add the necessary authentication information.



Step-by-Step Instructions
IMPORTANT: For the following technique to work, you must not save the password when you link the table in Access.  Create a new Standard EXE project in Visual Basic. Form1 is created by default. 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. Add a CommandButton control to Form1.  The following code uses the Microsoft OLE DB Provider for Jet version 4.0 to open your Access database and sets the dynamic &quot;Jet OLEDB:Link Provider String&quot; 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 &quot;provider=microsoft.jet.oledb.4.0;User ID=admin;&quot; & _ &quot;Password=;data source=linktables.mdb;&quot;

Set cat = New ADOX.Catalog cat.ActiveConnection = cn Set tbl = cat.Tables(&quot;MyLinkedTable&quot;) ' Append authentication parameters to the end of the existing connection string. tbl.Properties(&quot;Jet OLEDB:Link Provider String&quot;) = _ tbl.Properties(&quot;Jet OLEDB:Link Provider String&quot;) & &quot;;UID=Demo;PWD=Demo;&quot; Set rs = New ADODB.Recordset

rs.Open &quot;Select * from MyLinkedTable&quot;, cn msgbox rs(0) rs.close set cat = nothing set rs = nothing cn.close set cn = nothing

end sub  Run the project, and click Command1.

