Added: April/27/2004 at 8:06pm | IP Logged

This is a note to let you know that this article is getting old and though I still agree with most of what I said, this has not been updated since 2/14/1999 and is only here for reference purposes.

Well... here's the deal.

If you are using an ACCESS Database try to avoid using a system DSN. They are much slower because they go through ODBC which then uses the Jet Drivers to access the database. They also have to do a registry lookup.

When you use a DSN-LESS connection with ASP theJet Drivers are accessed directly and the performance increase can be dramatic especially with the newer versions of MDAC installed on the server  which are especially slow with Access System DSN's.

If you hang out in the newsgroups  you will hear this.

Don't use DSN-LESS connections

My theory is all those people have used System DSN's so long that they just tell people to use them and don't really have a good reason or explanation why.

I have some major web applications at work running ACCESS fine with 20 -30 simultaneous users using the same database driven web site. It all depends on how much they are accessing the database, but for the most part there can be a lot of users without problems.  If you have the resources make it SQL in the 1st place, but if you don't then simply experiment with ACCESS. You can always upsize to a SQL database later on  if needed, and with minor changes to your code.

I use SQL databases and ACCESS everyday so I have a good opinion of both. They both have their place. "Simple as that".

Here's a little story....
The other day when we upgraded the servers to MDAC 2.1 and a lot  of ACCESS driven sites started giving Error messages like "Too Many Client Tasks".
After researching this I found something on the Microsoft site claiming SYSTEM DSN's were horrible with ACCESS. " And no.. I can't find the article again or I would have posted a link here"

So I finally went around and changed some of the bigger sites from SYSTEM DSN's to DSN_LESS Connections and "Jeez" ....  instantaneous speed improvements and no more error messages. The newer MDAC drivers made the problem more troublesome , but then again some of those ACCESS database driven sites are running the same queries 10 times faster then they ever did. And because the queries run faster the users get their data faster and there are less concurrent users which helps keep the simultaneous clients down since ACCESS can't handle allot of concurrent users. "Ummm something like that". Also the ODBC ACCESS Driver seemed to be getting overloaded from the overall amount of ACCESS databases running on the same server.

I also tried this on a server where we hadn't upgraded the MDAC drivers yet and queries ran faster there also.

So from now on all my ACCESS projects are DSN-LESS.
Though most of my projects at work won't be ACCESS driven anyway.

BTW... I first looked for answers in the Microsoft Newsgroups and basically just got pounded on for even asking about ACCESS. Very helpful place indeed and a good place to get into an argument.

Take all of this with a grain of salt. If your SYSTEM DSN ACCESS Driven web site is running like crap on the server try changing it to a DSN-LESS connection and see if it runs any better.

Try it out for yourself. If it doesn't help then it didn't really hurt anything to try it.

Also, the more concurrent users the more you will notice a difference though I noticed a big difference in query speed with only myself using certain sites.

I contradict myself all the time BTW.

With Access here is an example of a normal DSN-LESS connection. Supposedly this method access's the Access driver directly, but there is a way to access the database even more directly using OLE as shown in the other example on this page. I will say though that this method and the other method I show both performed much better than the System DSN version. 

Basic DNS-LESS connection example:
DataConn.Open "DBQ=" & Server.Mappath("../_database/database.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

This is the other way to do it that should be even better in theory. In my testing I notice a little performance difference.
   
Accessing the Driver directly using OLE connection example for Access 97:

DataConn.Open "Data Source=" & Server.Mappath("../_database/database.mdb") & ";Provider=Microsoft.Jet.OLEDB.3.51;"

Here is an example with Access 2000

DataConn.Open "Data Source=" & Server.Mappath("../_database/database.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"


Related information
System DSN or DSN-less Connection?
OLEDB For Me
Connections And Server Database Permissions

Added: July/06/2004 at 12:56am | IP Logged

And for help creating those DSN-less connection strings, here is a great resource:

Able Consulting - ADO Connection String Samples
http://www.able-consulting.com/ADO_Conn.htm