Difference between revisions of "ADO Database Connection VB6"
From Free Knowledge Base- The DUCK Project: information for everyone
m (→Example: Read from Access Database and Populate an Array) |
|||
Line 57: | Line 57: | ||
== Example: Read from Access Database and Populate an Array == | == 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 | Public cn1 As ADODB.Connection | ||
Line 87: | Line 91: | ||
Set rs1 = Nothing | Set rs1 = Nothing | ||
End Sub | End Sub | ||
− | + | ||
== Example: Write one Record to Access Database Table (AddNew / Update) == | == Example: Write one Record to Access Database Table (AddNew / Update) == | ||
Revision as of 19: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
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
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.