Using ADO to connect to an IBM DB2, Microsoft SQL Server or Oracle database

Resolving The Problem

To connect to an IBM DB2, Microsoft SQL Server or Oracle database using Active X data objects:

1. Ensure that an ODBC connection exists for the database. To verify this
1. Click the Start Button and select Settings > Control Panel > Administrative Tools > Data Sources (ODBC)
2. There should be a data source there that uses the driver
1. "IBM DB2 ODBC DRIVER" for an IBM DB2 database
2. "SQL Server" for a Microsoft SQL Server database, or
3. "Oracle ODBC Driver" for an Oracle database
2. Declare and instantiate 2 variables of type "object" to represent instances of the ADOB.Connection and ADOB.Recordset classes, for example:
   
   Dim conn As Object
   Dim rs As Object
   Set conn = CreateObject("ADODB.Connection")
   Set rs = CreateObject("ADPDB.Recordset")
   
   
3. Call the open method of the ADODB.Connection object:
   
   conn.open "%ODBC DATA SOURCE NAME%", "%DB LOGIN%", "%PASSWORD%"
   
   Replace the values above with
   
   %ODBC DATA SOURCE NAME% with the Data source from Step 1
   %DB LOGIN% with the login used to access the database
   %PASSWORD%
   
   
4. Call the "open" method of the ADODB.Recordset object:
   
   rs.open "%SQL STATEMENT%", conn
   
   Replace the value of %SQL STATEMENT% with a valid SQL statement.
   
5. Close the ADODB.Recordset and ADODB.Connection objects:
   
   rs.close
   conn.close

'Declare variables to hold new objects
Dim conn as Object 'Variable for ADODB.Connection object
Dim rs as Object 'Variable for ADODB.Recordset object

'Instantiate variables
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'Open connection to DB2 database
conn.open "db_DSN", "username", "password"

'Open connection to recordset
rs.open "select name from master_dbs where master_dbid = 16777220;", conn

'Close recordset and connection objects
rs.close
conn.close