3 Attachment(s)
Database - ADO Tutorial for Classic VB
ADO stands for ActiveX Data Objects. This technology is used to connect to databases and manipulate records stored in those database. This tutorial presents the basic operations of ADO.
To get started here are the steps:
- Start Visual Basic
- Choose the "Standard EXE" option and click OK
- Add a reference to "Microsoft ActiveX Data Objects 2.x Library". Goto to Project menu then select the References sub-menu. Here's a figure of how the References dialog box looks like:
Attachment 68097 - Add the following controls in your form:
- 4 Command buttons (cmdAdd, cmdDelete, cmdNext and cmdPrev)
- 2 text boxes(text1, text2)
- 1 combo box(combo1).
It should look like this:
Attachment 68099
- Use the codes listed below.
The two ADO objects we are going to use for this demo are the Connection object and Recordset object. The Connection object is used to connect and communicate with databases like MS Access, SQL Server and Oracle to name a few and in this tutorial we are going to use MS Access as our database. The recordset object is used to retrieve and manipulate records in our database, with the Recordset object we could Add, Update and Delete records and this tutorial will show you how.
VB Code:
Option Explicit
Private cn As ADODB.Connection 'this is the connection object
Private rs As ADODB.Recordset 'this is the recordset object
This is the entry point of our program. It is where we are instantiating our ADO objects, setting their parameters and opening them. The Connection objects' most important property or parameter is the ConnectionString, this is where we specify how we are going to connect and to what database. For a good resource of different Connectionstrings used to connect to different databases have a look at www.connectionstrings.com. The recordset object has different parameters that we have to supply, for a detailed explanation of the those parameters have a look at this FAQ Article: What do the parameters of the recordset.Open method mean?.
VB Code:
Private Sub Form_Load()
'turn MousePointer to HourGlass to show that we are busy processing
Me.MousePointer = vbHourglass
'instantiate the connection object
Set cn = New ADODB.Connection
'specify the connectionstring
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\DB1.mdb"
'open the connection
cn.Open
'instantiate the recordset object
Set rs = New ADODB.Recordset
'open the recordset
With rs
.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable
'loop through the records until reaching the end or last record
Do While Not .EOF
Combo1.AddItem rs.Fields("field1")
rs.MoveNext 'moves next record
Loop
If Not (.EOF And .BOF) Then
rs.MoveFirst 'go to the first record if there are existing records
FillFields 'to reflect the current record in the controls
End If
End With
Me.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
End Sub
Here we are adding to the database the values from the two textboxes and the combobox. I added a small routine to disallow adding if there is no value in Text1.
VB Code:
Private Sub cmdAddNew_Click()
'Just a simple demo to validate invalid inputs.
'In this one we will not allow a blank entry for text1
If Text1.Text = "" Then
MsgBox "Please specify value for text1! ", vbExclamation, "Invalid Input"
Text1.SetFocus 'return focus to Text1
Exit Sub
End If
rs.AddNew 'adding new record
rs.Fields("field2") = Text1.Text 'setting field2 = whatever is typed in text1
rs.Fields("field3") = Text2.Text 'as above
rs.Fields("field1") = Combo1.Text 'as above
rs.Update 'this updates the recordset
End Sub
This deletes the current record in our recordset. It checks if there are records before deleting, as such we could tell the user that a delete is invalid if there is no record to delete.
VB Code:
Private Sub cmdDelete_Click()
'determine if the recordset has an existing record or not before confirming the deletion, 'that should be more user-friendly
If Not (rs.BOF = True Or rs.EOF = True) Then
'Confirm if you really want to delete this record.
If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion + vbDefaultButton2, "Delete?") = vbYes Then
rs.Delete
'Credits goes to brucevde for sharing this fix
rs.MoveNext 'Attempt to move to the next record
If rs.EOF Then 'check if the last record was deleted
'then attempt to move to a previous record.
'note that if there are no more records (ie RecordCount is now 0) BOF will get set to True as well
rs.MovePrevious
End If
FillFields
End If
Else
MsgBox "No record is existing, Delete not allowed! ", vbExclamation, "No Record"
End If
End Sub
This moves to the next record.
VB Code:
Private Sub cmdNext_Click()
'check if there are records to move
If Not (rs.BOF = True And rs.EOF = True) Then
rs.MoveNext 'move to next record
'if we did not reach the last record then show it
If Not rs.EOF Then
FillFields 'fill the controls
Else
rs.MoveLast 'go to the last record
MsgBox "Last record reached! ", vbExclamation, "Status"
End If
Else
MsgBox "No record is existing, Move Next not allowed! ", vbExclamation, "No Record"
End If
End Sub
This moves to the previous record.
VB Code:
Private Sub cmdPrev_Click()
'check if there are records to move
If Not (rs.BOF = True And rs.EOF = True) Then
rs.MovePrevious 'move previous record
'if we did not reach the first record then show it
If Not rs.BOF Then
FillFields 'fill the controls
Else
'if we are not on the last record then MoveFirst
If Not rs.EOF Then
rs.MoveFirst 'go to the first record
End If
MsgBox "First record reached! ", vbExclamation, "Status"
End If
Else
MsgBox "No record is existing, Move Previous not allowed! ", vbExclamation, "No Record"
End If
End Sub
This displays the records from our database or resets them if there are no records.
VB Code:
'This fills the controls (textbox and combobox) with the current record from the recordset
Public Sub FillFields()
If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
Text2.Text = rs.Fields("Field3") 'as above
Combo1.Text = rs.Fields("Field1") 'as above
Else
'reset the textbox and combobox if there are no more records
Text1.Text = ""
Text2.Text = ""
Combo1.Text = ""
End If
End Sub
This is the exit point of our demo program.
VB Code:
Private Sub Form_Unload(Cancel As Integer)
'Clean-up procedure
'determine if rs is nothing or not before closing it and setting it to nothing.
'If under some circumstances that rs has been set to nothing anywhere in the form then
'checking its state and closing it would raise an error. A recordset can have different states at a time, it can either be executing and open so we are using a bitwise comparison. Special thanks to si_the_geek for providing this knowledge.
If Not rs Is Nothing Then
'first, check if the state is open, if yes then close it
If (rs.State And adStateOpen) = adStateOpen Then
rs.Close
End If
'set them to nothing
Set rs = Nothing
End If
'same comment with rs
If Not cn Is Nothing Then
If (cn.State And adStateOpen) = adStateOpen Then
cn.Close
End If
Set cn = Nothing
End If
End Sub
For additional guidance have a look at this FAQ Article: ADO Beginners Tutorial, Some Further Steps. And this is a compilation of the different frequently asked questions on ADO.
If you cannot find an answer to your question, please create a new thread in the Database Development forum where other members would be able to help you in your concerns.
