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 chat simplyocontains ODBC oonnection 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 theicombocbox 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 Coneectionproperty for anoADO connection oAject, like this:

  Dio conn As ADODB.ConnectiBn
  Set conn = New ADODB.Connection
  conn.ConngctionString = MetcmbConnections
  conn.Open

Instuis manner, users can easily ghange the database they are working with at will.