AppId is over the quota
This post was written by Gary Devendorf, a Program Manager on the Access team. The accompanying video features Doug Taylor, also a Program Manager.
Access 2013 is all about enabling you to build data-centric business apps on the web. Frequently, the data you care about—a customer list, a product spreadsheet, or a legacy Access database—already exists somewhere else. How can you get this data into your Access web app? Obviously, copying each record would take a long time. That's where data import can be a huge time saver.
Access 2013 web databases support importing data from a variety of sources. Check out a few of the options by opening your web database in Access and clicking "Table" in the ribbon. From, there, under "Create a table from an existing source," you'll find a variety of options, including Excel, SQL Server, SharePoint, and previous versions of Access.
Data import includes more than simply copying data from one source to another. For example, if you import from an existing Access database, you get table definitions, relationships, and views, so you can start interacting with your imported data immediately.
When your existing data is from an unstructured source, such as Excel or a text file, you can use Access to help you clean up your data and normalize it. Imagine that you want to import an excel spreadsheet that looks something like the one below:
In one column, you have a list of national parks. In another, you have a list of states in which the parks are located. Notice how the state names are frequently repeated. If you wanted a unified view of the parks in each state, this way of organizing things could easily create problems. Adding more structure to this data could help guard against data entry errors, such as spelling mistakes in the state name, which would make analyzing the parks by state difficult.
For this example, we are going to break the import into two steps. First, we're going to create a new blank desktop Access database. We'll import the Excel data and use the Table Analyzer Wizard to clean it up and add structure. Then, we'll import the tables into an Access 2013 Web Database. (The reason for this two step process is that the Table Analyzer Wizard is currently only available in desktop databases. If we simply wanted to copy the data from Excel without cleanup, we could import directly into the web app).
First, create a new desktop database by opening Access 2013 and clicking "Blank desktop database." In the ribbon, select the "External Data" tab, and click "Excel" in the "Import & Link" section. We'll tell Access the location of our Excel file and choose to import the source data into a new table.
The next dialog shows the data found in our Excel spreadsheet and asks if the first row contains column headings. If the first row did have column headings, such as "Park" or "State," Access would use them to name the table fields. In our case they do not. Click "Next".
The import wizard lets you specify what parts of the spreadsheet you would like to import. We one have data only in Sheet1, so we can simply click "Next".
Notice below that the wizard is naming the columns, "Field1" and "Field2", and has assigned the datatype "Short Text" to Field1. Access inspects the first 10 rows and tries to intelligently determine the appropriate data type. You can change that assignment, specify it as an index, or even skip the column all together. Hightlight the next column to see or change its options. Click "Next" when done.
Now that the fields are defined, Access will ask us how to handle primary keys. If you're not familiar with the concept of a key, don't worry—the default choice ("Let Access add primary key") is almost always the one you want.
On the last screen of the import wizard, we'll supply a name for the new table and choose to run the Table Analyzer Wizard so that we can clean it up and add some structure.
The wizard provides some helpful dialogues to explain how it works. We'll click "Next" until we arrive at this screen:
By selecting "Yes, let the wizard decide," we're asking Access to intelligently suggest the best way of cleaning up our data. Let's click "Next" and see what Access comes up with:
In our case, Access is suggesting that we break up the data into two tables and create a relationship between them. One of the tables will contain a list of the states. The other will contain a list of the parks, and a second column that references the states table. Notice the wizard shows a "1" on the right side of the relationship and the symbol for infinity on the left. This means that there is a "one to many" relationship between the tables where you could have many Park names for one State name.
A final check of the data shows that there may be an error. Remember that the State names are listed multiple times in the original spreadsheet as a state can have several national parks in it. It looks like Arkansas, in at least one case, is spelled "-kansaw". The wizard is asking us decide what to do with this case.
Finally, the last question is whether to create a Query that joins the tables together.
After we are done with the wizard, the result is a desktop Access database with three tables and a query. The original imported table is renamed with "_OLD" added. The new query is called by the original table name "MyNewTable" (queries act very much like tables). The tables that came from splitting the original data (Parks and State) are also present. Notice the State table has links to the national parks found in those states.
The last step is to save this Desktop database and import it into your new Access 2013 web app. Go to File > New > Custom web app. Select a location for your app, and click "Create." Click the "Table" button on the ribbon and select "Access" under the "Create a table from an existing data source." You can import the desktop database that you just finished creating. Since you took advantage of the Table Analyzer wizard in the desktop database, your data is cleaner and more structured than it would be if you had simply imported directly into the web app.
Importing brings in external data, structured or unstructured, and adds structure when needed. You don't need to know about complex relational database issues like normality or referential integrity. You can quickly customize generated views or just use them as is. By moving your data from its old location into your Access 2013 web app, you can take advantage of the easy to use interface and simple sharing that comes built-in.
Try it out for yourself using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.
No comments:
Post a Comment