DSN-less connection

Posted on 2005-04-26
Medium Priority
Last Modified: 2008-01-09
My web hosting company is telling me I should switch to a DSN-less connection for performance issues.  I'd like to try, but I don't know how to create the connection.  Here's what I have right now.  I've created an ODBC connection for SQL Server on my machine.  I created a Microsoft Access 2003 DB which links to the SQL DB using the ODBC connection.  What is the code that I would use to create a DSN-less connection, and where do I insert this within my Access DB??
Question by:crowegreg
LVL 15

Expert Comment

by:Juan Ocasio
ID: 13871853
Try checking out my post here:

Try  this:

Set oConn = Server.CreateObject("ADODB.Connection")
oPath="DRIVER={SQLServer};" & _
"SERVER=TheServer;UID=TheUser;" & _
oConn.open oPath



Author Comment

ID: 13871869
Thanks.  Working within Access, where do I place your code?
LVL 15

Expert Comment

by:Juan Ocasio
ID: 13871876
You shouldn't need the Access DB.  The above should tale to SQL Server directly.  Oh, BTW I forgot the link to one of my posts:


This is for VB, but I'm sure ASP is similar syntax.  The code above would be put in an asp page

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 25

Expert Comment

ID: 13871879
You can do this by creating and running a VB program.  This link has a lot of good sample code, but I've copied the basics below:


-- Create this subprocedure in Access 97. Substitute your server name and password if necessary, and run it:

Public Sub LinkODBCTable()

Dim db As DATABASE, tdf As TableDef
Dim strConnect As String

Set db = CurrentDb()
Set tdf = db.CreateTableDef(Name:="dbo_tblODBCLink__21_chars", _
Attributes:=dbAttachSavePWD, _

SourceTableName:="dbo.tblODBCLink__21_chars", _
db.TableDefs.Append tdf

End Sub

Author Comment

ID: 13872022
To make sure we're on the same page.  I've created a Microsoft Access 2003 DB.  Within this DB, it is using an ODBC link to a SQL database located on a web server.  Within my Access 2003 DB are 82 linked tables from the SQL DB on my web server.  My web hosting company has said my performance would increase if I switched to a DSN-less connection.  Using the ODBC connection, and the get external data from within Access, it's very easy to create the link for the tables.  Now, trying to understand the code listed above is a little over my head.  Could you include more detail to help me understand.  Thanks!!

Expert Comment

ID: 13874075

When you connect the tables in Access, when selecting a DSN, you
can choose between a File Data Source or a Machine Data Source.

If you select a Machine Data Source, the information stored in the
connection points to that DSN. If the DSN is removed from the machine
the connection is lost.

If you select a File Data Source the information from that DSN is copied
into the tabledef. When you remove the DSN, the connection still works.

Maybe this is what your provider means.

On the other hand, maybe he means you should talk directly to the SQL server
from your web application (without Access in between). In that case you
should create an ADODB connection, as in jocasio's code.

LVL 15

Accepted Solution

Juan Ocasio earned 750 total points
ID: 13875914
If you use Access as the ODBC Client (which you are), you in effect have to have the DNS set up because This is how it knows to talk to the server.  Even when you select New from ODBC Databases, you are in effect creating a new User DSN.  I beleive they want you to do this without using Access as the frontend.  When you are designing the DB access from the web, the webpage is in effect your frontend to the DB.  You can either store the queries in the asp pages or execute stored procedures.  I think you may be incurring a bit more overhead by first going to access to get to SQL Server.  Look at my code in the link I gave you above.  It is option 3 where it shows you the code to put in for a dns-less connection.

Also the little snipit above in my first post shows how to connect to a SQL Server, just substitute your info TheUser, TheDatabase, TheServer, and ThePassword...

Author Comment

ID: 13879645

I've been having performance issues, and my web hosting company offered this as a suggestion.  All of the research I've found says it can be done, but the only instructions show using createtabledef, put with 82 tables, it's not worth it.

We are an e-retail company, and our shopping cart application uses MSSQL as the DB source.  Because of the limitations of the shopping cart application for management within our business operation, we've created a client side application to assist in our business operation.  With our skillsets, we created this in Access 2003.  Over time we've created a great application using the SQL DB on the web site as the datasource.  We're always trying to improve the performance of our application.  So this was one area that I've been doing research on.  But it looks like this isn't going to work.  

Thanks for everyone's input!!

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question