Adding new tables to your web app

Access provides a handy list of table designs for you to choose from if you’re starting your web app from scratch.

Here’s how to add a table:

1. Click Table from the Create group on the Home tab of the Ribbon if necessary (you’ll be here by default after creating a new web app).

2. Type a keyword in the search box that represents the type of data to go into your table.

   For example, type contacts for contact-related tables or order for customer- and order-related tables.

3. Click search (the magnifying glass icon).

   Access displays a list of related tables (see Figure 11-6).

4. Select a table.

   Access creates the table. The table name appears down the left side of the screen.

Access web apps do not support the OLE Object, and Attachment data types that that Access desktop databases support. For more on data types, see Chapter 3.

Uploading an existing table to your web app

Access gives you the ability to upload a data table from an existing source such as an Access desktop database, an Excel spreadsheet, or text file. Follow these steps to upload an existing Access table. Steps for other file formats are similar.

1. Click Table from the Create group on the Home tab of the Ribbon if necessary.

2. Click Access from the Create a table from an existing data source list (Figure 11-7).

   The Get External Data dialog box appears, ready to serve.

3. Click Browse, navigate to the file location, and then click on the file that contains your table.

4. Click Open.

   The path and file name are entered in the File name text box in the Get External Data dialog box.

5. Click OK.

   The Import Objects dialog box appears.

6. Select the table name and click OK.

   The table and its data are imported into the web app.

7. Click Close.

   The imported table appears down the left side of the web app screen.

Web app tables are maintained just like Access desktop database tables. Just open the Navigation Pane (F11), right-click the table, and choose Design View. If you read Chapters 4 and 5 (and I know you did!), you already know all about editing and designing Access tables.

Web app forms

When you create a web app table, Access is such a great pal that it also creates two views for you. While a bit confusing, it’s not quite the same as the views you are familiar with in desktop Access such as Design view and Datasheet view. A view, in this context, is a way for you to see and edit data in a table similar to a form in the desktop version of Access. In fact, Access classifies these views as forms in the Navigation Pane (see Figure 11-8). Unlike tables, though, views do not look like desktop Access forms. They’re actually web pages.

The first view Access creates is called List view. It displays table data one record at a time. The second view is called Datasheet view. Sound familiar? It’s just like desktop Access’s Datasheet view. It displays table data in a spreadsheet-like format.

Unlike desktop Access, in a web app, you can’t see data in Datasheet or List view from the Navigation pane. To see data in the view, you must open it in a web browser (see the next section, “Launching your Web App in a Browser”).

Customizing web apps is an advanced topic beyond the scope of this book. If you’re interested in learning more, search the Internet for “Create and customize a web app in Microsoft Access.” You’ll find several great articles on how to get the job done!

Launching your web app

When you’ve created your tables and Access has created the corresponding views (remember your pal Access does that for you!), it’s time to take a look at the web app in a browser. When you launch your app, you’ll see the tables you created or uploaded listed down the left side of the screen (Figure 11-9). When you click a table name, the data from that table opens in List view ready for data entry. Records are listed down the left side of the view. When you click on a record, you’ll see the data from that record on the right side of the view. You can change to Datasheet view by clicking (oddly enough) the Datasheet link at the top left of the form.

Each form has a series of command buttons across its top called an Action Bar. By default, the Action Bar has buttons for common record management actions. Figure 11-10 shows you the Action Bar buttons with an explanation of what each does.

Data entry in the browser is very similar to data entry in desktop Access. The two major differences are you must click a button to edit a record and another to save it. Don’t worry though: If you forget to click Save and then try to move to another record, Access prompts you to save. Access and you, perfect together! Here’s how to launch your web app:

1. Click the Home tab on the Ribbon if necessary.

2. Click the Launch App icon (Figure 11-11) in the View group of the Ribbon.

   Your browser opens showing your web app.

To launch a web app from any browser on any device, you’ll need its URL (web address). Open the web app in desktop Access (File  ⇒  Open on the Ribbon). Click the File tab on the Ribbon and then Info. You’ll see the URL at the top of the Info tab. If you click on the URL, you’ll see a shortcut menu choice to copy it to the Clipboard. You can email the URL to yourself and launch the web app from that email on your mobile device. Make sure to bookmark it after opening!

Adding a record

Once you’ve launched your web app, List and Datasheet views give you two different ways to add a new record. List view is best if your table contains many fields and you don’t like scrolling. Datasheet view, as you can probably guess, is best if your table contains a small number of fields (generally, less than fifteen) and you do like scrolling! Once you’ve launched your app, follow these steps in either view to add a new record:

1. Click the name of the table to which you’d like to add data.

   The table opens in List view displaying the first record in the table. Click Datasheet if you want to add your records in a datasheet. Remember, tables are listed down the left side of the screen and have star symbols next to them.

2. Click the Add icon (+) on the Action Bar to move to a new record.

   The form moves to a new record and the cursor is placed in the first field.

3. Add data for the new record to the appropriate fields on the form.

4. Click the Save icon (disk) on the Action Bar.

   The record is saved. You must click the Save icon in a web app to save data, unlike desktop Access where records are saved automatically when you change records or close a form. Don’t panic though, if you forget to click Save and change records or views in your web app, Access will prompt you.

Editing a record

Once you’ve launched your web app, List and Datasheet views give you two different ways to edit data. List view has a filter feature, making it easy to find a record. Datasheet view assumes you’ll scroll through the data or use your browser’s Find command (Ctrl+F in most browsers) to locate the record in question. List view requires the click of an icon to switch to edit mode. Datasheet view is already in edit mode.

Here’s how to edit a record in List view. The steps for Datasheet view are similar.

1. Click the table name that contains the record you’d like to edit.

   The table opens in List view. Remember, tables are listed down the left side of the screen and have star symbols next to them.

2. Click in the filter box and type a word from any field on the record you’d like to edit.

3. Tap the Enter key on your keyboard.

   The form filters to the record or records with your keyword (Figure 11-12). If the keyword matches multiple records, you’ll see the data from the first field for each record listed down the left side of the form. Click that data to switch to the desired record.

4. Click the Edit icon (pencil) on the Action Bar.

   The form switches to edit mode. Datasheet view doesn’t have an Edit icon. Just click in the field you’d like to edit and type away!

5. Edit the record as necessary.
6. Click the Save icon (disk) on the Action Bar to save your changes.

Deleting a record

Once you’ve launched your web app, steps for deleting a record are identical regardless of view.

1. Click the table name that contains the record you’d like to delete.

   The table opens in List view.Tables are listed down the left side of the screen and have star symbols next to them.

2. Locate the record you’d like to delete.

3. Click the Delete icon (trash can) on the Action Bar.

   You’re asked to confirm the deletion.

4. Click Yes in the delete confirmation message box.

You can’t undo a record after you delete it! Proceed with caution!

Linking to a web app from an Access desktop database

It is possible to connect to web app tables located in the cloud or your locally hosted SharePoint site from your desktop database. This means your data is in one place (cloud or local SharePoint site) and you’ll use two different applications to access it – the web app and the desktop database. This is an advanced topic beyond the scope of this book. However, this is a very important piece of information to know before planning and building your database. If you’re like me, you’ll want your data in one place (in the cloud) and the ability to connect to it from multiple sources — your desktop database and your web app via a browser.

This means you can have people in the office in the desktop version of your database and out of the office using your web app editing the same data. All you need is an Internet connection (easy) and a well-designed database and web app (not quite as easy, but you can do it!). If you want to know how to do this, search the Internet for “How to make external connections to an Access web app.”

Adding a Hyperlink field to your desktop database table

Access provides a handy field type specifically designed for this special type of data. As you probably guessed, this type is called the Hyperlink field.

Adding a Hyperlink field to a table doesn’t require special steps. Just use the same steps for adding any field to a table — get to Design view for your table and use the Data Type column to choose the Hyperlink data type, as shown in Figure 11-13.

The Hyperlink data type is no different from the other field types in terms of applying it. When you hop back to Datasheet view, you’ll see that your entries (if any) in the Hyperlink field are underlined, looking just like link text on a web page.

To switch between Design and Datasheet views of your data, click the View button on the Ribbon’s Home tab or its Design tab. It’s the first button, and appears either as

• A table icon (a small grid)
• An icon combining images of pencil, ruler, and angle

Fine-tuning your hyperlinks

Once you’ve created your hyperlink field, enter your hyperlinks into the field with the Edit Hyperlink menu. Follow these steps to enter a hyperlink:

1. Right-click the hyperlink field you want to change in your table.

2. Choose Hyperlink and then select Edit Hyperlink from the pop-up menu, shown in Figure 11-14.

   The nifty little dialog box shown in Figure 11-15 appears.

3. Use the dialog box to fill in the following information:
   • The text that’s displayed.
   • The ScreenTip (the little text that pops up when your mouse pointer hovers over a web address). To create one, click the ScreenTip button and use the resulting Set Hyperlink ScreenTip dialog box to create your tip — then click OK to return to the Edit Hyperlink dialog box.
   • Links for documents, spreadsheets, graphics, or even email addresses in an Access database.
   • The site address.

4. Click OK.

   Your hyperlink is edited, showing the text you choose to display and pointing to the web address (document or other file) that you designate. If you choose to create a ScreenTip, you can test the new tip in Datasheet view by mousing over your hyperlink and seeing what appears.

Pretty neat, huh? Experiment with the dialog box a little to find out how everything works. It takes only a moment.

Although most hyperlinks store web or other Internet addresses, they can point to just about anything in the cyberworld. Thanks to their flexible tags, hyperlinks understand web pages, intranet servers, database objects (reports, forms, and such), and even documents on your computer or another networked PC.

Testing links

Hyperlinks in your table work just like the ones you find on the web — just point and click. You can double-check for yourself by doing the following:

1. Make sure you’re connected to your network and the Internet.

   Although Internet Explorer (or whichever browser you’re using) will open whenever a hyperlink that points to a website is clicked, if you’re not online at the time, it won’t go where the hyperlink points.

2. Open the Access desktop database you want to use.

3. Open the table containing hyperlinks.

   The fun is about to begin!

4. Click the hyperlink of your choice.
   • If the hyperlink leads to a web page, Internet Explorer (or whatever browser you’ve set as your default) leaps onto the screen, displaying the website from the link.
   • If the link leads to something other than a website, Windows automatically fires up the right program to handle whatever the link has to offer.