Connecting to SQL Server

The first migration steps are to install SQL Server and establish a connection from Microsoft Access. Consult SQL Server Books Online for information about installing the server. You must create a data source on the client computer, the computer running Microsoft Access, to connect to the server. If Microsoft Access and SQL Server are on the same computer, the data source is still required.

What Is a Data Source?

A data source is a method of standardizing database connectivity. Data sources are created with the Open Database Connectivity (ODBC) dialog box accessible from Windows Control Panel. Windows NT and Windows 95 operating systems have a 32-bit version of the ODBC application, while Windows 3.1 and Windows for Workgroups have a 16-bit version. Microsoft Access 95 and 97 can create a new data source from within the application.

A data source (abbreviated DSN for data source name) can be created as a system data source or a user data source. Should you create a user DSN or a system DSN? A system DSN tends to be more convenient but less secure than a user DSN. Select a user DSN if you have multiple users of the same client computer and you want to hide the existence of the data source from some of the users.

Creating a System Data Source

To create a system data source

  1. On the Start menu, click Settings, then click Control Panel. (In earlier versions of Windows, select Control Panel from the Main program group.)

  2. Double-click ODBC to open the driver manager's user interface, the ODBC Data Source Administrator. What you see varies with the version of the Odbc32.dll (or Odbc.dll) that you are using. ODBC 3.0 presents the ODBC Data Source Administrator dialog box.

    ODBC 2.5 presents a dialog box with much of the same information, but without tabs and without the option of creating a System DSN. If you are presented with that dialog box you may want to switch to the ODBC files on the SQL Server CD (Odbc32.dll and Sqlsrv32.dll for 32-bit operating systems or Odbc.dll, Sqlsrvr.dll for 16-bit operating systems.)

  3. Click the System DSN tab.

  4. Click Add.

  5. From the installed ODBC drivers list, select SQL Server and click OK to bring up the ODBC SQL Server Setup dialog box.

  6. Give your data source a name, such as Payroll. This can be either the name of the server to which you are connecting, or the name of the database to which you are connecting or some other name that is meaningful to you, such as the purpose of the connection. You are likely to accumulate several data sources, so the name should be descriptive.

  7. The Description field is optional.

  8. Enter the name of the SQL Server in the Server box. The SQL Server should have the same name as the Windows NT-based computer on which it is running.

    If Windows NT was reinstalled with a new computer name after SQL Server was installed, they can be different and then you may experience connection problems. See sp_rename in SQL Server Books Online if you suspect this.

  9. Click the Options button and provide the name of the database on SQL Server to which you want to connect. Remember that several databases with different names can exist on one SQL Server.

  10. At the bottom of this dialog box is a check box called Convert OEM to ANSI Characters. IMPORTANT: THIS BOX SHOULD NOT BE CHECKED.

  11. Click OK to return to the ODBC Data Source Administrator dialog box, and click OK again.

DSN-less Connections

Users can easily make their connections with data sources. Moderately skilled users can create and maintain their own DSNs. However, there is also room for user error. Many programmers decide to make programmatic DSN-less connections. This can be more reliable because it does not require maintenance on the client computer, but it cannot be updated without rolling out a new version of the client program.

To make a connection without a DSN

  1. Create a new SQL Pass-through query in Microsoft Access.

  2. On the View menu, click Properties.

  3. In the ODBC Connect Str. field of the Query Properties box, enter a connection string like the one below. The italic text must be replaced with your information. Enter the string on one line:
    ODBC;driver={SQL Server};server=YourServerName;database=
YourDatabaseName;uid=UserName;pwd=Password;dsn=;

    This example uses a server named Acctg1, a database named Purchases, and the sa account with no password:

    ODBC;driver={SQL Server};server=Acctg1;database=
Purchases;uid=sa;pwd=;dsn=;

For more information about making DSN-Less connections with VBA code, see the Visual Basic Knowledge Base Article Q147875, "How To Use 'DSN-Less' ODBC Connections with RDO and DAO."