Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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

stConnect = “ODBC;DRIVER=SQL Server;SERVER=” & stServer & “;DATABASE=” & stDatabase &  _ “;Trusted_Connection=Yes”

and here’s one using SQL Server security:

stConnect = “ODBC;DRIVER=SQL Server;SERVER=” & stServer & “;DATABASE=” & stDatabase & “;UID=” & stUsername & “;PWD=” & stPassword

Here is an example using SQL Server Native Client 10.0:

stConnect = “ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=” & stServer & “;DATABASE=” & stDatabase & “;UID=” & stUsername & “;PWD=” & stPassword

 

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:

stConnect = “Provider=SQLOLEDB;Data Source=” & stServer & “;Initial Catalog=” & stDatabase & “;Integrated Security=SSPI;”

Or if you’re using native client:

stConnect = “Provider=SQLNCLI10;Data Source=” & stServer & “;Initial Catalog=” & stDatabase & “;Integrated Security=SSPI;”

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”

 

 

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help, SQL Server

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores