Difference between revisions of "ADO Database Connection VB6"

Revision as of 18:59, 30 January 2008

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

References requires in Visual BASIC 6.0

Microsoft ActiveX Data Objects 2.7 Library

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

Example: Write one Record to Access Database Table (AddNew / Update)

Option Explicit

Public cn1 As ADODB.Connection
Public txtDBPath As String
Public strConn1 As String
Public rs1 As ADODB.Recordset

Private Sub Form_Load()
  txtDBPath = App.Path & "\LeadersCRM.mdb"
  txtMsg.Text = "Application Path: " & txtDBPath
  strConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtDBPath & ";"
  Set cn1 = New ADODB.Connection
  cn1.ConnectionString = strConn1
  cn1.CursorLocation = adUseClient
  cn1.Open
  txtMsg.Text = txtMsg.Text & vbCrLf & "connection established"
  
  Set rs1 = New ADODB.Recordset
  
  With rs1
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open "Contact", cn1
  End With
  
  rs1.AddNew
  rs1!FirstName = "Savannah"
  rs1!LastName = "Meowface"
  rs1!Position = "Cat"
  rs1.Update
  
  rs1.Close
  
  Set rs1 = Nothing
  
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Set cn1 = Nothing
End Sub

Example: Obtaining Autonumber Value After Using Recordset AddNew

 Set rs1 = New ADODB.Recordset
 rs1.CursorLocation = adUseClient
 rs1.Open "TableName", cn, adOpenKeyset, adLockOptimistic
 rs1.AddNew
   rs1!FirstName = Trim("" & txtCon(0).Text)
   rs1!LastName = Trim("" & txtCon(1).Text)
 rs1.Update
 NewCID = rs1!CID
 rs1.Close

The variable NewCID now has the autonumber value. CID is the name of the table field that is set to Autonumber and is also the key field for the table.