Difference between revisions of "ADO Database Connection VB6"

From Free Knowledge Base- The DUCK Project: information for everyone
Jump to: navigation, search
Line 88: Line 88:
 
  End Sub
 
  End Sub
 
    
 
    
 +
== Example: Write one Record to Access Database Table ==
  
−
 
+
<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>
  
 
&nbsp;
 
&nbsp;

Revision as of 14:47, 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

Example: Write one Record to Access Database Table

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

 

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