Creating an Access database
First we will create a database in Access called Employees.mdb. This will have a table called Employee and here is what it should look like:
Insert the following values into the tables:
The Connection object
The first thing to do before you can use a database is create a Connection object.
Set cn = Server.CreateObject("ADODB.Connection")
Next set the ConnectionString property of the Connection and open the Connection.
cn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & Server.MapPath("Employees.mdb")
The RecordSet object
We now need a RecordSet object to store the results of a query. You must create the RecordSet in a way similar to how you created a Connection.
Set rs = Server.CreateObject("ADODB.RecordSet")
Before you can go on with the next step you need to copy C:\Program Files\Common Files\System\ado\adovbs.inc to C:\Inetpub\wwwroot. Also add the following line after <%Option Explicit%>:
Now we can open the RecordSet like this:
rs.Open "SELECT * FROM Employee", cn, adOpenStatic, adLockOptimistic
The first parameter in quotes after rs.Open is a SQL statement that says all records from the Employee table must be retrieved. The second parameter is the connection. The Third parameter is the CursorType and the Fourth parameter is the LockType.
Reading from a RecordSet
Now that we have opened the RecordSet we can read values from it. We will use a loop to write the first name of each employee in the Employee table on the page.
While Not rs.EOF
Response.Write rs("FirstName") & "<br>"
The Find command can be used to find a specific record.
rs.Find "LastName = 'King'"
Writing to a RecordSet
You can change the value of a field in the RecordSet by setting its value in the same way as you set the value of a variable. After you have set the value you have to use the Update command or the changes will not happen.
rs("FirstName") = "Mike"
You must always close all Connections and RecordSets when you are finished using them. You must also set them equal to Nothing. If you do not then they will stay in the server's memory until it has not memory left.
Set rs = Nothing
Set cn = Nothing