Part VI: Access as an Enterprise Platform
1204
Let’s focus on the ODBC driver allowing Access to communicate with a SQL Server. How do you
deal with an ODBC driver? You have to create an ODBC data source, often called a Data Source
Name (DSN), then reference the DSN from within Access. Once configured, a DSN remains on the
local computer and is available to any ODBC-compliant application, such as Microsoft Access. The
DSN contains enough information about the ODBC data source that the Access database engine
(Jet or ACE) is able to use the ODBC driver to communicate with SQL Server.Create a data source as follows:- Go to Windows Start menu, and choose Settings ➪ Control Panel.
- In the Control Panel, double-click the Administrative Tools option, and select Data
Sources (ODBC).
The three ODBC configuration options are
l User DSN: A User DSN applies to a specific user on the client computer on which the
User DSN is created.
l System DSN: A System DSN is similar to a User DSN, except it applies over a net-
work (to a certain extent).
l File DSN: A File DSN creates a connection configuration (a connection string), for a
database, into a file on your client computer.Tip
Of these three options, the File DSN is the best choice in most situations. Because the connection information
is stored in a file (the default location for DSN files is C:\Program Files\Common Files\ODBC\Data
Sources), you can easily share a DSN configuration with other users.
Sharing a File DSN is easy: Simply locate the DSN on your machine and attach it to an e-mail, or
move it to a common location on the network. User DSNs and System DSNs are actually stored in
the computer’s Registry, and must be manually set up on each computer needing access to an
ODBC data source.Essentially, what you’re doing in this situation is creating a link from Access to data that is stored
in SQL Server. Therefore, tables are maintained in SQL Server, and the front-end application (que-
ries, forms, reports, VBA code, and so on) is maintained in Access. In Chapter 17, you examined
importing tables into Access, making copies of data from a SQL Server database, and creating com-
plete copies of data in Access. In this case, you want to simply link between Access and SQL Server
because data are maintained in SQL Server, and not copied to Access.A DSN specifies a data source (like SQL Server) and a database managed by the data source.
Referencing a DSN from an application (like Access) provides the application with a direct path to
the database. All the tables in the database are accessible through the DSN. The logic required to
actually retrieve the data from the database is provided by the ODBC driver. Access knows nothing
about SQL Server, but it knows a lot about working with an ODBC driver.