You are Here: FAQ Scripting & Programming ASP Article #7

How do I make a connection to Access Database using ASP?

Microsoft Packages Only!
This includes:

	1&1 MS Home	1&1 Dual Standard Windows
	1&1 MS Business	1&1 Dual Unlimited Windows
	1&1 MS Business Pro	1&1 Dual Business Windows
	1&1 MS Professional

In this guide, we already have a Microsoft Access database created named salesDB.mdb which has been uploaded to an MS Hosting package in the db/ folder. Within this Access database there is one table named tbl_Sales. This table holds the data for recent sales by employees. Look below at the data in the database.

tbl_Sales
Employee	Product	Price	SaleNumber
John	Shoes	$39.99	28637283
Sue	Dress shirt	$41.99	33894673
John	Pants	$34.99	34783263
John	Socks	$4.99	38473936
Sue	Gloves	$7.99	38637286
Bob	Jacket	$74.99	39074730

In this example, we will create an ASP script in VBScript to connect to the Access database, print the price for each sale on the page as well as the total for all sales.

<%
total = 0
src = Server.Mappath("\db\salesDB.mdb")
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open sConnStr
Set RsPriceList = Server.CreateObject("ADODB.recordset")
RsPriceList.Open "SELECT * FROM tbl_Sales", OBJdbConn
Do While NOT RsPriceList.EOF
total = total + RsPriceList("Price")
Response.Write("<p>$" & RsPriceList("Price") & "</p>")
RsPriceList.MoveNext
Loop
Response.Write("The total is: $" & total)
RsPriceList.Close
Set RsPriceList = Nothing
OBJdbConn.Close
Set OBJdbConn = Nothing
%>

The VBScript example above is saved to the root of the webspace. The function Server.Mappath() will output the current folder structure for where the script is saved. The value in parenthesis can be used to specify a file name, subfolder, parent folder, parallel folder, etc. The line

src = Server.Mappath("\db\salesDB.mdb")

indicates that the database file is in a subfolder relevant to where the script itself is saved (the root of the webspace). If the database file was saved in the same folder as the script, the value in quotes would need to be changed to "\salesDB.mdb". If the ASP script itself is saved in a subdirectory, you will need to replace the value in quotes to "..\db\salesDB.mdb". The two dots directs the script "up" one level to the root of the webspace, and then back "down" into the db/ subfolder where the database is held.

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 use the following method to determine the number of lines received:

Incorrect

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 find the number of row in a table, use the following code:

Correct

Set RsRowList = Server.CreateObject("ADODB.recordset")
RsRowList.Open "SELECT COUNT(*) FROM tbl_Sales", OBJdbConn
Response.Write RsRowList(0)

Alternate Connection Scripts
You can replace the sConnStr above to use the Microsoft Access Driver instead if you prefer.

sConnStr = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & src

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