Chapter 37: SQL Server as an Access Companion .....................................................................
1201
Caution
Be aware, however, that when you create an .adp file in Access (File ➪ New ➪ Browse, then select ADP from
the Save as Type drop-down list), you’re actually creating an Access 2000–format data file. This isn’t a big issue
for most developers, but you may encounter situations where Access features can’t be supported in an applica-
tion because the file is not an .accdb.
Also, when working in an .adp file, you won’t be able to create local tables or queries. The .adp
file format is specifically designed to work as a front-end to SQL Server data.Connecting to SQL Server
There are several ways for Access to use SQL Server data:l Using the .adp file format discussed earlier in this chapterl (^) Using ADO code to programmatically open a SQL Server database and work with its data
Cross-Reference
For more on ADO, turn to Chapter 25.
l Linking to SQL Server tables and using them as if they were tables linked to another
Access database
The remainder of this chapter covers this third option. As you’ll soon see, working with SQL
Server data through linked tables is no different from using tables linked to any other data source.
One of the most fundamental operations with any large-scale multiuser database engine, such as SQL
Server, is connecting to the database. Connecting directly to SQL Server, using SQL Server front-end
tools is quite easy. All that’s required is a connection to SQL Server, and Access does the rest.
Introducing connection strings
The specification used to communicate with a database is called a connection string. A connection
string is made up of a number of things:
l (^) Host name: The host is the computer where the database server resides.
l Database name: The name of a database on the server. SQL Server supports multiple
databases in a single installation, as well as multiple SQL Server installations on a single
computer. Now and then, a SQL Server installation services a single database for each
database server.
l Authentication: A user name and password are used for security. In some environments,
the user name and password can use an operating system user name and password. In
other cases, a user name and password can be part of the database software itself.