Database connection in VBA

[fdmaxey]

Newbie question: In a project with a number of subroutines and functions that query and update a database, is there an advantage (or disadvantage) to having just one global database connection that is opened upon initialization of the project and stays open until the project is closed?

 

[Jerry Sullivan]

The advantage is that the code might run somewhat faster. The disadvantage is it might lose the connection so the code becomes more complex as you need to validate the connection each time you use it and restore it if it has been lost.

You might consider using Power Query to import and transform your data. I haven't used VBA queries since I started using PQ. It' relatively easy to learn (easier than VBA).

 

[fdmaxey]

Many thanks for the helpful reply. Unfortunately, I have to stick with VBA in this case.