Difference between revisions of "ADO Database Connection VB6"

Revision as of 11:33, 30 October 2007

The old database access method using Jet has been replaced in VB6 by the new ADO or Active Data Objects. ADO makes it easier to access data from existing databases such as a Microsoft Access database or from an ODBC database connection such as MS SQL or Oracle.

Three Important objects in the ADO object model are:

• Connection Object
• Command Object
• RecordSet Object

Connection Object

Properties

• ConnectionString
• Provider
• Mode
• CursorLocation
• ConnectionTimeout
• CommandTimeout

Methods

• Close
• Open

Command Object

Properties

• ActiveConnection
• CommandText
• CommandType

Methods

• Execute

RecordSet Object

Properties

• CursorLocation
• CursorType
• EOF and BOF
• Fields
• LockType
• RecordCount

Methods

• AddNew
• Close
• MoveNext
• MoveFirst
• MoveLast
• Open
• Update
• UpdateBatch

Example: Read from Access Database and Populate an Array

Public cn1 As ADODB.Connection
txtSourcePath.Text = App.Path & "\Contact.mdb"

Private Sub cmdConnect_Click()
  Dim strConn1 As String
  Set cn1 = New ADODB.Connection
  cn1.ConnectionString = strConn1
  cn1.Open
End Sub

Private Sub MakeCompanyArray()
  Dim strSQL1 As String, cnt As Integer
  Set rs1 = New ADODB.Recordset
  strSQL1 = "SELECT * FROM Company ORDER BY CompanyID"
  rs1.CursorLocation = adUseClient
  rs1.Open strSQL1, cn1, adOpenKeyset, adLockOptimistic
  If rs1.RecordCount > 0 Then
    For cnt = 1 To rs1.RecordCount 
      arCompanies(cnt, 1) = rs1!CompanyID
      arCompanies(cnt, 2) = rs1!CompanyName
      arCompanies(cnt, 3) = rs1!PhoneNum
      arCompanies(cnt, 4) = rs1!Website  
      rs1.MoveNext
    Next cnt
    arCompanies(0, 1) = cnt
  End If
  rs1.Close
  Set rs1 = Nothing
End Sub