p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

proasp_howto thread: Pulling data from Multiple Databases


Message #1 by ASP@t... on Sun, 12 Mar 2000 23:27:32
You would need to setup 3 different connection objects, then setup separate 
recordsets for each of those connections.

shawn


On 03/13/00, "ASP@t..." wrote:
> I am beginning to wonder if this is possible. I have looked everywhere and
can not find any examples of what I need to accomplish.

I need a form that displays the current information contained in the 
databases.
The user will then need to change and update the information.
The information is contained in two databases and three tables ((1Db & 
1tbl)   +   (1Db & 2tbls)).
Each of the databases is on a different server.
One is an Access database and the other is SQL.

During testing I did not have any problem pulling from three different 
tables in ONE database. And once I had pulled the information down I was 
able to update the information using multiple Recordset objects. But when
you change the configuration everything changes.

How do you handle the Connection Object & Recordset Object when the tables
are from different databases?
How do you handle the SQL statement?

Here is a Snippet of code that is similar to what I would normally do.
++++++++++++
'       Create the Connection Object
 set objConn = server.createobject("adodb.connection")
'       Open the Connection using the connection string specified in the
DataStore.inc file
'       I normaly use DSN-Less Connections
 objConn.open strDbConnect
SQL="SELECT * FROM (employee INNER JOIN clients ON employee.Empnum 
clients.CLTID)" & _
        "INNER JOIN tblSecurity ON clients.CLTID = tblSecurity.SECCLTID " 
&
_
        "WHERE Employee.Empnum= '" & CLTID & "';"
' Create RecordSet Object
 set objRs = server.createobject("adodb.recordset")
' Open RecordSet Object using the SQL statement and the Connection Object
 objRs.Open SQL, objConn

' ....BUNCH OF CODE TO USE THE INFORMATION

' Clean up the Recordset and Connection Object
 objRs.Close
 set objRs=nothing
 objConn.Close
 set objConn=nothing
++++++++++++++++++++++++++++++++++++++

Any ideas or even a pointer on where to look will be greatly appreciated.

Thanks
Tom

  Return to Index