Question Locked

How do you script a ASP page to connect to a Access Database?

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have been trying to do this for a class assignment and cannot figure how to script the page to open up the database through the ASP script. I have searched through forums, websites, and Google, to no luck. I feel I have a basic idea of what to do. And I am using this website: https://www.securepaynet.net/domains/search.aspx?prog_id=413450 to host my website. They have everything I need, just not the step by step coding tutorial. I am really excited to learn this, and could use help in this project right away. Thanks for the help. Also see links below.

ASP Script:
http://reseller.netchico.com/419585/help.securepaynet.net/article/259@prog_id=419585.html
Access Database I am trying to use:
http://downloads.techrepublic.com.com/abstract.aspx?docid=173356
Regards;
-Matthew

5 replies

by robo_dev

For starters, you should try to do this on a workstation running IIS or PWS, with the Access DB as a local file on the workstation.

Muuuch simpler.

Typically on a public web server it gets confusing since you have to define relative paths versus just giving it an explicit drive path and file name.

http://www.haneng.com/lessons_17.asp

by scholletech

I do agree with your post 100% and thank you for that link to do it locally. It was very helpful. I understand the challenge I am up against, but you mentioned the paths. Could you help me understand it step by step in this script for the public server? I would like to learn it in shorter time. This is a class project and I would like to be able to understand how to do it. You see I do not have a local machine to publish to the web, and I am using that .com host to supply everything for me. Please reply and Thanks for your posts!

by robo_dev

First you need to define variables:
Dim oConn, oRs

the most critical is the 'connection object' (oConn) since that's the point of the whole script, connecting to something.

You also need to define some variables such as your connection string, the path to the database, all the important details.

For learning purposes, you could really do away with the substitution and do it all explicitly...to me the substitution is the tricky part to understand and get right.

So an example of a random connect string WITHOUT all the messy substitution would look like:

oConn.Open("Provider=MSDASQL.1;Password=masterkey;Persist Security Info=True;User ID=sysdba;Data Source=ibdsn1");

But getting back to our variables for a moment.....

Once you have 'filled in the blanks' and populated all the variable values, it's time to pull the trigger:

To do that you need to performing functions associated with that object, such as OPEN, which opens the connection, EXECUTE, which runs something, and CLOSE which closes the database connection.

oConn.Open connectstr

so here you're saying to perform the OPEN function on the oConn object, with the paramters in the variable we called 'connectstr'

Next, we are dumping a SQL database query into the 'qry' variable:

qry = "SELECT * FROM " & tablename

Then pulling the trigger and hoping it works:
Set oRS = oConn.Execute(qry)

the oRS variable holds the 'result' of the query

All the 'if not end' stuff gets a bit tricky, because here is where you need to stuff your database results into your form, or post it on your web page.

If you're building a list on the web page from a database table, you need to grab the records one at a time to post them to the page.

And last but not least, you need to reset your connection and variable, and VERY IMPORANTLY, CLOSE the database connection, or you will slow down the database with too many open connections.

IS there an easier way to do all this?

Heck yes. There is a tool called ASPMAKER which will generate all this code for you. But that would be cheating.

by tobif

When I read a bit about these things for the web hotel I'm using, I noticed that the provider had put rather strict limitations on what modules and access ways were allowed.

So, when you get the logics to work locally, next step is to learn more about how this particular provider wants you to do it. (and which out of some 2-3 different ways to reach an Access db they support.)

This discussion is closed to new replies.