You are Here: FAQ Scripting and Programming Languages Access Database Article #2

WebHosting Microsoft-Edition This Article is for 1&1 Microsoft Web Hosting Only.

How to make a connection to Access Database using ASP script

            
<html>
<title>Database query using ASP</title>
<body bgcolor="FFFFFF">
<h2>Query table <b>Products</b> with ASP</h2>
<%
Set dbaseConn = Server.CreateObject("ADODB.Connection")
dbaseConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("\db\products.mdb") & ";"
SQLQuery = "SELECT * FROM PRODUCTS"
Set RS = dbaseConn.Execute(SQLQuery)
%>
<%
Do While Not RS.EOF
%>
<%=RS("name")%>, <%=RS("description")%>, <%=RS("price")%> DM
<p>
<%
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
dbaseConn.Close
Set dbaseConn = Nothing
%>
</body>
</html>

A database query is similar to a phone call. You pick up the receiver and dial a
number. As soon as the other person has answered, you ask your questions(query) and
receive the appropriate answer. BUT do not forget to hang up!
So - in order to make a query to a database, you need a connection. The target
telephone number is the "Data Source Name" (DSN) on the server.

DSN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("\db\products.mdb") & ";"

Server.Mappath is the most important one. By default all Microsoft packages have "db"
folder. You have to place your databases(.mdb) in that folder only.

Please check the path "\db\products.mdb". If your script is in a subdirectory, then
you have to relatively reference the path. For e.g. ..\db\products.mdb
..\ means, you are coming up one level above the subdirectory and then accessing the
db folder.

(Note: There are many different ways of creating a connection to the database, but
for the sake of simplicity we will only use one here. As far as performance is
concerned, there is not much difference between the various methods, so the final
choice is a matter of personal preference.)

Set dbaseConn = Server.CreateObject("ADODB.Connection")
dbaseConn.Open(DSN)

These statements open a connection to the database. You can now query the
database. The connection can stay open for as long as queries are being asked.

dbaseConn.Close
Set dbaseConn = Nothing

It is very important to close the connection once you are done querying the
database. Only then will the memory resources be released. It is always good to
have very minimum connections open.

Avoiding Errors
Never under any circumstances do the following to determine the number of lines
received:
Do While Not RS.EOF
i = i + 1
RS.MoveNext
Loop
RS.MoveFirst

The system will have to go through the entire record and all its columns without
using any of the data in there. This takes time, the page takes longer to load, and
the database connection is open for longer (as already mentioned, there is a limit
to the number of connections that can be open at the same time).

To get the number of line received you can issue database's own command:
strQuery = "SELECT Count(name) FROM example table WHERE name = 'John'"
RS_2.Open strQuery
RS_2(0) then issues the number of lines.

Client cursor
"Client cursor" can be set up using the feature RS.CursorLocation. This instantly
copies all of the data that is required in the Recordset. The cursor is then put on
the client rather than, as is usual, on the server. The advantage of this is that
the connection can be closed immediately after the query. But it may slow down the
performance a little. So if it is not absolutely essential, the cursor really
should stay on the server (default).

Arranging several queries
If you use several queries/Recordsets (ideally with the same connection) in a page,
do try to close the various queries as promptly as possible instead of closing the
Recordsets at the end of the final query. The webserver does not process your page
as a whole, but rather in lines.

Buffer variables
The most efficient way of working is to write database output (records) in
variables at the outset so they can be read later. Buffers such as these can really
save a lot of time, even with smaller charts of less than 10 lines. Often the
database connection can be closed before the webserver sends even a single
character to the client. For instance, the functions RS.GetRows (data in a two-
dimensional array) or RS.Save (Recordset as a Stream or FileObject).

Disclaimer: 1&1 provides the scripts and related information on this page as a courtesy, subject to 1&1's General Terms and Conditions of Service (the "GT&C"). As set forth in more detail in the GT&C, the scripts and information are provided "as-is", without any warranty, and 1&1 is not liable for any damages resulting from your use of the scripts or information.

Print Article

Feedback

Overview	Search
Sitemap	Glossary