I am not clear whether you want to connect to 2 "databases", or you want to
connect to 2 tables in the same database. I guess the former in your case...
Let me provide a more straight forward macro template for you. (Just replace
the database name and table names.) It should work in most cases. (But make
sure you added the appropriate ADO reference to your project.)


Sub example()
Dim Cnxn As ADODB.Connection
Dim Cnxn2 As ADODB.Connection
Dim rstData As ADODB.Recordset
Dim rstData2 As ADODB.Recordset
Dim strCnxn As String
Dim strCnxn2 As String

Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='host_name'; _
Initial Catalog='DB_name1';Integrated Security='SSPI'"
Cnxn.Open strCnxn

Set rstData = New ADODB.Recordset
rstData.Open "table1", strCnxn, adOpenKeyset, adLockOptimistic, adCmdTable

Set Cnxn2 = New ADODB.Connection
strCnxn2 = "Provider='sqloledb';Data Source='host_name'; _
Initial Catalog='DB_name2';Integrated Security='SSPI'"
Cnxn2.Open strCnxn2

Set rstData2 = New ADODB.Recordset
rstData2.Open "table2", strCnxn2, adOpenKeyset, adLockOptimistic, adCmdTable

'
'do something else
'

rstData.Close
rstData2.Close
Cnxn.Close
Cnxn2.Close
Set Cnxn = Nothing
Set Cnxn2 = Nothing
Set rstData = Nothing
Set rstData2 = Nothing
End Sub


Regards,
Edwin Tam

http://www.vonixx.com



"vanessa via OfficeKB.com" wrote:

Hi all,

i have a problem that i need to connect to two databases concurently to
retrieve records. But i never try more than one database before i use the
same way of databases connection format by chnaging the variables name, the
result still from previous database that i connect.
this is my code:

Dim con, recordSet
Dim con1, recordSet1

'Create a connection to ms sql server
Set con = CreateObject("ADODB.Connection")
Set recordSet = CreateObject("ADODB.Recordset")

con.Provider = "SqlOleDB"
con.Properties("Data Source").Value = "localhost"
con.Properties("Initial Catalog").Value = "bbiprod"
con.Properties("Integrated Security").Value = "SSPI"

con.Open

Set con1 = CreateObject("ADODB.Connection")
Set recordSet1 = CreateObject("ADODB.Recordset")

con1.Provider = "SqlOleDB"
con1.Properties("Data Source").Value = "localhost"
con1.Properties("Initial Catalog").Value = "bbidev"
con1.Properties("Integrated Security").Value = "SSPI"

con1.Open


Please help me......thank you yea.

regards,
vanessa

--
Message posted via http://www.officekb.com