Difference between revisions of "ADO Database Connection VB6"

From Free Knowledge Base- The DUCK Project: information for everyone
Jump to: navigation, search
(New page: 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 Acc...)
 
 
(8 intermediate revisions by one user not shown)
Line 1: Line 1:
 
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.
 
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.
  
−
Important objects in the ADO object model are:
+
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
 +
: <big>Microsoft ActiveX Data Objects 2.7 Library</big>
 +
 +
 +
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) ==
 +
 +
<nowiki>Option Explicit</nowiki>
 +
<nowiki></nowiki>
 +
<nowiki>Public cn1 As ADODB.Connection</nowiki>
 +
<nowiki>Public txtDBPath As String</nowiki>
 +
<nowiki>Public strConn1 As String</nowiki>
 +
<nowiki>Public rs1 As ADODB.Recordset</nowiki>
 +
<nowiki></nowiki>
 +
<nowiki>Private Sub Form_Load()</nowiki>
 +
<nowiki>  txtDBPath = App.Path & "\LeadersCRM.mdb"</nowiki>
 +
<nowiki>  txtMsg.Text = "Application Path: " & txtDBPath</nowiki>
 +
<nowiki>  strConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtDBPath & ";"</nowiki>
 +
<nowiki>  Set cn1 = New ADODB.Connection</nowiki>
 +
<nowiki>  cn1.ConnectionString = strConn1</nowiki>
 +
<nowiki>  cn1.CursorLocation = adUseClient</nowiki>
 +
<nowiki>  cn1.Open</nowiki>
 +
<nowiki>  txtMsg.Text = txtMsg.Text & vbCrLf & "connection established"</nowiki>
 +
<nowiki>  </nowiki>
 +
<nowiki>  Set rs1 = New ADODB.Recordset</nowiki>
 +
<nowiki>  </nowiki>
 +
<nowiki>  With rs1</nowiki>
 +
<nowiki>    .CursorType = adOpenStatic</nowiki>
 +
<nowiki>    .CursorLocation = adUseClient</nowiki>
 +
<nowiki>    .LockType = adLockOptimistic</nowiki>
 +
<nowiki>    .Open "Contact", cn1</nowiki>
 +
<nowiki>  End With</nowiki>
 +
<nowiki>  </nowiki>
 +
<nowiki>  rs1.AddNew</nowiki>
 +
<nowiki>  rs1!FirstName = "Savannah"</nowiki>
 +
<nowiki>  rs1!LastName = "Meowface"</nowiki>
 +
<nowiki>  rs1!Position = "Cat"</nowiki>
 +
<nowiki>  rs1.Update</nowiki>
 +
<nowiki>  </nowiki>
 +
<nowiki>  rs1.Close</nowiki>
 +
<nowiki>  </nowiki>
 +
<nowiki>  Set rs1 = Nothing</nowiki>
 +
<nowiki>  </nowiki>
 +
<nowiki>End Sub</nowiki>
 +
<nowiki></nowiki>
 +
<nowiki>Private Sub Form_Unload(Cancel As Integer)</nowiki>
 +
<nowiki>  Set cn1 = Nothing</nowiki>
 +
<nowiki>End Sub</nowiki>
 +
 +
== 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.
 +
 +
&nbsp;
  
 
[[Category:Computer Technology]]
 
[[Category:Computer Technology]]
 
[[Category:Programming]]
 
[[Category:Programming]]
 
[[Category:VB6]]
 
[[Category:VB6]]

Latest revision as of 06:58, 2 February 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.

 

Retrieved from "https://wiki.robotz.com/index.php?title=ADO_Database_Connection_VB6&oldid=1982"