VBA Query - Close and Reopen db Connection when SQL Changes? 2

[MattGreer]

I'm pulling data into Excel 365 from Access 365 using a slightly modified version of this code. I'm using a list to loop through the script, changing the SQL string each time to pull new/different data and insert it into Excel. What I'm finding is that the first loop pulls the records correctly, but each subsequent loop comes back empty.

Do I have to close and open the connection to the database each time I send a query over?

I've tested the SQL string by Debug.Print then pasting it into a query in the database and it works, but yet when I run it from Excel, only the first query works. After the first loop, everything else comes back blank/empty.


Thanks!!


Matt

 

[SkipVought]

Matt,

Is your Excel CarsList driving this query? This is really a Forum707 question, it seems to me. Is so, you ought to be using an ADODB Parameter in your query for each car object in your CarsList. And NO, the loop would be using the same Connection. Only the Recordset would open/close within the loop.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Yep, that's what I figured out, trial and error. Connection stays open, but the recordset has to be closed each time. For good measure I set it = nothing just in case.

It's not really cars that I'm working on, but it's the same project, yes. I figured this was more of a database question but in retrospect I think you're right. Thank you!

Thanks!!


Matt

 

[SkipVought]

The presidents are

[tt]
Set the Recordset

Do
Get Value
Assign Parameter Value
Open the Recordset
'...
Close the Recordset
Loop

Set Recordset to Nothing
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

If you have a lot of values to process fast, I would do:

[pre]
Set the Recordset
Open the Recordset with all Values

Do
Get Value
Filter the Recordset Where SomeField = Value
'...
Loop

Close the Recordset
Set Recordset to Nothing[/pre]


---- Andy

There is a great need for a sarcasm font.