Hack 82. Allow Users to Choose a Back-End Database

Store ODBC connection strings in a table so they are ready to go when needed.

Certain system applications provide more than one database you can interact with. As long as the structure of the various databases and/or their tables is based on a common schema, it is possible to swap the back ends in and out. An easy way to do this is to provide the available connections in a list in which users select the database to use.

Figure 8-17 shows a table that simply contains ODBC connection strings.

On the form in which users select a connection, they just see the friendly names, provided in the table's second column (the first column serves as a way to sort the list). Figure 8-18 shows the form and the combo box from which a connection is selected.

Figure 8-17. A table filled with ODBC connection strings

Figure 8-18. Selecting a database connection

The value of the combo box is used in code to set the Connectionproperty for an ADO connection object, like this:

  Dim conn As ADODB.Connection
  Set conn = New ADODB.Connection
  conn.ConnectionString = Me.cmbConnections
  conn.Open

In this manner, users can easily change the database they are working with at will.