Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on SQL from XL to Access
Using ADO, I was able to send a query to Access and get the result back. I
used the following code to extract the data from Access. My question is that if I want to make multiple queries against the same db. Do I need to open and close the db connection after every query? I tried keeping the connection open and run against 2 queries to start with and I am getting an error on the 2nd query. It cannot proceed because I have to close the connection. What's the most efficient way to pull data with mutliple SQL queries? I presume open and close the connection everything would slow it down. True or False? Thanks Set Connection = CreateObject("ADODB.Connection") Set Recordset = CreateObject("ADODB.Recordset") Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; " Cnct = Cnct & "Data Source=" & DBFullName & "; Jet OLEDB:Database password=" & passwd & "; " Connection.Open ConnectionString:=Cnct Src = "SELECT fname, lname, address, city, state, zip from customer" Recordset.Open Source:=Src, ActiveConnection:=Connection Range("A1").CopyFromRecordset Recordset Set Recordset = Nothing Connection.Close Set Connection = Nothing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on SQL from XL to Access
See below
Tim "matelot" wrote in message ... Using ADO, I was able to send a query to Access and get the result back. I used the following code to extract the data from Access. My question is that if I want to make multiple queries against the same db. Do I need to open and close the db connection after every query? I tried keeping the connection open and run against 2 queries to start with and I am getting an error on the 2nd query. It cannot proceed because I have to close the connection. What's the most efficient way to pull data with mutliple SQL queries? I presume open and close the connection everything would slow it down. True or False? Thanks Set Connection = CreateObject("ADODB.Connection") Set Recordset = CreateObject("ADODB.Recordset") Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; " Cnct = Cnct & "Data Source=" & DBFullName & "; Jet OLEDB:Database password=" & passwd & "; " Connection.Open ConnectionString:=Cnct Src = "SELECT fname, lname, address, city, state, zip from customer" Recordset.Open Source:=Src, ActiveConnection:=Connection Range("A1").CopyFromRecordset Recordset Recordset.Close Src = "SELECT fname, lname, address, city, state, zip from customer2" Recordset.Open Source:=Src, ActiveConnection:=Connection Range("Z1").CopyFromRecordset Recordset Recordset.Close Set Recordset = Nothing Connection.Close Set Connection = Nothing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on SQL from XL to Access
Tim,
Thanks. It works. Mat "Tim Williams" wrote: See below Tim "matelot" wrote in message ... Using ADO, I was able to send a query to Access and get the result back. I used the following code to extract the data from Access. My question is that if I want to make multiple queries against the same db. Do I need to open and close the db connection after every query? I tried keeping the connection open and run against 2 queries to start with and I am getting an error on the 2nd query. It cannot proceed because I have to close the connection. What's the most efficient way to pull data with mutliple SQL queries? I presume open and close the connection everything would slow it down. True or False? Thanks Set Connection = CreateObject("ADODB.Connection") Set Recordset = CreateObject("ADODB.Recordset") Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; " Cnct = Cnct & "Data Source=" & DBFullName & "; Jet OLEDB:Database password=" & passwd & "; " Connection.Open ConnectionString:=Cnct Src = "SELECT fname, lname, address, city, state, zip from customer" Recordset.Open Source:=Src, ActiveConnection:=Connection Range("A1").CopyFromRecordset Recordset Recordset.Close Src = "SELECT fname, lname, address, city, state, zip from customer2" Recordset.Open Source:=Src, ActiveConnection:=Connection Range("Z1").CopyFromRecordset Recordset Recordset.Close Set Recordset = Nothing Connection.Close Set Connection = Nothing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access question | Excel Discussion (Misc queries) | |||
Secondary Access Question | Charts and Charting in Excel | |||
Access Form In An Access Report (SubForm) Question | Links and Linking in Excel | |||
it's a question for access | Excel Programming | |||
Access Question | Excel Programming |