Connecting with Microsoft Access

Up to this point, you have been coding inside a form module. The module is tied to the form, which is, in turn, tied to Microsoft Access. However, when you program in a standard module, that module may only assume that you are talking about the current database.

Remember, VBA is not really part of Access. The code you write in a standard module could be connected to any database anywhere. For that reason, you usually need to insert information showing what database it should be connected to, even if it is the current database.

If you do not have a standard module, perhaps called Module1, open a new one by right-clicking inside the Project Explorer and selecting Insert | Module. Once there, put the following code into it:

Sub openForm()
  DoCmd.openForm "frmCustomer"
End Sub

If you go ahead and run this code, using the Run button on the Standard toolbar, it will open the form. Once the form is active, any VBA code in the form module will take over and control the form from there.

This is not great programming, however, since the module assumes you are connected to the current database. It is far better to formally put connection code into it. We will do this as a string and tell the module that the connection will be to the current project.

To use this code, you must first make sure that you have the references shown in Figure 8-8 turned on. You access this dialog by using Tools | References located in the VBA Editor. From here on, you will need to use this code pro forma.

Sub openForm()
  Dim con As ADODB.Connection
  Set con = CurrentProject.Connection
  DoCmd.openForm "frmCustomer"
End Sub

Figure 8-8: References for Access Programming

Remember, an object is an entity located somewhere in memory. In order to use it, there must be a pointer, called the object reference, to that memory location.

The first line of the preceding code states that the word “con” will be an object reference that will point to the Connection object accessed in the ADODB library. The Set command actually assigns the reference to the object. It is similar to a variable: first you declare it, and then you assign a value to it. You will learn more about this as we progress through the book. The code should run the same as before.