Looping through tables and using recordset objects

[jaryszek]

Hi Guys,

i have code like here:

Please Login or Register  to view this content.

and have easy question: if i want to do a loop through all the tables (like i have the 5 tables where i would use the similar sql) should i break every time adodb connection and recreate or just use one connection?
If yes how can i do this? Or better practice is to clean connection and recordset ?

Or maybe it is possible to create connection to workbook once but change onlt table name within it?

Please help,
Jacek

[romperstomper]

The connection is to the workbook, not to a specific table. The SQL query is what determines which table you are looking at.

[jaryszek]

thank you Rory,

so i should open conection once and only reset recodsets?

Best,
Jacek

[romperstomper]

It depends on what you are doing. You should ideally not keep a connection open any longer than necessary. If you are opening a connection and running 5 queries one after the other, it doesn't make sense to keep opening and closing the connection.

[jaryszek]

thank you Rory!

Why not to keep it open too long ?

Best,
Jacek

[romperstomper]

Because of file/record locking. If nobody else could be using the file it's probably not an issue but it's a bad habit to get into.

[jaryszek]

thank you !

Best wishes for you,
Jacek