Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use more than one databases in a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use more than one databases in a macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use more than one databases in a macro
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" <u18944@uwe wrote in message news:5c94945331359@uwe... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use more than one databases in a macro
Robin Hammond wrote:
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 -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use more than one databases in a macro
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 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
databases | Excel Worksheet Functions | |||
Databases | Excel Discussion (Misc queries) | |||
connecting to databases | Excel Discussion (Misc queries) | |||
LINK DATABASEs | New Users to Excel | |||
DATABASES | Excel Discussion (Misc queries) |