Preconnecting

Establishing a connection takes time and memory on both the client and the server. This section describes several ways you can limit connections and thus reduce the resources your application uses.

Using the connection-management capabilities of the Jet database engine, you can log a user onto a server when your application starts without waiting until the first form opens. Microsoft Jet caches the connection and authentication information and reuses them as needed.

One way to preconnect is to create a form to gather user and password information, construct a connection string containing the values, and use OpenDatabase. For example, the following procedure connects to a server called MyServer and opens a database:

Sub PreConnect (UserName As String, Password As String)
    Dim MyDB As Database, ConnStr As String
    
    ConnStr = "ODBC;DSN=MyServer;DATABASE=MyDatabase;"
    ConnStr = ConnStr & "UID=" & UserName & ";"
    ConnStr = ConnStr & "PWD=" & Password & ";"
    Set MyDB = OpenDatabase("", False, False, ConnStr)
    MyDB.Close        ' Close database but keep connection.
End Sub

If you're not sure which ODBC data source to connect to, you can display a dialog box listing all registered data sources by using "ODBC;" for the connection string argument. Then you can access the actual connection string using the Connect property, which connects you to the server, as in the following example. You can then use the ConnStr value when you create pass-through queries or attach tables.

Sub PreConnect ()
    Dim MyDB As Database, ConnStr As String

    Set MyDB = OpenDatabase("", False, False, "ODBC;")
    ConnStr = MyDB.Connect
    MyDB.Close        ' Close database but keep connection.
End Sub

In both examples, the Close method doesn't disconnect from the server — instead, Microsoft Jet caches the connection internally.