Programming Using Access Database

Add This To Del.icio.us Share on Facebook StumbleUpon This Add to Technorati Digg This

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:-

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.

Example:-

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]

For Further Reading,

Software Developing