You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

How To Open ADO Connection and Recordset Objects

This article was previously published under Q168336
For a Microsoft Visual Basic .NET version of this article, see 310985.
SUMMARY
ActiveX Data Objects (ADO) offers several ways to open both the Connection and Recordset objects. This article presents sample code for several common techniques for each object.
MORE INFORMATION
There are several ways to open a Connection Object within ADO:
  • By Setting the ConnectionString property to a valid Connect string and then calling the Open() method. This connection string is provider- dependent.
  • By passing a valid Connect string to the first argument of the Open() method.
  • By passing a valid Command object into the first argument of a Recordset's Open method.
  • By passing the ODBC Data source name and optionally user-id and password to the Connection Object's Open() method.
There are three ways to open a Recordset Object within ADO:
  • By opening the Recordset off the Connection.Execute() method.
  • By opening the Recordset off the Command.Execute() method.
  • By opening the Recordset object without a Connection or Command object, and passing an valid Connect string to the second argument of the Recordset.Open() method.
This code assumes that Nwind.mdb is installed with Visual Basic, and is located in the C:\Program Files\DevStudio\VB directory:
   Option Explicit   Private Sub cmdOpen_Click()       Dim Conn1 As New adodb.Connection       Dim Cmd1 As New adodb.Command       Dim Errs1 As Errors       Dim Rs1 As New adodb.Recordset       Dim i As Integer       Dim AccessConnect As String       ' Error Handling Variables       Dim errLoop As Error       Dim strTmp As String       AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _                     "Dbq=nwind.mdb;" & _                     "DefaultDir=C:\program files\devstudio\vb;" & _                     "Uid=Admin;Pwd=;"       '---------------------------       ' Connection Object Methods       '---------------------------       On Error GoTo AdoError  ' Full Error Handling which traverses                               ' Connection object       ' Connection Open method #1:  Open via ConnectionString Property       Conn1.ConnectionString = AccessConnect       Conn1.Open       Conn1.Close       Conn1.ConnectionString = ""       ' Connection Open method #2:  Open("[ODBC Connect String]","","")       Conn1.Open AccessConnect       Conn1.Close       ' Connection Open method #3:  Open("DSN","Uid","Pwd")       Conn1.Open "Driver={Microsoft Access Driver (*.mdb)};" & _                  "DBQ=nwind.mdb;" & _                  "DefaultDir=C:\program files\devstudio\vb;" & _                  "Uid=Admin;Pwd=;"       Conn1.Close       '--------------------------       ' Recordset Object Methods       '--------------------------       ' Don't assume that we have a connection object.       On Error GoTo AdoErrorLite       ' Recordset Open Method #1:  Open via Connection.Execute(...)       Conn1.Open AccessConnect       Set Rs1 = Conn1.Execute("SELECT * FROM Employees")       Rs1.Close       Conn1.Close       ' Recordset Open Method #2:  Open via Command.Execute(...)       Conn1.ConnectionString = AccessConnect       Conn1.Open       Cmd1.ActiveConnection = Conn1       Cmd1.CommandText = "SELECT * FROM Employees"       Set Rs1 = Cmd1.Execute       Rs1.Close       Conn1.Close       Conn1.ConnectionString = ""       ' Recordset Open Method #3:  Open via Command.Execute(...)       Conn1.ConnectionString = AccessConnect       Conn1.Open       Cmd1.ActiveConnection = Conn1       Cmd1.CommandText = "SELECT * FROM Employees"       Rs1.Open Cmd1       Rs1.Close       Conn1.Close       Conn1.ConnectionString = ""       ' Recordset Open Method #4:  Open w/o Connection & w/Connect String       Rs1.Open "SELECT * FROM Employees", AccessConnect, adOpenForwardOnly       Rs1.Close   Done:       Set Rs1 = Nothing       Set Cmd1 = Nothing       Set Conn1 = Nothing       Exit Sub   AdoError:       i = 1       On Error Resume Next       ' Enumerate Errors collection and display properties of       ' each Error object (if Errors Collection is filled out)       Set Errs1 = Conn1.Errors       For Each errLoop In Errs1        With errLoop           strTmp = strTmp & vbCrLf & "ADO Error # " & i & ":"           strTmp = strTmp & vbCrLf & "   ADO Error   # " & .Number           strTmp = strTmp & vbCrLf & "   Description   " & .Description           strTmp = strTmp & vbCrLf & "   Source        " & .Source           i = i + 1        End With       Next   AdoErrorLite:       ' Get VB Error Object's information       strTmp = strTmp & vbCrLf & "VB Error # " & Str(Err.Number)       strTmp = strTmp & vbCrLf & "   Generated by " & Err.Source       strTmp = strTmp & vbCrLf & "   Description  " & Err.Description       MsgBox strTmp       ' Clean up gracefully without risking infinite loop in error handler       On Error GoTo 0       GoTo Done   End Sub				

ERROR NOTES

Only the ADO Connection object has an errors collection. The observant reader will notice that a lightweight error handler is in effect for the RecordSet.Open examples. In the event of an error opening a RecordSet object, ADO should return the most explicit error from the OLEDB provider. Some common errors that can be encountered with the preceding code follow.

If you omit (or there is an error in) the DefaultDir parameter in the connect string, you may receive the following error:
ADO Error # -2147467259
Description [Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)'
isn't a valid path. Make sure that the path name is
spelled correctly and that you are connected to the server
on which the file resides.
Source Microsoft OLE DB Provider for ODBC Drivers
If there is an error in the Dbq parameter in the connect string, you may receive the following error:
ADO Error # -2147467259 Description [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't find
file '(unknown)'.
Source Microsoft OLE DB Provider for ODBC Drivers
The preceding errors also populate the Connection.Errors collection with the following errors:
ADO Error # -2147467259
Description [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
Source Microsoft OLE DB Provider for ODBC Drivers
ADO Error # -2147467259
Description Login Failed
Source Microsoft OLE DB Provider for ODBC Drivers
Note that for each error, the ADO Error number is the same, in this case translating to 0x80004005, which is the generic E_FAIL error message. The underlying Component did not have a specific error number for the condition encountered, but useful information was never-the-less raised to ADO.
REFERENCES
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
301216 How To Populate a DataSet Object from a Database by Using Visual Basic .NET
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
188857 PRB: Use Open Method to Change CursorType and LockType
194979 INFO: ADO Spawns Additional Connections to SQL Server
168335 INFO: Using ActiveX Data Objects (ADO) via Visual Basic
193332 FILE: MDACCON.EXE Using Connection Strings with ODBC/OLEDB/ADO/RDS
Properties

Article ID: 168336 - Last Review: 03/02/2005 19:22:48 - Revision: 3.5

  • Microsoft ActiveX Data Objects 1.0
  • Microsoft ActiveX Data Objects 1.5
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • kbhowto KB168336
Feedback
meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> >