Step 1: Coding the common DB Connection in a module

Before starting, make sure that you have the appropriate Project references to use ADO and related components.  Under Project-References, select the following:

Now you are ready to start the actual coding.

1.1 Define a module to establish the ADO database connection (module is called "modData" here)

1.2 Declare the database connection:

Global db As New ADODB.Connection

1.3 Event to open database connection (in module "modData" here):

Note: the code below could be changed slightly to open a Jet database (instead of ODBC shown here). Also, the database name could be added as another argument to routine.

Public Function f_OpenDB(ByVal asUserid As String, ByVal asPassword As String) As Boolean
Dim lsDbOpen As String

On Error GoTo OpenErr

' open ODBC database using supplied userid and password
db.CursorLocation = adUseClient
lsDbOpen = "PROVIDER=MSDASQL;dsn=the_database;"
lsDbOpen = lsDbOpen & "uid=" & asUserid & ";pwd=" & asPassword & ";"
db.Open lsDbOpen
f_OpenDB = True
Exit Function

OpenErr:
  MsgBox Err.Description
  f_OpenDB = False

End Function

Step 2: Invoking Database Connection Code during application startup

From the open event of the application’s MDI Form_Load event (or in the application’s first window form_load), use one of the following approaches to invoke the database connection code.

2.1 Simple Approach

2.2 Logon window Approach

In order to have a form use the application-level database connection, you must specify the application connection object (defined as "db", our global variable) in the Recordset.open.  This is shown in the code below.

Dim sSQL As String

' set retrieve SQL
sSQL = "select product_id,product_desc from product"

' create a recordset
Set datPrimaryRS = New Recordset
datPrimaryRS.CursorLocation = adUseClient

' as the recordset is opened, associate the recordset with the app-level connection
datPrimaryRS.Open sSQL, db, adOpenForwardOnly, adLockReadOnly

' now have the control on the form use the recordset that has been defined
' (to re-use db connections, controls must not specify a recordset during design time)
Set MSHFlexGrid1.DataSource = datPrimaryRS