Tutorials And Tips2

BASICS ABOUT ADO

You people are there just to learn the basics about ADO means no modules ,no finances just a project that connects to a database, displays you the data and some buttons.

Now most of you have probably experimented with a data control or two and heard that they aren’t the best way to connect to a database and thought about the move to ADO but didn’t quite know where to start.

first look below!!! And be patient I will make you understand inshallah very fast.

START
Even slow but steady is a good start

Ingredients
1 MS Access database (in this article it will be referred to has DB1)
1 MS Access Table (in this article it will be referred to has table1)
1 VB Project

Persumptions
I will persume you have made a database in MS Access 2000 and have 1 table for the sake of this article we’ll call the table “tbl_master”. This table has only 3 fields though as you will see it sint hard to add or take.

METHOD
First you’ll need to start up Visual Basic and choose Standard Exe project.
Now you’ll have a blank form in front of you. Before we add anything onto or into this form we shall:
1) Go up to the “Projects” Menu click on it.
2) Go down to “References” and click it.
3) Once in the references we will now go and tick “Microsoft Active Data Objects 2.5 Library” (2.5 is just a version you can use whatever version you have).
4) Select Ok and we have a reference to ADO!!

Ok lets get some of the easy stuff done. Lets go and add onto our Form in Visual Basic:
4 Command buttons (cmdAdd, cmdDelete, cmdNext and cmdPrev), 2 text boxes(text1, text2) and 1 combo box(combo1).

It should look a little something like this:

Declarations
Before we do any coding on events lets make some declarations at the top.
Code:
Option Explicit
Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset ‘this is the recordset

Events
Lets code all the events and I’ll explain along the way.

First the Form_Load event:
Code:
Private Sub Form_Load()
Me.MousePointer = 11 ‘this makes the mouse pointer the hourglass

Set cn = New ADODB.Connection ‘we’ve declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\my documents\DB1.mdb" ‘this is the connection string explained in the notes section.
cn.Open
Set rs = New ADODB.Recordset ‘as we did with the connection
rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable ‘opening the recordset explained in the notes

rs.movefirst ‘moves to the first record
Do until rs.eof = true ‘this is the Loop to add items to the combo box
Combo1.additem rs.fields(“field1”) ‘this adds items from field1 into the combo box
rs.movenext ‘moves next record
Loop
rs.movefirst
fillfields ‘i’ll explain this later on.

Me.MousePointer = 0 ‘sets the mouse pointer to the normal arrow
End Sub

Notes:
1) Connection: The first part Set cn = New ADODB.Connection tells VB that cn is a New ADODB connection.
a) The part“Provider=Microsoft.Jet.OLEDB.4.0;” is setting the provider to the Jet Engine and version 4.0.
b) The part "Data Source= c:\my documents\DB1.mdb" sets the connections data source to the database required.
c) The part cn.Open opens the connection to the specified database.

2) Recordset: The first part Set rs = New ADODB.Recordset tells VB that rs is a New ADODB recordset.
a) The part rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable opens the recordset to the table tbl_master using the connection “cn” with the other options.

Phew form load is done now we can go onto the all important fillfields sub

FillFields
This fills the boxes with the data from the recordset.
Code:
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
MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
End If
End Sub

Simple hey??

Ok almost there lets do the command buttons:

Move Previous
Private Sub cmdPrev_Click()
If Not (rs.BOF = True) Then
Rs.moveprevious ‘move previous record
Fillfields ‘fill the controls
End If
End Sub

Move Next
Private Sub cmdNext_Click()
If Not (rs.EOF = True) Then
Rs.movenext ‘move to next record
Fillfields ‘fill the controls
End If
End Sub

Add New Record
Private Sub cmdAdd_Click()
With rs
.AddNew ‘adding new record
.Fields("field2") = text1.text ‘setting field2 = whatever is typed in text1
.Fields("field3") = text2.text ‘as above
.Fields("field1") = combo1.text ‘as above
.Update ‘this updates the recordset etc.
End With
End Sub

Deleting a Record
Private Sub cmdDelete_Click()
If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then ‘check if you really want to delete this record
Exit Sub ‘exit the command
Else
If Not (rs.BOF = true Or rs.EOF = true) Then
rs.Delete ‘delete the current record
If Not (rs.BOF = true Or rs.EOF = true) Then
rs.MoveNext ‘move next
If rs.EOF Then rs.MoveLast
Fillfields
End If
End if
End If
End Sub

Now we’ve done all that lets cleanup our code when the form is unloaded.

Unload
Private Sub Form_Unload(Cancel As Integer)
Rs.close ‘close the recordset
Cn.close ‘close the connection
Set rs = nothing ‘set them to nothing
Set cn = nothing ‘as above
End Sub

Well that’s it boys and girls you can now view, add, delete and edit your data in code using ADO.
Good day, good afternoon and if I don’t see you in the forums good night!
Ralph-vb

:cool: