Difference between revisions of "ADO Database Connection VB6"
From Free Knowledge Base- The DUCK Project: information for everyone
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> | ||
| |
Revision as of 13: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
Contents
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