If you want to access a database multiple times, you should establish a connection using the Connection object. You can also make a connection to a database by passing a connection string via a Command or Recordset object. However, connecting in this manner is only appropriate for one single query.
Syntax
Connection Strings
ADO offers several ways to connect to and open a data source. The following examples are for demonstration purposes only. The connection string that you choose will depend on your data source. Some of the connection strings below may work for the same data source. You should note that you must change elements such as database name, server name, database location, Data Source Name (DSN), etc…
Microsoft Access
DSN-less
System DSN
OLE DB
MS SQL
DSN-less
System DSN
OLE DB
MySQL
DSN-less
System DSN
Example
In this example, we are going to connect to a table in a MySQL database called “Employees”. We will use a DNS-less connection. We will run a SQL Select Query and capture the results in a recordset. Finally, extract the information from the recordset and display the results in a table.
Cleaning Up
After you create an instance of the Connection object and open the connection, you access the data source and collect the information into a recordset. After you are finished working with the data, you should close the active connection and clean up your objects. Open ADO objects just before they are needed, and close them right after you are done. This frees resources while other logic is processing. It’s also just good programming practice.
