ODBC is a data source for a database system such as MS Access. Each proprietary database system produced by different companies, such as Oracle, IBM, Borland or dBase has its own internal methods for storing data; thus you can only access data created in Paradox from within Paradox itself, and so on. Things would be so much better if data from any system could be accessed from any other system, that way, data is compatible between systems and you don't have to worry if a company goes out of business or changes its data format. ODBC helps out here because it provides a way of reading data from one system into another system - just what some companies didn't want but exactly what you want if you are going to avoid entering the data all over again.
Microsoft developed ADO (ActiveX Data Objects) to provide a programming interface for accessing ODBC data sources. There is a two-step method to ODBC and ADO, first finding and opening the source (ODBC) and then manipulating it with a series of commands (ADO). This approach is required if you create a database in a system such as MS Access and then re-locate it in another environment such as the Web, which lacks the framework and tools of Access. A database transferred to the web does not run inside the Access framework but rather in a web server such as Apache (Linux) or Internet Information Services (IIS - Windows). IIS provides the link between the data and the web pages you may create to access and present your data and the means to interpret ODBC and ADO. This is very useful because it does not matter what format you use for your original database, ODBC and ADO can provide the means to access it (though not all proprietary database systems provide ODBC drivers).
In the case of a database on the web, the first thing you need to do is to upload it to your web server and also to make sure that the web server is configured to provide access for the routines you will create. The second thing you will do is to write the web pages, using a server-side scripting language such as ASP (Active Server Pages), JSP (Java Server Pages) or PHP. At this point you may have to take time out to learn how to create web pages, though you could choose to stay with this text anyway and use copy/paste to create the web pages until you understand what is going on and can write them for yourself.
When the database has been uploaded on to a suitable configured web server we can carry out some operations on it. The things we want to do on this database are the same as we would do on a database in Access, that is create objects such as tables, records, queries, forms and reports and modify and delete these queries. One important difference about our database when it is in web format is that it is in a multi-user environment so we will have to learn something about locking data while it is being accessed.
For a Microsoft web server (IIS) we use a combination of ODBC, ADO and the script languages ASP (Active Server Pages) and, optionally, JavaScript.
To open an ODBC data source on a web server such as IIS we need to define a connection to it:
openstr = "driver={Microsoft Access Driver (*.mdb)}; "dbq=" & Server.MapPath("music.mdb")
This defines a connection string for opening the database. 'openstr' is the name of the variable which holds the details of the string. The 'driver=' part declares that the type of database is Access (*.mdb); the dbq= portion joins together the two parts of a string, the second being an instruction to find the specified file on the web server. Server.MapPath() finds the specified file without the need for a fully qualified file identifier (such as "G:/InetPub/wwwroot/myweb/soccer/data").
The next step is to declare a variable for the database:
Set cn = Server.CreateObject("ADODB.Connection")
This sets the variable cn to be a database object, created by the Server.CreateObject command with ADODB.Connection as its parameter. ADODB is a database connection.
We now open the database using the cn variable and the openstr variable we created in the first line:
cn.Open openstr
With the ODBC data source now open we can define a SQL query:
sql = "SELECT * FROM CDs ; "
This defines a variable called sql, which contains the SQL code we want to run on the database, selecting all records from the CDs table.
We now create an ADO recordset and then open it with a list of parameters:
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn, 0, 1
The parameters are:
Put together the code looks like this:
<html><head>
<%
openstr = "driver={Microsoft Access Driver (*.mdb)};" & "dbq=" & Server.MapPath("music.mdb")
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open openstr
sql = "SELECT * FROM CDs ; "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "sql", cn, 0, 1
%>
</head><body>
The <% and %> symbols are the delimiters for a section of code in the native VB Script of ASP.
The next section of code is a block of html to define a table and its headers:
<table border=1>
<caption><b>Clubs</b></caption>
<tr>
<th>Serial Number</th>
<th>CD TItle</th>
<th>Artist</th>
<th>Date Released</th>
<th>Label</th>
<th>Category</th>
<th>Number of Tracks</th>
<th>Website</th>
</tr>
Now a loop is started to read the records from the data source:
<%
On Error Resume Next
rs.MoveFirst
Do While Not rs.EOF
%>
This part of the code does three things:
This section of html and ASP code places the data in the fields of the recordset into the data fields of the table on the web page:
<tr>
<td width=200><% =Server.HTMLEncode(rs.Fields("SerialNumber").Value)%></td>
<td width=90 align="right"><% =Server.HTMLEncode(rs.Fields("CDTitle").Value)%></td>
<td width=100><% =Server.HTMLEncode(rs.Fields("Artist").Value)%></td>
<td width=100><% =Server.HTMLEncode(rs.Fields("Label").Value)%></td>
<td width=200><% =Server.HTMLEncode(rs.Fields("Category").Value)%></td>
</tr>
The final piece of ASP moves to the next record until all the records have been read and then closes the recordset and the ODBC connection:
<%
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
%>
</table></body></html>
Put together, these three sections of code read the records from the data source into a html table and then publish this on the user's web browser.
If we set the string to access the Recordset as follows, we can run the SQL query:
<%
openstr = "driver={Microsoft Access Driver (*.mdb)};" &
"dbq=" & Server.MapPath("music.mdb")
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open openstr
sql = "SELECT Title FROM CDs WHERE ((CDs.Artist)='The Beatles');
"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn, 0, 1, 1
%>
The differences here are: