Question on SQL from XL to Access

[Guest]

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 OLEDBatabase
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

 

[Tim Williams]

See below

Tim

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 OLEDBatabase
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

 

[Guest]

Tim,
Thanks. It works.

Mat

See below

Tim





Recordset.Close

Src = "SELECT fname, lname, address, city, state, zip from customer2"
Recordset.Open Source:=Src, ActiveConnection:=Connection
Range("Z1").CopyFromRecordset Recordset

Recordset.Close