Connecting to Access database using ADO

Connecting to Access database using ADO

We will be using ActiveX Data Objects (ADO) to create a connection to Access database. ADO model contains six objects and we will be using Connection String to connect to database.

Connecting String
Creating a Connection String using ADO and opening the connection To connect to Access database we can create a connection object by using ADO. Here is an example of connection object creation and opening the connection object.

Note that here we have using server MapPath to locate the access database. Here database is stored inside db directory relative to the script running this code. Now we have opened the connection to the access database next is we need one object to handle records.

Recordset Object
To handle records of a MS access table we need recordset object provided by ADO. Using this object we can get a subset of records of our table. We can declare Record set object like this.

By just declaring the recordset object we can’t handle data, we need to open it and during the opening process we can give different arguments to handle the recordset object. Here is the syntax of opening a Recordset object.

As you can see above we have used different arguments and let us try to understand each one with some examples.

Source
This can be the table name or sql query or some other types.

Connection
Connection which we have established before

Cursourtype
This tells how the recodset will move. By default it is forward only.

Locktype
This tell how we handle the record. As there will be more than one client trying to access / update the data. By default it is read only.

Commandtype
Tells how the source parameter to be evaluated. adCmdTable Tells that the source is a table. Let us try to display all month names stored in an access table.

Recordset object property in ASP to browse through the records

We will learn how to collect data from an Access table and display them. We have seen before how to connect to Access table and how to open a recordset object. We will discuss here some methods and properties of recordset object to handle data.

While opening the recordset object we define different parameters and based on that we can handle the data collected by recordset object. Based on the query we apply our recordset object will fetch data for us. Recodset object maintain a pointer to this set of records and this pointer always points to one row or one record at a time. We can move to different locations in different directions by using methods associated with recordset object. We will try to understand some of the methods here.

EOF This is a Boolean value that gets the value True if the record pointer is at the end of a set of records. This property is often used while displaying all records of the recordset object by looping through. Each time at the beginning of the loop EOF property is checked and record is displayed ( inside the loop ) if EOF is not true.

BOF Like EOF (explained above )BOF gets TRUE value if recordset is pointing to the first record of the set of records the recordset object is having.

MoveNext: This moves the pointer by one step in forward direction. It will generate an error if record pointer is at the last record or the EOF property is true.

MovePrevious: This moves the recordset pointer by one step backward. This depends on the Cursourtype we declare at the time of opening the recodset object. We can’t use MovePrevious for a forward-only cursor. MovePrevious will return error if BOF property is TRUE.

MoveFirst: With this we can move the pointer to beginning of the set of records.

MoveLast: Goes to the last record

Move number: Goes (or jumps) to the number of record from the current pointer position.

We will use all the above discussed properties to collect records and display them row wise in our next tutorial.

Displaying records of an Access table

We can display records of a table by looping through all the records of a recordset object. Recordset object properties are discussed before. We have to connect to our Access table using connecting string before collecting our records.

Now let us use our emp table where we have stored our employee details. This table has four fields. Filed name emp_no stores the unique employee number of the employee. Then Name field stores the name of the employee, desg field stores the designation of the employee then the last one dept field stores the department name of the employee.

Using connecting string we can connect and then we will use one sql command to select records from the table.

After opening the recordset object we will use recordset properties and then we will use Do Wile loop to loop through the records of the recordset. Here at the beginning of the each loop the condition of EOF is checked and if NOT TRUE then the loop is executed. Inside the loop we display two fields of each record by using their filed names like this.

This way all the records are displayed and the loop ends if the last record is shown. This is the simplest way to display records of a access table. Here is the output of the script.

Here is the complete code

We have seen how to populate a drop down list box by taking data from a table. Now we will design two drop down list boxes and try to learn how to manage options of the second drop down list box based on the selection of the first drop down list box. There are many such requirements where user select the value in first list and then second list changes to show available options under the selected data of first list. For example if we have a list of states of USA in the first list and the cities list in the second list, then on selection of a state from first list the second list should change to show only the cities of selected state in USA.

Another example is our employee table. In first drop down list box the name of the departments will appear. As we select a department from the first list the second list should show the employees of the selected department only. You can see how the first department list is populated by taking data from the Access table in our dynamic population of list box tutorial . Such requirements are very common and often used in different web sites.

This particular script reloads the browser on selection of the first drop down list box and sends the data back to server. From server the options of the second list box are managed. So we will be using server side scripting languages to manage the operation.

Here we will be using VBScript under ASP environment to develop this script. The same functionality of managing two dropdowns is also developed by using PHP and is available with demo in our PHP section. This can be managed by using client side JavaScript also. The tutorial on managing list boxes at client side using JavaScript is available at JavaScript section. Here the list boxes are managed without sending back the data to server or reloading the page.

Read how Ajax is used to manage two drop down lists in PHP section.

We will not discuss on how to connect to db, how to display data or how to populate list box with table data here. ( It is advisable to learn all these before reading this tutorial ).

We will keep one JavaScript function within the head tag of our page. The main job of this javascript function is to reload the page with the selected data of the first drop down box. Note that as we are using JavaScript to reload the page so we can add a section to check the status of JavaScript ( enabled or disabled ) of the client browser.

Here is the JavaScript function within our head tag.

<SCRIPT language=JavaScript>
function reload(form){
var val=form.dept.options[form.dept.options.selectedIndex].value;
self.location=’emp.asp?dept=’ + val ;
}
</script>

The above code gets executed once the first drop down list box is selected, it collects the selected value of the first drop down list box ( here it is dept ) and then redirects the page with the value of department in the query string. Note that our file name is emp.asp, so once the department name is selected, then the url at the address bar should look like this.

So this way we can get the value of the department and store it in the variable dept.

Now let us create object connection and record set. We will not discuss much on this as the tutorials are available here.

Set objconn = Server.CreateObject(“ADODB.Connection”)
objconn.ConnectionString = “DRIVER=Microsoft Access Driver (*.mdb);DBQ=” & Server.MapPath(“db/emp.mdb”)
objconn.Open

Set objRs = Server.CreateObject(“ADODB.Recordset”)

Now let us populate the drop down list box by taking data from the table.

Note that we have used onchange event handler for the first drop down list box to execute the JavaScript function reload() once an option is selected. This drop down list ( first list ) always shows same elements. Now let us move to second drop down list, where the elements changes based on the value of first list. We will do a check for the variable dept before adding it to the query for second list. We will check the length of the dept variable by using len function of ASP. If the length of the variable is more than 0 that mean there is a department name stored inside. Here is the code for the second drop down list.

Note that The second list is populated once the value for dept is stored by selection of the first list.