Use a web datasheet view in an access app

Important    Microsoft no longer recommends creating and using Access web apps in SharePoint. As an alternative, consider using Microsoft PowerApps to build no-code business solutions for the web and mobile devices.

To see many records of data in your Access app at the same time, use a Datasheet view. When you add a new table to your Access app or import data into your app, Access automatically creates a Datasheet view for each table but you can create additional Datasheet views for your tables. The Datasheet view has built-in filtering and sorting options available when you want to search or filter for specific data. The Datasheet view is also useful for quickly viewing the details of many records, adding new records, and deleting records from a table. You can also download records from a Datasheet view in an Access app into Excel. For more about adding tables to Access apps, see create an Access app.

Note: This article doesn't apply to Access desktop databases. For more information about creating forms in desktop databases, see create an Access form.

When Access creates a new Datasheet view, it automatically creates a control on the view for each field in the table. When you add a new field to the table, Access adds a new control for that field onto the Datasheet view as well. However, if you make any design changes to a Datasheet view, such as moving or resizing controls, and save your design changes, Access no longer adds new controls to the Datasheet view when you add new fields to the table. In this case, you'll need to add new controls to the Datasheet view when you add new fields to the table.

What's a web datasheet view?

A web datasheet view in an Access app displays online data arranged in rows and columns in a web browser. It can allow data edits or not, as you prefer.

When you create a web datasheet view in Access, it opens in Design View, where you can do any of the following:

• Set the record source

• Provide a caption for the view

• Add automatic actions that happen when the view opens and when someone moves between records using the view

• Add custom actions that happen when someone clicks a button (you pick the button)

• Resize and move controls and set control properties

Adding a new Datasheet view

To add a new Datasheet view to a table in an Access app, complete the following steps:

1. Open the app in Access.

2. Click the table caption name in the Table Selector in the left pane and then click the Add New View button (the plus sign button next to the view caption names).

   

3. In the Add New View dialog, enter a name for the view in the View Name box, set the View Type to Datasheet, and select the table name in the Record Source box.

Set or change the record source for the view

On the top right of the design area, click Data to open the Data box:

Choose the table name to provide data for the view, and optionally check the Read Only check box to prevent people from changing the data in this view. When you're done making changes, just close the dialog.

Tip: If you're having trouble finding the Data options for the Datasheet view, click an empty spot on the design area away from any controls.

You can also select the name of a saved query in the Record Source box if you want to display the results of a query in your Datasheet view.

Provide a caption

On the top right of the design area, click Formatting to open the Formatting box:

Choose whether you want the Action Bar to be visible or hidden in the ActionBar Visible box. You can still add, edit, and delete records in Datasheet views if the Action Bar is hidden as long as the data in the Datasheet view is updateable.

In the Caption box, enter the text to display on the Datasheet view when it is opened as a popup in a browser window. When you're finished, just close the dialog.

When you set the Caption property on the Formatting dialog, Access displays that caption only if the Datasheet view is opened as a popup. Setting the Caption property on the Formatting dialog doesn't change what Access displays on the View Selector for the view. To change the text shown on the View Selector, see the section on Rename a Datasheet view caption.

Add automatic actions

On the top right of the design area, click Actions to open the Actions box:

Each button opens a macro in Design View. Click On Load to design a macro that runs when the datasheet opens, and click On Current for a macro that runs whenever someone selects a different row in the datasheet. When you're done, just close the dialog. Learn more about customizing a view by creating a user interface macro.

Add custom actions to the Action Bar

By default, Datasheet views include an Add action button and a Delete action button. Learn more about working with action buttons in an Access app. You can also add your own custom action buttons to the Action Bar.

At the top center of the design area, click Add custom action (the plus sign button next to the Action Bar). The new action shows up as a button with a star icon:

Click the custom action button, and then click the Data button that appears. Access opens the Data dialog for the custom action:

Give your custom action button a name, pick an icon to use, enter a tooltip (text that appears when the pointer hovers over the action icon), and then click On Click to open macro Design View and write a macro that runs when the custom action button is clicked. Learn more about adding custom actions to the Action Bar.

Adding, editing, and deleting data

You can make changes to your data using Datasheet views by opening the view in your browser. Access automatically saves changes to records whenever you move to a different record in the view.

• To add a new record: Click the Add record button on the Action Bar, type in the data you want in the columns, and then tab or click to a different record to save the new record. New records in Datasheet views always appear at the bottom.

• To edit a record: Click into the row you want to edit and tab to the specific column you want to change. Type in your new data and then tab or click to a different record to save your record changes.

• To delete a record: Click into the row you want to delete and then click the Delete action button. Access displays a confirmation dialog to make sure you want to delete that record. Click Yes to delete or click No to cancel deleting the record.

  You can also delete a record by right-clicking the row selector on the left side and then clicking Delete. To delete more than one record at a time, hold down the CTRL key while clicking different row selectors, right-click one of the row selectors, and then click Delete.

Filtering and sorting data

Datasheet views in Access apps have built-in options for filtering and sorting the data. You can quickly filter and sort different columns of data to view just the records you want to see.

To filter data:

1. Navigate to your Datasheet view in the Access app using your browser.

2. Hover over a column header, click the drop-down arrow, and click an option to filter the list for that specific data in the column. Access displays a funnel icon above each column that has a filter applied.

   

   Tip: Select more options from the list to filter for more than one data option.

3. To remove a filter on a column, click Clear Filter from the list of options.

   Tip: Use your browser's "find on page" feature to quickly find data in a datasheet view. For example, in Internet Explorer, press Ctrl+F, enter the text or number you're looking for, and then click Previous or Next to advance through the found values

To sort data:

1. Navigate to your Datasheet view in the Access app using your browser.

2. Hover over a column header, click the drop-down arrow, and click Sort Ascending or Sort Descending to sort the records in the view by that column.

To hide a column:

1. Navigate to your Datasheet view in the Access app using your browser.

2. Hover over a column header, click the drop-down arrow, and click Hide Column. Access hides the column from view in the browser.

   Note: Hiding a column in a Datasheet view in your browser is only temporary. If you navigate to a different view and come back or refresh the view, Access displays the column again.

Downloading records from Datasheet views into Excel

You can download records from your Access app into Excel from Datasheet views.

To download records into Excel:

1. Click the Datasheet view when viewing the app in your web browser.

2. Click the Download in Excel action button on the Action Bar.

   

3. Click Open to open the Excel spreadsheet, click Save to save the spreadsheet to a folder, or click Cancel to cancel downloading the records into Excel.

4. Excel displays the records from the Datasheet view.

   

Tip: If you use the filter options in the Datasheet view to restrict the records shown to a smaller set of records before clicking the Download in Excel action button, Access still downloads all the records from the view's record source into Excel. If you want to download a smaller set of records, you can open a Datasheet view from a different view using the OpenPopup macro action and use the Where clause argument to filter the records to a smaller subset. If you click the Download in Excel action button on the Datasheet view in this case, Access downloads the smaller subset of records into Excel.

Note: The Download in Excel action button can't be removed from Datasheet views. If you don't want to display this action button, you'll need to hide the Action Bar for the Datasheet view.

Delete a Datasheet view

To delete an existing Datasheet view, complete the following steps:

1. Open the app in Access.

2. Click the table caption name in the Table Selector in the left pane and then click the Datasheet view.

3. Click the property button and then click Delete.

Rename a Datasheet view caption shown in the View Selector

Access displays a list of view caption names across the top of the app in the View Selector. To rename the caption of a Datasheet view shown in the View Selector, follow these steps:

1. Open the app in Access.

2. Click the table caption name in the Table Selector in the left pane and then click the Datasheet view.

3. Click the property button and then click Rename.

   

4. Type the new Datasheet view caption name and then press Enter.

   

5. Click Save on the Quick Access Toolbar to save your caption changes.

To change the position of the Datasheet view at the top of the screen, click and hold the caption and drag it to the right or left to a new position.

Making a copy of a Datasheet view

If you want to make an identical copy of a Datasheet view, you can use the Duplicate feature in Access apps.

To make a copy of a Datasheet view, complete the following steps:

1. Open the app in Access.

2. Click the table caption name in the Table Selector in the left pane and then click the Datasheet view.

3. Click the property button and then click Duplicate.

   

4. Type the name of the new copy of the Datasheet view in the Name of Duplicate box. Note that each view object in the Navigation Pane must be unique.

5. Select a table to assign this to in the Location for Duplicate box. When you assign a copy of a view to a table, Access displays the view caption name when you click the table caption name in the left pane.

   

   Tip: If you don't want to assign a copy of the view to a specific table, you can select [Standalone/Popup] in the Location for Duplicate box. When you make a copy of the view as a standalone view, Access displays the object in the Navigation Pane but not in the list of views assigned to tables. To open standalone views in your browser, you must use the OpenPopup macro action.

6. Click OK to create the new copy of the Datasheet view or click Cancel to cancel creating the copy of the Datasheet view.

7. Click Save on the Quick Access Toolbar to save your changes to the list of views.

More information about Access apps

Web datasheet views are a feature of Access apps – databases running in a browser that people can use online – but they are only one feature. To learn more about Access apps, see the article Create an Access app.

• Learn how to add controls to a view.

• Learn how to work with action buttons in an Access app.

• Learn how to add custom actions to the Action Bar.

• Learn how to customize a view by creating a user interface macro.

Top of Page