How do I use two data sources in ADP?

[RocProg]

Hello All,

I have an mdb Access database that uses two link tables from two different SQL servers with ODBC. Now my problem is that the decision has been made to not use ODBC because of ODBC errors that have occured for no apparant reason at a customer's site. The links have been recreated and the program is running fine, but they still want to make sure this doesnt happen again. I solved the problem with another Access file by switching over to ADP and it works beautifully, yet it only has one data source. Now how do I directly connect to two SQL servers at the same time so I can perform a union and create a report since it seems to only allow one connection?

I know I can connect directly through code using a connect string, is there a way to make certain stored procedures use a connection from the VBA code? Or, is there a way to run a query through VBA and somehow specify that as a record source for the report?

Any help would be greatly appreciated. Thanks.

 

[cmmrfrds]

If this is a permenant ongoing need then have the DBA set up the tables from the second server as linked servers. This way you can access the tables as if they were on the same server i.e. the same connection.

For more adhoc needs use OPENROWSET.
Example of using openrowset from sql server to access, but can be to any other type of database.


Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function