How can I connect db to access using asp?

use ADO or DAO

A more specific question will get a better answer.

You can see here that someone did tests on different connection styles, but usually it boils down to using the method that the programmer is most familiar with...

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
response.write RS("name")
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
dbaseConn.Close
Set dbaseConn = Nothing

That example is a template for the way I usually do it... Borrowed from: http://faq.1and1.com/scripting_langu...atabase/2.html

response.write RS("name")

You don't need the time stop at the end of that line.

It's also not a good idea to do a Select * query...always select the fields you want specifically, as it will take strain off of your database.

Originally Posted by ADAM Web Design

response.write RS("name")

You don't need the time stop at the end of that line.

It's also not a good idea to do a Select * query...always select the fields you want specifically, as it will take strain off of your database.

Thanks for the catch, I did a quick copy and paste job from the link I posted, then shorted a few extra lines, didn't notice the added %> ... Thanks, edited

No probs, dude. You'll find that, if you make an ASP-related error in here, Hirst or I will probably catch it.

Originally Posted by ADAM Web Design

It's also not a good idea to do a Select * query...always select the fields you want specifically, as it will take strain off of your database.

Is this true for Access? How would you really know? The file lives somewhere, as a file, and the application -- IIS here -- opens and works with it. It stands to reason that it's more work to grab all the fields, but I don't think that's actually the case. Since the "client" does all the work, parses the entire row to get the columns in your select list, the only savings would be sending the data from the db to the client, but with Access the client does all the work.

Yes, it's true for Access as well, and the reason is what you just said...something has to figure out what fields are meant to be used. Most queries also don't require all of the fields as well, which to me is enough justification by itself.

Field specification leads to fewer issues, in other words.

Originally Posted by ADAM Web Design

Field specification leads to fewer issues, in other words.

Absolutely. With ADO.NET, the fastest way to get at the data is to parse the tabular data stream directly, and access each field by it's ordinal ... go after the 4th field in the record and take the result as a string. You can refer to them by field-name instead, but this requires a lookup inside ADO to map the name of the field to its position in the TDS. If you refer to fields by the position / index number instead of their name for speed, and use *, if someone else adds a field to the middle of a table, or even to the end and your query uses a join, it's going to break your code. And any number of other reasons to only ask for what you need.

But I wonder if it's really any faster to select half the fields in an Access table instead of all of them ... and kind of doubt it. I'd still ask for the specific fields I intend to use, as a best practice, but I personally wouldn't expect any speed benefit from it.

With MySQL, Oracle, SQL Server, sending less data over the network is going to make your advice faster, on top of the other benefits. And you can measure that pretty easily with the query plans, profilers, and so on. But Access is more of a black box, a lot harder to figure out exactly what's going on inside.

In Access it is slower to use SELECT * .

The reason being that the client has to parse the columns & tables twice. Once to get the column names from the tables so they can be applied to any criteria in the query, for the conversion of the query into the p-code used for accessing the data store.

What Hirst said...I was going for that explanation in an awkward sort of way.

This is pretty interesting. Mind if I ask how you learned this? Microsoft white papers? I haven't used Access in a long time, and I'm working on a project that's going to allow that or SQL Server for a back-end.

I've always found Access does pretty well with just the basic optimizations, multiplexing connections and stuff like that, but without getting too aggressive, with stuff like clustered vs regular indexes, query hints, and so on. I think that's why Access doesn't support triggers and complex procs; the performance is good enough they needed more to distinguish the apps and make SQL more expensive. Anyway, optimizing for Access has been on my mind lately.