Linking to External Data
Access 2007 supports connecting to a wide variety of different types of data sources; although some are read-only, many are fully updatable from an Access application. This is because different data source types use separate, but distinct methods to connect to the data. Indexed Sequential Access Method (ISAM) drivers are generally used for connecting to other desktop or file-based data sources, such as Excel, text, and HTML. On the other hand, Open Database Connectivity (ODBC) data source vendors typically supply connection utilities for use with their specific database products. In most cases, the client machine needs to have a specific database product's relevant utilities installed before an application can connect to the data source. For example, even though Access ships with an ODBC driver for Oracle, the Oracle client utilities still need to be installed on the machine for the ODBC driver to be of any use to an Access application.
Fortunately, Access makes linking many different data sources extremely easy. The following steps outline a common method for creating a connection to a SQL Server Express database:
1. To create a link to an external data source from an Access ACCDB file, select External Data tab on the Ribbon. In the Import section, click the More button on the Ribbon and choose the ODBC database option. This opens the Get External Data Wizard, as shown in Figure 19-1.
- Figure 19-1
2. In the wizard, choose Link To The Data Source By Creating A Linked Table (the second radio button in the list) and then click OK. This invokes a Select Data Source dialog box that looks similar to the one shown in Figure 19-2. The Select Data Source dialog box enables you to create links from the current database to other supported ODBC databases.
3. You can select a pre-existing data source name (DSN) connection or create a new data source. For now, open the Machine Data Source tab and click the New button. The Create New Data Source dialog box opens (see Figure 19-3).
On Windows Vista, you may get a warning that you are not able to create system DSN connections because of how Access is run and/or the permission level of the Windows account. The warning should provide instructions so that you can create a user DSN.
Create New Data Source
Select a type of data source:
:User Data Source (Applies to this machine only)!
f* System Data Souice (Applies to this machine only)
Selecting User Data Source creates a data source which is specific to this machine, and visible only to you.
Figure 19-3
The path contained here determines the location in the Registry where the connection information will be stored. User-specific data source locations are stored to the following registry key and are available only to the current user:
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
Machine data sources are available for all user profiles and are stored to the following registry location:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
Odd but true: HKCU has a proper case Software node, while HKLM has an uppercase SOFTWARE node. The registry is case insensitive, so it would be okay to make these consistent, although you will see the different cases when you open the registry editor UI.
4. To create a system DSN connection on Windows Vista, you must be logged into an Administrator account or Access will need to be run as an administrator. For now, choose User Data Source, and click the Next button. The next Create New Data Source page opens, as shown in Figure 19-4.
5. At this point, the screens and options presented vary depending on what drivers are present on the machine and which drivers you choose. For now, select SQL Native Client option from the list, click Next, and then click Finish to bring up the dialog box information specific to SQL Server, as shown in Figure 19-5.
- Figure 19-4
Figure 19-5
Figure 19-5
6. Enter TestDSN for a name and, optionally, the description for the connection. You can type in the name of your SQL Server or you can select it from the list box. The name of the SQL Server is typically machine name\database server (for example, MyMachineName\SQLServerExpress). Also, a default SQL Server can be configured for the machine, and in that case, you can enter (local) to reference the default instance of the SQL Server on the machine. Click Next. The page shown in Figure 19-6 opens.
7. Enter the necessary security credentials to log into the server. The credentials will depend on how the particular SQL Server is configured. If the steps shown earlier were used to install the SQL Server, choose the option to use NT Authentication for the Windows User Account. If your server administrator has specified users using SQL Server authentication, select the proper option and type in the username and password. Click Next to continue.
8. The last setting to change is the default database (see Figure 19-7).
Create a New Data Source to SQL Server
How should SQL Server verify the authenticity of the login ID?
With Windows NT authentication using the network login ID.
f- With SQL Server authentication using a login ID and password entered by the user.
To change the network library used to communicate with SQL Server, click Client Configuration.
Cient Configuration...
Connect to SQL Seiverto obtain default settings for the additional configuration options.
Figure 19-6
- Figure 19-7
The default database can be selected on a per-connection basis and different connections can reference different databases on the same server. The default database selection determines the database context for which commands are issued against the server. For example, if code is called to select records from a table in the NorthwindCS database, but the default database is the master database used by SQL Server, the query results in an error because the table doesn't exist in the master database. Always specify a default database other than the master database to help prevent any accidental, unwanted modifications to the database.
9. For this example, select the NorthwindCS database (which you created earlier) from the default database list and then click Next. The remaining default option settings should be fine, so click Finish to complete the wizard. All of the options selected for the new connection are displayed, as shown in Figure 19-8.
ODBC Microsoft SQL Server Setup
A new ODBC data source will be created with the following Microsoft SQL Sen/er ODBC Driver Version 06.00.5920 Data Source Description: Test Data Source for Access
Prepared Statements Option: Drop temporary procedures on
Figure 19-8
10. Click the Test Data Source button to ensure that the connection is working correctly. If the connection is working, the dialog box displays the message TESTS COMPLETED SUCCESSFULLY. Click OK to close the dialog box.
11. You are returned to the Select Data Source dialog box, which now shows a new data source called TestDSN, the connection you just created. If new links are needed for this connection in the future, the existing DSN can be used instead of creating another one from scratch. Select the TestDSN option from the Select Data Source dialog box, as shown in Figure 19-9.
Select Data Sonne J
Be Data Source Machine Data Source |
Select Data Sonne J
Be Data Source Machine Data Source |
|
Data Source Name |
Type |
1 Description |
|
dBASE Files |
User | |
|
Excel Files |
User | |
|
MS Access Database |
User | |
|
User |
Test Data Source for Access |
A Machine Data Source is specfic to this machine. and cannot be shared. "User" data sources are specific to a user on this machine. "System data sources can be used by all users on this machine, or by a system-wde service
Figure 19-9
12. Click OK. The Link Tables dialog box opens showing all of the available objects in the database for the DSN connection that was just created. The names and types of objects depend on the data source, but in this case, assuming you are using the NorthwindCS database provided in the sample files, the dialog box should look similar to Figure 19-10.
- Figure 19-10
13. Select the dbo.Orders table object and click OK. A link to the dbo.Orders table is created and is shown in the Navigation pane. This new link is created directly to the NorthwindCS database on SQL Server and any changes made to the data are reflected in updates to the data on the server. At this point, the new linked table is available to view, bind to a form or report, reference in code, or use in any other way a normal table can be used, except for modifying the schema of the table.
Because the table is linked and the actual structure and data reside on the SQL Server, changes to the schema, or design, of the table must be completed from the SQL Server database. This does not change the data in any way, only the schema. However, if the user credentials supplied to the DSN connection do not have permission to modify the data in the table, no user can make changes to the data. When the Access application connects to the SQL Server, it only has as much permission as the user's account has to the SQL Server.
Average user rating: 3 stars out of 2 votes
Post a comment