Elegantly connecting to DB with ADO, and executing commands

[stimulus]

So when I started making applications that needed to connect to Access, I would search forums and would end up using something like what's in this;

Please Login or Register  to view this content.

And this would need to be written out every time you want to connect. This is fine when you're starting out, but as the number of connections grows, and if you move databases this will soon become frustrating, as every connection will need to be changed.

I began to wonder how I could make this easier for myself and have come up with the following.

In the VB project I will start by adding a reference to the latest 'Microsoft ActiveX Data Objects' library (probably 6.1 for most users), and creating a module just for declaring things which has the following;

Please Login or Register  to view this content.

Every database that you want to connect to should have it's own public constant. There are other properties that you can include in the connection string, but the above should work just fine (Only tested for Access, but tthe principle should work for any DB that supports ADO as long as a valid connection string is used).

In a separate module, we set up the sub to open the connection to the database.

Please Login or Register  to view this content.

This now means that when we want to execute a command against our DB it is now very simple. All we need to do is to define our sql command to something like;

Please Login or Register  to view this content.

There are of course extra things you can add in like error trapping if you fail to connect etc, but hopefully this can help if you're starting to use DB connections with Access.