Ways to work with SQL Server data in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 2002

One of the many strengths of Access is that it works well with a wide variety of databases and file formats, including Microsoft SQL Server™ data.

 Tip    Try Office 2010 In Access 2010, you can publish a database to a SharePoint server that is running Access Services, and then use the database in a browser. Read an article or try Office 2010!

There are five main ways to work with SQL Server data in Access.

ShowCreate an Access project

An Access project (.adp) is an Access data file that provides efficient, native-mode access to a SQL Server database through the OLE DB component architecture. Using an Access project, you can create a client/server application as easily as a file server application. This client/server application can be a traditional solution based on forms and reports, or a Web-based solution based on data access pages, or a combination of both. You can connect the Access project to a remote SQL Server database, a local SQL Server database, or a local installation of SQL Server 2000 Desktop engine.

An Access project interacts with a SQL Server database

1 Connects the Access project to the SQL Server database

2 Database objects stored in the SQL Server database

3 Database objects stored in the Access project

Working with an Access project is very similar to working with an Access database. The process of creating forms, reports, data access pages, macros, and modules is virtually the same as that used to create an Access database.

Once you connect to an SQL Server database, you can view, create, modify, and delete tables, views, stored procedures, user-defined functions, and database diagrams by using the Database Designer, Table Designer, Query Designer, Query Builder, and SQL Text Editor. Although the user interface for working with these database objects is different from the equivalent database objects in an Access database, it is just as user-friendly. An Access project also contains many of the same wizards as an Access database, such as the Form Wizard, the Report Wizard, the Page Wizard, and the Input Mask Wizard. These Wizards help you to quickly create a prototype or simple application, and also make it easier to create an advanced application.

ShowCreate a data access page

A data access page is a special type of Web page designed for viewing and working with data from the Internet or an intranet data that is stored in an Access database or a SQL Server database.

Using a data access page is similar to using a form: You can view, enter, edit, and delete data in a database. However, you can also use a page outside an Access database, so users can update or view data over the Internet or an intranet.

Data access page in Page view

1 Enter product information in the text boxes, drop-down list box, and check box.

2 Use the record navigation toolbar to navigate, add, delete, save, sort, and filter records, and to get Help.

A data access page gets its data from an Access database, or a SQL Server database version 6.5 or later. To design a page using data from one of these databases, the page must be connected to the database. If you have already opened an Access database or an Access project that's connected to a SQL Server database, the data access page you create automatically connects to the current database and stores that path in the ConnectionString property of the data access page. When a user browses to the page in Microsoft Internet Explorer 5 or later or displays the page in Page view, current data from the underlying database is displayed by using the path defined in the ConnectionString property.

ShowCreate an Office Web Component

Microsoft Office Web Components is a set of ActiveX controls you can download that work with Microsoft Office Professional Edition 2003 or Microsoft Office XP. With Office Web Components installed on your computer, you can interactively use a spreadsheet, a PivotTable list, and a chart on a Web page in Microsoft Internet Explorer (version 5.01 or later for Office 2003, version 4.01 or later for Office XP). If you want to use the components on a data access page in Access, you should have Internet Explorer 5.01 or later.

The following illustration shows a simple PivotTable list that displays summarized data.

Example of a PivotTable list

Although the data access page gets its data from an Access database or SQL Server database, Office Web Component controls on the page can display data from either of these databases or from other sources. For example, the page can contain a PivotTable list, spreadsheet, or chart with data from a live SQL Server database. The SQL Server database can also be an Online Analytical Processing (OLAP) database, which is an approach to database server processing that organizes large amounts of data in ways that make it accessible and understandable to people who want to analyze business trends and results.

For more information on changing external connection information, see Access Help.

ShowLink SQL Server tables from an Access database (.mdb)

Linking is a way to connect, using an ODBC connection, to data from another application without importing it so that you can view and edit the data in both the original application and in an Access database.

When you open a linked SQL Server table in Design view, it looks much like a regular Access table. Although you can't change the way the linked table and its fields are defined in the external SQL Server database, you can set the properties that control the way the fields appear in Access. Changes that you make to properties for linked tables affect only how Access handles and displays data from the linked table; no changes are made to the source SQL Server table. Field properties that you can set for linked tables are listed in the following table.

Property Effect
Format Controls how data is displayed in a field
DecimalPlaces Controls the number of decimal places displayed
InputMask Creates a data input mask with separator characters and blanks to fill in
Caption Changes the name used for a field's datasheet column heading, and specifies a default name to be used as a label when adding a field to a form

Access also requires a unique index to update, delete, or insert data in a linked SQL table or view. When you link a SQL Server updateable view, or an SQL database table that doesn't have a unique index, Access asks you to select one or more fields to generate a unique index. However, Access doesn't test to make sure that the fields you selected uniquely identify each record. If there are duplicates in the index Access builds, you won't be able to update those records.

For more information on linking data and using the MSysConf table, see Access Help.

ShowImport SQL Server data into an Access database (.mdb)

You can import SQL Server data into a new Access table. In general, importing is a way to convert data from a different format and copy it into Access. The source table or file is not altered in this process. You can import directly from a SQL Server database using an ODBC connection, a text file exported from SQL Server, or an XML file exported from SQL Server.

To make frequent import operations more convenient, you can automate them by creating a macro or creating a Microsoft Visual Basic® for Applications (VBA) procedure. This is useful, for example, when you import data on a regular schedule or you have unusual or complex requirements for importing data.

For more information on linking data and automating your links, see Access Help.

Applies to:
Access 2003