How Access Projects Link to External Data Sources

ADP files are specifically designed to work with SQL Server and cannot be bound directly to any other data source. They are tightly bound with and optimized for use with SQL Server, so that all data processing is done on the server, which can greatly improve performance. Fortunately, SQL Server has strong capabilities that allow linking to many other data sources.

Access projects rely on the linked server capability of SQL Server. Although architecturally different than linked ODBC tables in an MDB file, similar functionality is obtained. Comparable to how a machine

DSN stores connection information in the registry that can be referenced by using the DSN name, SQL Server can store connection information (linked servers) in a system table in the SQL Server Master database. An alias defined when the linked server is created can be used to reference the server's connection details. The alias can be used from other SQL Server objects such as views, functions, and stored procedures.

Views based on linked servers can be created programmatically or through the user interface in an ADP. Any version of SQL Server supports creating a linked server, so there is no need to worry about having the correct version for a given machine. Linked servers can be created directly through one of the SQL Server client tools, such as SQL Management Studio Express, or through the Access UI.

Creating Linked Server Data Sources Through Access

To create a new linked-server view connection to a SQL Server, open the SampleADP.adp file included with the download files for this chapter, and follow these steps:

1. Go to the Select File O Server O Link Tables to invoke the Link Table Wizard. The wizard opens, as shown in Figure 19-11.

Figure 19-11

2. The Linked Server option should be selected by default if MSAccess.exe is set to Run as Administrator, otherwise you will need to set Access to run as administrator to enable this option. (The Transact SQL option stores the connection information in the query instead of creating a linked server. That is useful when you do not have permissions to create a linked server or when the resulting view is run on an infrequent basis.) To continue, select Linked Server and click Next. The Select Data Source dialog box opens, as shown in Figure 19-12.

3. From this point, the pages of the wizard vary depending on which data source you choose. To create a new link to another SQL Server, select +New SQL Server Connection.odc and click Open. The Data Connection Wizard opens. Choose the SQL Server option and click Next. The wizard requests the name of the SQL Server, as shown in Figure 19-13.

Figure 19-12

Data Connection Wizard Connect to Database Server

Enter the information required to connect to the database server.

1. Server name:

2. Log on credentials

Use Windows Authentication © Use the following User Name and Password

2. Log on credentials

Use Windows Authentication © Use the following User Name and Password

Figure 19-13

4. Enter the name of the SQL Server to be linked, enter the necessary security credentials (in this case, you are using NT Authentication), and click Next to continue. A dialog box opens showing the databases and tables on the SQL Server, as in Figure 19-14.

5. Select the NorthwindCS database from the list of databases on the SQL Server in the Data Connection Wizard dialog box. Click Finish and the new SQL Server is linked. The dialog box closes, returning you to the Linked Table Wizard dialog box.

Select Database and Table

Sdect the Database and Table/Cube which contains the data you want.

Select the database that contains the data you want:

Select Database and Table

Sdect the Database and Table/Cube which contains the data you want.

Select the database that contains the data you want:

1 E

Name

Owner

Description

Modified Created

T>-

[fp Alphabetical List of Products

dbo

11/20/2006

:41:47 AM

vilH

|fp Category Sales for 1997

dbo

11/20/2006

:41:48 AM

VI

dp Current Product List

dbo

11/20/2006

ï: 41:47 AM

VI

Ei Customer and Suppliers by City dbo

11/20/2006

ï: 41:47 AM

VI

[fp Invoices

dbo

11/20/2006

ï: 41:47 AM

VI

OP Order Details Extended

dbo

11/20/2006

ï: 41:47 AM

VI

[fp Order Subtotals

dbo

11/20/2006

S¡41:48 AM

VI T

y 1

*

Figure 19-14

At this point, Access needs to know which view to use, so select the objects to create views against. In this case, select the Table:Orders table from the left list, click the > button to move the table to the right list, and click Finish. A new view data source file is created in your My Data Sources folder in My Documents.

Unfortunately, the wizard does not always function as well as one might like. It's generally good for creating links to other SQL Server data sources, but less reliable with other data sources. Sometimes Access creates the linked server but not any views that use it and sometimes Access won't even be able to create the linked server. Fortunately, it is not too complicated to create the linked servers programmatically, if needed.

Creating Linked Server Data Sources Programmatically

You can create a linked server by writing some ADO code to execute a SQL command to create the link. The CurrentProject object is the instance of the code project that is currently loaded in the Access instance. It exposes the Connection object that can be used to specify the SQL Server to which the ADP is connected. The Connection object's Execute method can be used to execute commands accepted by the SQL Server on the SQL Server. This way, an application can link to a SQL Server by calling Execute with the proper SQL statement. Here's an example of linking to a SQL Server data source:

Dim strCommand As String

' Create the command to link to the SQL Server strCommand = _

"EXEC sp_AddLinkedServer " & _ "@server='RemoteServerAlias', " & _ "@srvproduct='', " & _ "@provider='SQLOLEDB', " & _ "@datasrc='<Machine Name>\<Instance Name>'"

' Execute the SQL command

CurrentProject.Connection.Execute strCommand

RemoteServerAlias is the name to use when referencing the linked server in queries. Then RemoteServerName is the actual name of the remote network server that is being linked to. In the case of a SQL Server Express edition installation (created at the beginning of this chapter), the format is <machine name>\sqlexpress. After creating the linked server, you can execute another SQL statement to create the other database objects by writing a few lines of code, such as the following:

Dim strCommand As String

' Create the SQL Statement to create a view object on the server strCommand = _

"Create View ViewName as " & _

"Select ShipperlD, CompanyName, Phone " & _

"From RemoteServerAlias.NorthwindCS.DBO.Shippers;"

' Execute the SQL command

CurrentProject.Connection.Execute strCommand

This code uses a standard SQL statement to create a new view object. Because all processing occurs on the server, the statement that is passed as the parameter for Execute is passed directly to SQL Server and not executed on the client machine. Therefore, any statement that can be processed on the SQL Server can be issued through the Execute method.

0 0

Post a comment