DSN-less connection

Posted on 2005-04-26
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 14

    Expert Comment

    by:Juan Ocasio
    Try checking out my post here:

    Try  this:

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



    Author Comment

    Thanks.  Working within Access, where do I place your code?
    LVL 14

    Expert Comment

    by:Juan Ocasio
    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

    LVL 25

    Expert Comment

    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

    strConnect = "ODBC;SERVER=MySQLServer;UID=sa;DRIVER=SQL
    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

    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!!
    LVL 6

    Expert Comment


    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 14

    Accepted Solution

    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


    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!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    This article describes some very basic things about SQL Server filegroups.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now