The openTheDatabase function is short and sweet. We simply set a reference to our form-level adoConnection variable. Once we set up our connection string, we open the database connection. If we are successful, then the form-level adoConnection object variable is available to any recordset that might need it. Notice that we can get away with a single connection to the database. We return a Boolean to indicate if we were successful or not. If there was an error of some sort, our minimal error handler will tell the user what is wrong and the calling routine will simply exit. Of course, in a real program, you would place a much more detailed error handler in this procedure.
1. Please add a new function to our frmID3 form. We wrap up the code to open the connection to the database in a straightforward function. And since the adoConnection variable has form-level scope, once we open the connection here, we can use it anywhere in the form.
Public Function openTheDatabase() As Boolean
'-- Here we want to open the database
Dim sConnectionString As String
On Error GoTo dbError
sbStatus.Panels.Item(1).Text = "Opening the database."
'-- Set reference to a new connection --
Set adoConnection = New ADODB.Connection
'-- Build the connection string
sConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\BegDB\Nwind.mdb"
adoConnection.Open sConnectionString
openTheDatabase = True
Exit Function
dbError:
MsgBox (Err.Description)
openTheDatabase = False
sbStatus.Panels.Item(2).Text = "Could not open database."
End Function
There is no magic here. This function, which is called from the Load event of frmID3, will simply open the connection to the database for us and return a Boolean of True or False indicating success or failure. Of course, we will also take advantage of our status bar to provide the user with valuable visual feedback in the process.