Ado and linked tables

[Rauken]

In my access database I have both regular Access tables and linked Sql tables. I try to use ado and do a simple select * from table on a linked table and I get an error that I'm missing parameters? I can double click on the linked tables and get the result. Anyone knows what's wrong?

My connection string looks like this:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\data\orders.mdb;" & _
"User Id=;" & _
"Password=;"

 

[tonyflavell]

Hi there

This is an extract from the MSDN Library and covers your problem.

The example demonstrates how to use the ODBC Provider, the default OLE DB provider in ADO, to connect to SQL Server:

[CodeDim Cn As New ADODB.Connection

Cn.ConnectionTimeout = 100
' DSN connection
Cn.Open "pubs", "sa"
' DSN-Less connection for SQL Server
' Cn.Open "Driver={SQL Server};Server=Server1;Uid=sa;Pwd=;Database=pubs"

Cn.Close[/code]

The example first sets the connection time-out value to 100 seconds, and then opens a connection using the pubs ODBC data source. A user ID is required for the pubs data source; therefore, the sa user ID is provided as the second parameter of the Open method. There is no password, so the third parameter is omitted.

In addition to using an ODBC data source, a commented line in the example shows how to connect to SQL Server without an ODBC data source. The SQL Server ODBC driver, {SQL Server}, is used to connect to a SQL Server called Server1. The user ID to connect to Server1 is sa, and there is no password for sa. The default database for this connection is the pubs database.

 

[Rauken]

Ok. Going straight against the sql server with ado is no problem.

I want to go against the Access database with the linked sql server tables so I don't have to enter userid and pwd for sql server and I want to combine both access tables and linked tables in a query. It might not work that way?

 

[Rauken]

I think we can forget my problem anyway. In my query which combines access tables and sql server linked tables I get an image field from the sql server.

I want to use ado's stream object to save the image to disc and then show it in a report. I guess I have to do it in two steps. First opening up the report with the query and then save the image to disc and show it in the report with some vba code?

 

[Rauken]

This is how you do it..

http://support.microsoft.com/kb/q245587/