MS-Access / Getting Started

---

Creating a Data Source to Link to an ODBC Database

Before you can connect to a database that requires ODBC, you must first create a data source-either a data source name (DSN) file or a data source entry in your Windows registry. A data source is simply a named set of ODBC driver parameters that provide the information the driver needs to dynamically link to the data. To create a new data source name file or registry entry, on the External Data tab in the Import & Link group, begin importing or linking a file by clicking the ODBC Database button. Access 2010 opens the Get External Data - ODBC Database dialog box. Select either the Import The Source Data Into A New Table In The Current Database option or the Link To The Data Source By Creating A Linked Table option, and then click OK. Access opens the Select Data Source dialog box where you can begin creating a new data source.

To start creating a new data source, you can click the New button on either the File Data Source or Machine Data Source tab on the Select Data Source dialog box. If you create a new file data source, Access 2010 stores a file with a .dsn file name extension in your default folder for data source name files. The resulting text file will contain a list of keyword assignment statements to set the values needed by the driver. (You can find an example of a data source name file at the end of this section.) If you create a new machine data source, Access stores the parameters in the Windows registry.

To create a new machine data source, click the Machine Data Source tab and click the New button. Access displays the Create New Data Source wizard.

Note: If you receive an error message indicating that you are not logged on with sufficient administrative privileges when you click the New button on the Machine Data Source tab, you'll need to log onto your computer with an account that has administrative privileges to create a new machine data source.

To create a data source that applies to all users on your computer, select System Data Source (Applies To This Machine Only) and click Next. Access displays a list of the available ODBC drivers on your system. To create a data source for SQL Server, select SQL Server at the bottom of the list and click Next. Access confirms that you are ready to create a system data source for the driver you specified. Click Finish, and Access displays the Create A New Data Source To SQL Server wizard.

Enter a name and description for your data source. To connect to the server on your computer, enter your computer name in the Server box. If you are authorized to connect to other servers on your network, click the arrow in the Server box. When you do this, Access 2010 searches your network for other computers running SQL Server and places the names of all servers found in the list. Click Next to go to the next page.

Depending on how SQL Server 2008 is configured, you might need to enter a login ID and password. By default, SQL Server 2008 uses your Windows logon information (your user name and password) to authenticate you. This means that you don't have to enter your user name and password a second time when you access the computer running SQL Server. If, however, the server is configured to use SQL authentication, you must select With SQL Server Authentication Using A Login ID And Password Entered By The User, and enter your login ID and password. Click Next to see the page where you can specify the default database for this data source.

If you are authorized to connect to more than one database on the server and you want to connect to a database other than your default database, select the Change The Default Database To check box. Access logs on to the server and returns a list of available database names. If you don't specify a database name and if multiple databases exist on the server, you'll be connected to the default database for your login ID. (You don't need to worry about the other options displayed on this screen.) Select the database you want, and click Next.

The last page shows various options, including the ability to change the language of error messages or log data. You can leave these settings as they are and click Finish. Access displays a final confirmation dialog box with a list of the settings you chose. If you need to change anything, click Cancel and then use the Back button in the Create A New Data Source To SQL Server wizard to correct your selections. You can click the Test Data Source button to verify that Access can make a valid connection using your settings. If the test runs successfully, click OK to create your new data source.

If you're familiar with the parameters required by the driver, you can create your own data source name file. A data source name file like the one listed here for SQL Server begins with the [ODBC] section delimiter and then includes keyword assignment statements for each piece of information the ODBC service needs to correctly load the driver you want. (You can find this file, named SQLServerLocal.dsn, on the companion CD.) Note that you must supply your Windows user ID (your user name) and computer name for YOURID and YOURCOMPUTER, respectively, to connect to the server running on your computer. You can edit any data source name file using a text editor such as Notepad.

[ODBC]
DRIVER=SQL Server
UID=YOURID
DATABASE=AdventureWorks
WSID=YOURCOMPUTER
APP=Microsoft® Windows® Operating System
Trusted_Connection=Yes
SERVER=YOURCOMPUTER
Description=Sample DSN for SQL Server

The first time you create a data source name file, you'll probably want to use the Create New Data Source wizard, but after you understand the structure of a valid data source name file for a particular data source, it's easy to modify an existing file or create a new one.