Programming Using Access Database:-
There are two parts in the database program:-
a. For entry and display-Form (front end)
b. For save permanently-Table (Backend).
Working with database:-
Working in database means entry records in the backend through the front end and retrieving data in front-end from the backend.
For retrieving and entering data we have to use the object named "Microsoft activex data object" (ADO) component.
Working method of ADO:-
The Ado works by two ways:-
a. Connection.
b. Recordset
Ado model:-
Connection means combining between front end and back end.It need the three methods:-
.open:-
It helps to open the backend for the connection.
provider:-
It helps to connect back and front end using provider i.e. data base engine.
The provider in Microsoft access database is:-
.provider=Microsoft.jet.oledb.4.0
; Data source:-
It helps to avail the full path and location of the database for connection.
Connection syntax:-
Cn.open "provider=Microsoft.jet.oledb.4.0; data source= "Full path""
Recordset:-
Using this method we can retrieve the data from backend to the front end and save the record in backend permanently as well as move the cursor.
The Recordset method contains the following three methods:-
a. open "table"
b. open type
c. Lock type
a. Open "table":-
This method helps to open the table for records retrieve and insert.
b. open type:-
This method helps to move the cursor differently. There are four open type but we can choose the adopendynamic method for cursor dynamicly movement.
C. Lock type:-
This method helps for database security. There are four lock type but we should use adlockpessimistic.
Recordset syntax:-
Rs.open"tablename",cn,adopendynamic, adlockpessimistic
Object creation
Or
Variable declaration for ado:-
For connection:-
Dim cn as new adodb.connection
For recordset:-
Dim rs as new adodb.recordset
Note:-
Cn,rs are variable name.
Working process in ado:-
a. create the backend and frontend.
b. Create the event procedure.
c. Go to tools menu.
d. Click on references
e. Press M and choose any Microsoft activex data object library.
f. Click on ok.
g. Declare the variable (create the object.)
h. Open connection and record set then do the retrieving and inserting work.
Connection:-
Dim cnads As New ADODB.Connection
Private Sub Form_Load()
cnads.Open "provider=microsoft.jet.oledb.4.0;data source=D:\Padam_Datas\Data_Base\MsAccess\Form\Programming\Ado\CSEMGMTFESS\CSEfessmgmt.mdb"
MsgBox "ok"
End Sub
Getting full path process:-
a. Go to run.
b. Click on browse.
c. Search the file/choose all files from file type.
d. Click on file.
e. Click on open.
f. Select and copy the full path.
g. Click on cancel button window.
h. Paste in the data source.
Open record set example:-
Dim cnads As New ADODB.Connection
Dim rsads As New ADODB.Recordset
Private Sub Form_Load()
cnads.Open "provider=microsoft.jet.oledb.4.0;data source=D:\Padam_Datas\Data_Base\MsAccess\Form\Programming\Ado\CSEMGMTFESS\CSEfessmgmt.mdb"
rsads.Open "addmissionstudent", cnads, adOpenDynamic, adLockPessimistic
MsgBox "ok"
End Sub
Retrieving data in front-end from backend:-
To retrieving data from backend to the front-end we have to do as following:-
a. open connection and record set first.
b. Show the "equivalent" between the table’s column and form’s fields.
c. Call the "equivalent" from the record set opened event procedure.
Equivalent showing syntax:-
Sub procedure ()
Textbox.value=rs.fields(index)
End sub
Example:-
Calling equivalent procedure:-
You can call the procedure that was created equivalent from the table and form should be called by event procedure where there is record set opened.
Syntax:-
Private sub cmdopened_click()
Rs.open "……",cn,…
Call [equivalent procedure]
End sub
Cursor movement:-
Cursor movement means searching the next data or previous data.There are four kinds of methods for cursor movement.
a. movenext.
b. Moveprevious.
c. Movefirst.
d. Movelast.
a.Movenext:-
It displays the next data from the existing row.
Syntax:-
Rs.movenext
Call [equivalent procedure]
Example:-
Private Sub cmdnextdata_Click ()
rsads.MoveNext
Call getdata
End Sub
b.moveprevious:-
This method displays previous data from the existing row.
Syntax:-
Rs.moveprevious
Call [equivalent procedure]
c.Movefirst:-
This method displays the first data out of all .
Syntax:-
Rs.movefirst
Call [equivalent procedure]
d.movelast:-
This method displays the last row’s data out of all data.
Syntax:-
Rs.movelast
Call [equivalent procedure]
Error Handing in Cursor movement:-
While moving the cursor there may appear different kinds of errors in moving next and previous .
To control these errors we can use the following two methods:-
i.EOF(end of field) method
ii.BOF(Beginning of field) method
i.EOF(end of field) method:-
This method is used when the cursor is moved next.
Syntax:-
Rs.movenext
If rs.eof=true then
Msgbox "error information…."
Rs.movelast
Else
Call [equivalent procedure]
End if
ii.BOF(Beginning of field) method:-
This method is used to handle the error when we move the cursor previous.
Syntax:-
Rs.moveprevious
If rs.bof=true then
Msgbox "error information….."
Rs.movefirst
Else
Call [equivalent procedure]
End if
Adding new data in recordsource(table):-
To add new data from front-end (form) to the backend (table) we have to use the following methods:-
a. Make empty existing row.
b. Use "rs.addnew" method.
c. Call equivalent to the table.
Make empty:-
Text.value= " "
Equivalent to the table:-
Rs.fields(index)=text.value
Add new method:-
Rs.addnew
Call [equivalent procedure to the table]
