How to use more than one databases in a macro

[vanessa via OfficeKB.com]

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

 

[Edwin Tam]

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
(e-mail address removed)
http://www.vonixx.com

 

[Robin Hammond]

Vanessa,

Are you sure you are setting the correct connections when you open your
recordsets?

e.g.
recordSet.Open "select * from table1", con
recordset1.Open "select * from table2", con1

Robin Hammond
www.enhanceddatasystems.com

 

[vanessa via OfficeKB.com]

Vanessa,

Are you sure you are setting the correct connections when you open your
recordsets?

e.g.
recordSet.Open "select * from table1", con
recordset1.Open "select * from table2", con1

Robin Hammond
www.enhanceddatasystems.com

Hi all,

[quoted text clipped - 33 lines]

regards,
vanessa

Hi Robin Hammond,


Thank you very much oh
yaya, i got it.....i'm too careless to change the recordset to recordset1.
i can success continue my following coding la.......

regards,
vanessa

 

[vanessa via OfficeKB.com]

Hi Edwin,

Thank you for your example oh, dun know why i try this always prompt out the
error which in:
Set Cnxn = New ADODB.Connection

I still keep my previous coding, i changed the variables when connect to
database, but forgot the changed also for the recordset to recordset1 that
why the result come out still same as first database. Thank you oh.

regards,
vanessa