Connect to a linked table with code

Question

Connect to a linked table with code I have some linked tables from a SQL-server; they are linked with an ODBC connection. The password is not saved with the connection. When I am double clicking on the table in Access table-view I get a prompt for username and password. After entering the password I can view the data in the table.

My problem is when I try to access the table with code before having opened it in this way. What I try to do is to use ADODB to open a recordset with data from the linked table, like:

Dim rst as new ADODB.Recordset
Dim sql as string
Sql = “SELECT * FROM LinkedTable”
rst.Open sql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Running this code without having access the table before will generate this error: Error# -2147467259, ODBC: connection to dns-name failed.

So, my question is, are there any way to connect to the database with code that can be run when the database is opened? This would also help the users as they would not have to remember a password to the SQL-server.

Answer 1

You can use a connection string in your code, it is easy enough, seeing you are already using ADO: http://www.connectionstrings.com/

You will need to find out which version of SQL Server you are linking to.

Answer 2

It seems that you are mixing 2 technologies that might not work together, ie linked tables through ODBC and ADODB recordsets. Have you tried to open DAO recordsets on your linked tables?

Dim rst as DAO.Recordset
Dim sql as string
Sql = “SELECT * FROM LinkedTable”
set rst = currentDb.openRecordset(sql,<your parameters>)

You could of course use ADODB recordsets through 2 ADODB connections, one to your access file, the other one to your SQL server:

Dim rsSQL as ADODB.recordset, _
    rsACCESS as ADODB.recordset, _
    connectionSQL as ADODB.connection, _
    connectionACCESS as ADODB.connection

set connectionSQL = New ADODB.connection
set connectionACCESS = New ADODB.connection

connectionSQL.properties(...) = enumerate your SQL parameters
connectionACCESS.properties(...) = enumerate your ACCESS parameters (use currentproject.accessConnection if your access tables are local tables only)

set rsSQl = New ADODB.recordset
set rsACCESS = New ADODB.recordset

rsSQL.open "SELECT * FROM ...", connectionSQL, <other parameters>
rsACCESS.open "SELECT * FROM ...", connectionACCESS, <other parameters>

Linking ADO recordsets to forms and comboboxes in Access is possible. But, when creating forms, this technology has to be mainly managed through VBA code (you will have to write 'on open' events such as set me.recorset = ...), while the standard "linked tables" technology can be easily used through the user-friendly 'form-design' interface.