Creating the module

Right, now to get cracking! Create a new module and call it something like modDatabase. What? How do you do that? Well, you see that bank of icons under the file menu? Click the drop down arrow on the second one, and select Module and click open. To rename the Module, click it in the Project Explorer and change its name property in the Properties window.

Right, open up that module by double clicking on it. First things first, let's be tidy and use the Option Explicit statement so that VB will tell us off if we forget to declare any variables.

Now, we must declare the most important variable - the variable that we'll use to manipulate our database. Let's call it db for now. To declare it, add the following line to your module:

Global db As database

Now, we need a procedure which will allow us to bind that variable to the database we wish to use. This is as simple as providing a path to the database to my prewritten LoadDatabase sub:

Public Sub LoadDatabase(strPath As String)
   Set db = OpenDatabase(strPath)
   RefreshSQL
End Sub

So for example, I could load "C:\My Documents\Test.mdb" using the code:

LoadDatabase "C:\My Documents\Test.mdb"

This would bind the db variable to this database.

The RefreshSQL call is to another procedure which we'll use to update references to the tables in the database. You'll need to change this RefreshSQL procedure to suit the database that you're handling. I'll show you an example for the database we've set up later in this article.