SQL Server Connections Strings for SQL Server

Readers of my blog know how passionate I am connecting Access to SQL Server, it’s one of my favorite ways to deploy my solutions. Today I’m going to talk about the different methods and connection strings you can use to connect between Access and SQL Server.

Two Drivers Available

MS Access Consulting

You have two sets of drivers available when connecting: use the standard driver that ships with every Windows station and SQL Server Native Client. The latter is recommended since I’ve personally seen better speeds connecting with SQL using it. You can download the client here, just navigate to the section called “Microsoft SQL Server 2008 Native Client”. Make sure you pick the version compatible with your operating system.

ODBC Connection

The easiest way to connect with SQL would be creating a DSN on the local machine and use it to link SQL tables in Access. This is also the least recommended, since you have to repeat the process for every computer using your Access with SQL database. You can also distribute a DSN file with your application as well. If you installed SQL Server Native Client then you will see both the old SQL Server driver and the new one as an option when creating the DSN. If you decide to use SQL Server Native client you will also need to install it on the local machine when installing your solution.

DSNless Connections

This is the preferred method in connecting with SQL, there is no need to create a DSN on each machine, thus avoiding all the hassles in visiting each machine when you’re ready to roll out the database. When creating DSNless or connecting with ADODB to SQL, you have four options: Old vs new SQL drivers and Integrated Security or SQL Server logins.

Integrated security means you are using the user’s windows credentials when connecting with SQL and is the preferred method in connecting. It allows you to leverage domain credentials and security to easily manage user rights in your application.

The other connection method is using SQL Server security. It’s the only method available if the SQL Server is on the Internet and your users are connecting from the road. If you go this route you will need to pass along the user name and password in order to validate your connection.

Here is an example of Integrated Security with the Windows standard SQL server driver:

and here’s one using SQL Server security:

Here is an example using SQL Server Native Client 10.0:

Bypass ODBC in your code

You should bypass the ODBC layer altogether when connecting to SQL Sever by using a connection string similar to this one in your code:

Or if you’re using native client:

You can see additional examples here.

Don’t forget the instance name!

When connecting to a SQL Server Express database, you must include the instance name along with the server name. For example, the default instance name when installing Express is “SQLExpress”, if you’re connecting to a server called “DEVServer” than the server portion of your connection string should be: “DEVServerSQLExpress”.


About the Author:

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.


  1. Paul Wolstenholme October 9, 2017 at 8:57 am - Reply

    Your information is great. It took me a long time to find out 2 important points, however:

    1) You can easily create a DNS-less linked table without code (great for getting started):
    In http://www.ascassociates.biz/choosing-between-system-and-file-dsns/
    it tells us: ‘In creating a File DSN, you have to create a .DSN file, however the connection that is created is “DSN-less”.’

    2) The instruction to “Bypass ODBC in your code” might NOT be intended to include the code that links tables. I failed in every attempt to do so. Was the intention that ODBC be bypassed only in VBA code that traverses recordsets or executes a SQL action query?

  2. Asad Ch June 12, 2017 at 11:49 pm - Reply

    Finding the connection string from Visual Studio while connecting your application with database sometimes difficult.Recently I found a simple way to get accurate connection string to connect to the database.
    1.Go to server explorer in Visual Studio.
    2.Select your current database and the open its properties.
    3.Here you can find the connection string. Simply copy it and use it in the code.
    4.That’s it

    Source: – Easiest way to get connection string from visual studio

  3. Rayman June 23, 2015 at 5:04 pm - Reply

    If you have problems with highlighted above instruction I found another one https://www.devart.com/odbc/sqlserver/docs/index.html
    It has new updated ODBC drivers for SQL Server https://www.devart.com/odbc/sqlserver/ which has bigger performance rate than a standard microsoft ODBC driver. I made some tests and it seems so.

  4. Robert A. December 9, 2014 at 12:10 am - Reply

    For the 2014 version of SQL Server Express, I see it only works if you leave off the default instance name, which has changed to MSSQLSERVER. You need to do something like the following:

    “Provider=SQLNCLI11;Data Source=ServerName;Initial Catalog=TestDB;Integrated Security=SSPI;”

  5. LauraNorth November 19, 2014 at 3:43 am - Reply

    The broken link mentioned by Suanne Varming is http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6c3e9ef-ba29-4a43-8d69-a2bed18fe73c for the download of SQL Server Native Client.

  6. Suanne Varming June 12, 2014 at 8:04 pm - Reply


    Faulty link on your page:

    I have a question too:
    I have upsized from access 2010 to sql server.

    The sql statements which previously worked with CurrentDb do not work any more.

    What do I need to do in order to be able to execute sql statements against sql server ?

  7. Jose October 9, 2013 at 12:23 am - Reply


    I am new to application development so please sorry for the ignorance. I built a MS Access front end with a SQL Server Backend. However, I used the DNS approach not knowing that each user machine will need a DNS created. I see the DNSless example above but I am not sure how to implement into my solution. Where do I write the code? Can I write it once then my application will have access to all SQL Server tables? Thanks in advance for you help.

    • Ben Clothier October 9, 2013 at 9:08 pm - Reply


      Generally, you’d use a standard module and add a procedure that you would call at startup to create the connection.

      To see an example of such thing, have a look at this article. I hope that helps.

      • LauraNorth November 19, 2014 at 3:51 am - Reply

        Taking this answer one step further Mr. Clothier, I especially liked your article on increasing security in a DSN-less ODBC. (http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/) How would you suggest maintaining this security when using an ADO connection. The cached connection works well for code written using a DOA connection, linked tables/queries and passthrough queries written in Access, but an ADO connection string still requires the UID and PWD. Thank you!

  8. http://tinyurl.com/gmairoe19421 April 14, 2013 at 3:05 pm - Reply

    I truly have a tendency to go along with every aspect that was
    in fact posted within “SQL Server Connection
    Strings for Microsoft Access | accessexperts.com”. I am grateful for pretty much all the actual information.
    I appreciate it-Mariel

  9. Sonny April 26, 2012 at 7:28 pm - Reply

    Thanks for Great Article Juan
    I now have access2007 connected to an sqlserver.
    I’m completley new to ADO but have a rudimentary knowledge of SQL/VBA/DAO
    It took a bit of fiddling around and trial and error but essentially took me about 3 hours
    to scale up my Access application from an accessFE & BE to an access FE/SQLserver backend
    and be able to connect to it

    the global variables and the OpenMyRecordset and ExecuteMyCommand
    will be a big help to convert my functions


    • Juan Soto April 26, 2012 at 7:35 pm - Reply

      Glad to hear!

      Please consider signing up as a subscriber to get the latest posts via email.


  10. Rx April 4, 2012 at 2:58 am - Reply

    Integrated Security=SSPI;” Just noticed – both of your examples are the same. I need the string for SQL Server Security. My front-end will connect to SQL Server and be distributed via Citrix. It helps with security, lowers bandwidth and we even have Apple users that are happy with MS Access.

    • Juan Soto April 4, 2012 at 3:21 am - Reply

      The first two examples demonstrate using integrated security and SQL Server security. The latter two demonstrate using the native SQL client vs. the standard ODBC driver that comes with Windows.

  11. Rx April 4, 2012 at 2:54 am - Reply

    One of the best DSNLess examples out there. For my Access 2010 to SQL 2008, I noticed that SQL Server Native Client 10 converted all my date /time fields perfectlly when useing it manually as part of the ODBC connection.
    Building code for my tables DNSLess to connect to a SQL Server 2008 R2 on a virutal server. What would the connection string be any different than SQL Express?

    • Juan Soto April 4, 2012 at 3:19 am - Reply

      With SQL Express you need to include the server instance, for example, the default instance is SQLExpress so that the proper way to connect would be the server IP followed by the instance and the port if it’s different than the standard port:

  12. naresh February 1, 2012 at 10:02 am - Reply


  13. Dave January 6, 2012 at 4:05 am - Reply

    Hi Juan,

    I recently came across an odd thing that I can’t explain. If I use DRIVER={SQL Server}; in place or DRIVER=SQL Server; in my connections string my re-linking takes twice as long to complete. Can you explain why this is?


    • Juan Soto January 7, 2012 at 10:43 pm - Reply


      Thanks for sharing! I have no idea why it would be faster but will come back here once I find out and let you know.


  14. Using ADODB recordsets and command objects in Access | Access Help and Tips by AccessExperts.net March 25, 2011 at 10:24 pm - Reply

    […] Fast Connection to SQL: By using ADODB objects and SQLOLEDB connection strings I bypass the ODBC layer altogether and connect straight to SQL, making my app that much more quicker. I’ll be writing an article on SQLOLDB next. […]

Leave A Comment


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.