Frequently asked questions for self help.

Knowledge base article 72

How do I connect to an Ability Database with ASP?

So you've got Microsoft Internet Information Server (IIS) and you want to use ASP to interact with an Ability Database. For the purposes of this exercise, you can treat an Ability database in exactly the same way as a Microsoft Access database. IIS ships with drivers for Jet databases and so there's no requirement for an ODBC driver or any other software to be installed on the server.

There is a small caveat applying to Ability Office 98 only: the database has proprietary field types not supported by Jet: Calculated, Enumerated and Incremental will be treated as Character fields (numeric for Incremental).

Below is a compete listing for an ASP page that will a) open an Ability Database and b) dump a specified table into a web page. The code assumes:

"test.adb"  - database name
"mytable" - table name in database
"/database/" - server directory (location of database)

The code does not require a Data Source Name (DSN) to be setup on the server since it explicitly states the database driver and name. It would be better practice to switch to a DSN in the longer term.

Dim cnnAB, rsAB, i

Response.Write "<p>Opening database...</p>"

Set cnnAB = Server.CreateObject("ADODB.Connection")
cnnAB.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & _ 
Set rsAB = Server.CreateObject("ADODB.Recordset")
rsAB.Open "SELECT * FROM MyTable" , cnnAB, 3

Response.Write "<p>And now dump the table to screen in a table...</p>"
Response.Write "<p><table border=1><tr>"

For i=0 To rsAB.Fields.Count -1
 Response.Write "<th>" & rsAB(i).Name & "</th>"

Response.Write "</tr>"

Do While Not rsAB.EOF
 Response.Write "<tr>"
 For i = 0 To rsAB.Fields.Count -1
  Response.Write "<td valign=top>" & rsAB(i) & "</td>"
 Response.Write "</tr>"

Response.Write "</table>"