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

Web Development

Collapse -

To do this on a public web server can be tricky

by robo_dev · 11 years ago In reply to How do you script a ASP p ...

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

Collapse -

Can you also help me understand the ASP script

by scholletech · 11 years ago In reply to To do this on a public we ...

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!

Collapse -

Not sure how to really explain it, but here goes

by robo_dev · 11 years ago In reply to Can you also help me unde ...

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.

Collapse -

Have no idea about your web hosting service, but

by TobiF · 11 years ago In reply to To do this on a public we ...

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.)

Web Development

Question