Register now: UK Access User Group National Seminar November 21

AppId is over the quota
AppId is over the quota

If you can't make it to Access Day in Seattle on November 21, why not attend the UK Access User Group 2013 National Seminar at the Microsoft Thames Valley Park in Reading?

The UK Access User Group conference is presented by and for Access enthusiasts, and the fall 2013 seminar features 5 sessions on a variety of topics:

Shape Up Or Ship Out! Microsoft Access Case Study (Presenter: Paul Millennas)
Paul will show how Microsoft Access has been used at Babcock Engineering at Rosyth on £12billion worth of projects including the construction of aircraft carriers, ships and submarines.

Adding Value To Your Applications By Integrating to Accounts Software with Specific Examples
using Sage 50 (Presenter: Tony Bayliss)
Nearly every Access project includes integration, and Tony will show how this has been achieved on some of his recent projects.

The Patient Death--What Happens Next? How a Hospital Handles and Manages the Human Side of a Patient Death (Presenter: John Baker)
John will present a case study of an application used in a key area of the National Health Service.

Web Application in Access 2013 Case Study (Presenter: Alan Cossey)
Alan will take us through the stages of development of an Access 2013 web database which includes both a standard Access front end and a web UI.

The Way Forward: A Discussion Session (Presenter: Rod Gordon)
If you are asked to develop a new system in the years to come, then what technology will you use? Building on the day's presentations, Rod will lead a discussion session considering the different options available to you today and in the coming years.

Book your spot now for the fall 2013 National Seminar, and visit the UK Access User Group site for full seminar details.

View the original article here

The Access 2013 Runtime now available for download

AppId is over the quota
AppId is over the quota

After a long, hard effort by many people, the stars have finally aligned. The Access team is pleased to announce the release of the Access 2013 Runtime! We know lots of you are very anxious to get your hands on the Access 2013 Runtime. You can now go and download the Access 2013 Runtime from the following location:

http://www.microsoft.com/downloads/details.aspx?FamilyID=0c44d623-a8bc-4be5-a561-f11d33602505

The Access 2013 Runtime is available for download in 38 languages.

Microsoft Access 2013 provides a rich platform for developing database management solutions with easy-to-use customization tools. If no end user customization is required (including report modifications), you can choose to distribute those Access 2013 solutions so that they run without requiring a full installation of Access 2013. To do so, you must package and distribute your application with the Access 2013 Runtime.

Web-based apps for SharePoint that are built with Access 2013 do not require a runtime--only a supported web browser is required.

Enjoy!

View the original article here

Attend Access 2013 presentations in Denver on July 2 and July 11

AppId is over the quota
AppId is over the quota

Join our own Kevin Bell as he takes a break from his summer vacation to show off the new Access 2013! Kevin will present to the Denver Area Access User Group meeting on Tuesday, July 2 at 6-9pm MDT and to the Colorado SharePoint Users Group on Thursday, July 11 at 6-8pm MDT. 

Both meetings will be held at the Microsoft office located at 7595 Technology Way, Suite 400, Denver, CO 80237.

Access 2013 web apps allow you to quickly create a database in Office 365, but if you are familiar with traditional Access desktop development, you may encounter some limitations. One of the best features in Access 2013 is that web apps created on Office 365 use SQL Azure to store their data. Unlike traditional SQL Server databases, SQL Azure is cloud-based, so your data can be accessible anywhere. However, every byte of data requested from the Access client is now traveling over the internet, so you need to be very diligent about returning the least amount of data possible. Kevin will show several techniques to help you convert your existing desktop applications to an Access 2013 'hybrid' app. Go to www.daaug.org to learn more about the Denver Area Access User Group.  

With the new Microsoft Access 2013 and Access Services 2013, anyone can build SharePoint Apps backed by SQL Azure in just about 60 seconds. In this presentation, Kevin will show you how easy it is to create, share, and collaborate with Access 2013 in an IT managed environment. The new Access allows you to satisfy the need for agile development from your business units and still rest assured that the data is secured, backed up, and managed appropriately. Kevin will also talk about how legacy Access desktop applications within your organization can be brought forward to the web on SharePoint and SQL Azure. Go to www.cospug.com to learn more about Colorado SharePoint Users Group. 

Kevin Bell is a test engineer on the Microsoft Access team in Redmond, Washington. Prior to joining Microsoft in 2008, Kevin was a partner in a consulting practice in Colorado that specialized in building software solutions for small businesses and departments in large corporations, primarily working with Microsoft Access and SQL Server. Over the years, Kevin has been a member, officer, and frequent presenter at the Denver Area Access User Group and now regularly presents at the Seattle Access User Group and the annual Portland Database Designers Conference. This past November he had the pleasure of presenting at the 2012 UK Access Groups National Seminar in London.

View the original article here

The McKinstry Tool Tracker App: Using Access 2013 and Office 365 to manage critical assets

AppId is over the quota
AppId is over the quota

It's a common scenario: There is a collection of things that people borrow and return. And any collection has common problems: How do you track the items? Are they available? If not, who has them? Where are they? When will they be returned? What is their condition?

For McKinstry, a builder and manager of energy-efficient commercial buildings, the problem centered on a collection of tools, from meters and sensors to hammers. McKinstry employees and subcontractors use the tools to complete energy audits and small-scale construction projects. Their solution for tracking the tools was simple: a clipboard hung in the tool room. If someone needed a tool, they would go find it in the tool room and, hopefully, mark the tool as checked-out on the clipboard. 

The drawbacks are obvious: People who needed a tool had to go to the tool room to see if one was available, and sometimes they'd forget to mark the tools when they checked one out. Tools were not where they were supposed to be, often getting left on job sites or forgotten about and not returned. Over time, the problem got worse and ended up costing the company thousands of dollars to replace lost and damaged tools.

Enter Access 2013 and Office 365. Microsoft worked with McKinstry to create a tool tracker app using Access 2013 (and hosted on Office 365) for the company to try in their Colorado office. The app features a table for tools, with views that show which are available or checked out, displayed by their storage location. There are also tables for people, jobs, and storage locations, which make it easy to associate this data with each tool. A checkout history shows the usage history of each tool displayed within the new Related Items control. The checkout history displays the last time the tool was checked out and who checked out the tool.

The Access web app UI provides a clear, easy-to-use layout for navigating the data in an Access app, a built-in Related Items control that shows the items associated with the record being viewed (think customers and orders), and a fast text-based search for data. Initial feedback was very positive, but some tweaks were requested, such as a home page with basic directions and buttons for the most common tasks (like checking out a tool).

With the early feedback addressed, daily use began at McKinstry. People could now find out whether a tool was available online from their office before going down to the tool room. They could see where the tool was stored, the condition of the tool, and its usage history. They could see who had the tools and when they were expected back.

While the UI and ease-of-use was appreciated, searching for a tool by typing in the tool name proved to be too much overhead for the people in a hurry to get out the door to their job site. They asked if a faster solution was available, such as a barcode scanner like those used for checkout in retail stores. The feature appears complicated but was actually quite simple to implement in Access. The scanner simply sends keystrokes through a USB connection, so Access uses built-in search to instantly identify the tool based on the code in the record for that tool. A data macro launched from a custom Action Bar button checks out the tool and adds a record to the checkout history with a simple button click.

McKinstry uses the Access app in their Colorado office using an Office 365 account, and plans wider adoption into other offices using either Office 365 or an on-premise app once they deploy SharePoint 2013. They know where their tools are and their current condition--improving productivity and providing a significant cost savings. Watch the video to see the tool tracking Access app in action.

View the original article here

Review: Daemon Tools USB lets you access and share your remote USB devices

AppId is over the quota
AppId is over the quota

Daemon Tools made a name for themselves with simple, no-nonsense optical drive emulation software that allows your disk libraries to go from cake boxes filled with silver platters to immediately accessible disk images stored on your hard drive. While commercial upgrades have appeared over the years, the free version retained most of the handy features and became a staple power user's tool. The company's focus has barely shifted over the years, with variations on platform support and paid features as the only real changes since the first version of Daemon Tools rolled out. Their latest product, Daemon Tools USB ($7, 20-day free trial), is a modest attempt to move away from the emulation business with a utility that allows remote access to USB-connected devices as if they were plugged in locally.

Shareable USB devices appear in the Local tab.

The ambitions here are modest and comfortably met. By running a copy of the software on a host system, you gain access to USB-connected devices such as thumb drives, printers and hard disks on other systems also running Daemon Tools USB. You can specify passwords and configure custom port numbers and proxy servers, so security (if not particularly robust security) is provided.

The interface is a model of simplicity, but this is less of a compliment than a consequence of its singular purpose. Access speed is swift and reliable under most circumstances, although this is largely dictated by network bandwidth. Despite its simplicity, I still had trouble accessing a few devices, such as a SanDisk portable USB drive that refused to allow remote access, while others worked perfectly.

Preferences are sparse, but Daemon Tools USB does support proxies and custom port numbers.

The interface is a tabbed window that allows you to specify and configure local USB devices for sharing and to access remote devices you've added to your server list. Under most circumstances, simply sharing the same device over a network via the normal OS route would duplicate this functionality.

There are a few scenarios that Daemon Tools USB simplifies, however. Printers and webcams often require a direct USB connection for access to management software, for example. Daemons Tools USB is a good fit for these situations. Not too many people need to share devices under those circumstances, however.

Server lists provide access to your remotely connected devices.

This brings up the next problem: price. While $7 doesn't seem a lot of money, Daemon Tools USB doesn't do much. Sure, it's just the price of two cups of coffee at Starbucks, but I like coffee and enjoy it every day. I doubt I could say the same about a $7 investment in Daemon Tool USB. I'm sure there are people out there looking for the solution this software provides; I just haven't met them yet. That makes the price a bit of stretch, especially since no free modes of use exist beyond the 20-day initial trial.

If this software appeals to you, you likely already know about it, although that doesn't make the cost any less irksome. Workarounds exist for almost every usage scenario this utility covers. For the rest of us, it's the answer to a question no one asked.

Note: The Download button on the Product Information page takes you to the vendor's site, where you can download the latest version of the software.

View the original article here

Visualize your Access 2013 web app data in Excel

AppId is over the quota
AppId is over the quota

This post was written by Harrison Shapley, a Program Manager on the Access team.

After you start organizing your data with Access 2013 web apps, you may find yourself wishing to visualize or summarize it. For example, imagine you are tracking your deal pipeline in Access, including when you expect to get paid and how much. Wouldn't it be great if you could make a visual graph of these projections to help you make decisions?

That's where external data connections come in handy. By following the steps below, you can connect an Excel workbook directly to the SQL Server database that stores the information in your Access 2013 web app. Then, you can build live graphs and visualizations using Excel. Of course, you could copy and paste the data from an Access datasheet into an Excel workbook, but if the data in Access changes, you'd have to repeat this process each time. By setting up the connection, you streamline the process, making it much easier to generate up-to-date reports.

Best of all, you can use this same technique with other programs and services that know how to communicate with a SQL Server database, such as Crystal Reports, Zapier, desktop Access databases, and custom web pages. The possibilities are vast, but for now, we'll focus on Excel.

There are many ways to summarize data in Access 2013 web apps. If Excel is your application of choice, you can follow these steps to connect your Excel workbook to your Access 2013 web app database for easy reporting.

Step 1. Enable connections to your database

Open the app in Access, and then go to File to view the backstage (also known as the Info section of the file menu—see the picture below). There you will see information about your database, and you have options to report on your data and manage connections to your database. Before you can create reports, you need to enable connections to your database from the Connections menu. At the minimum, you will need to Enable Read-Only Connections. Depending on your situation, you may also need to enable connections "from your location" or "from all locations".

Step 2. Create a connection to your database in Excel

After enabling connections to your database, open up a new workbook in Excel and navigate to the Data ribbon. You want to Get External Data from your SQL database, so select the From Other Sources button, then From Data Connection Wizard. The parameters of the connection need to be specified manually, so selecting "From SQL Server" will not work. Once the Data Connection Wizard is open, select Other/Advanced and click Next. Select the SQL Server Native Client and click Next again.

 

You will then be prompted for the database connection information. If you go back to the backstage in Access, you will find an option to View Read-Only Connection Information in the Manage Connections menu. Selecting this option will bring up all of your connection information, including the server name, database name, username and password. This information can be copied directly into Excel's data connection wizard.

Copy the information as shown below and then click OK:

Step 3. Select the data to link

The next screens will ask you about what tables you want to bring in from your database. On the first screen, uncheck Connect to a specific table and click Next.

On the second screen, make sure to select Enable selection of multiple tables and Import relationships between selected tables. You only need to select the tables you want to report on, and make sure not to select any tables from AccessSystem or AccessRuntime. In this example, all of the tables were selected to be linked.

Finally, a dialog comes up asking what you want to create, including a regular table, a PivotTable, and a PowerView report. In this example, we created a PivotChart.

Step 4. Create the reports and visualizations you want

Once you click OK, a live connection will be made and the data will be fetched from the SQL server and shown on the right. You can then use the full capabilities of Excel as you normally would to create your report.

In this case, we were creating a PivotChart to show the companies with the most orders, so we brought the Company field from the Customers table to the Axis area and the Order ID field from the Orders table into the Values area. Instead of the sum of Order IDs, we wanted the count of Orders, so this was changed from the dropdown in the Values area. Finally, we needed help Excel understand how the two tables connected with each other, so we created a new relationship linking the Customer ID field in Orders to the ID field in Customers. The PivotChart now displayed as desired, and we could easily see that Company F and Company H had made the most orders.

In summary, we covered how to create reports in Excel, but this is just the beginning of what you can achieve by exposing your data through ODBC connections. By exposing your SQL database to ODBC connections, you can use any application that supports ODBC. This permits many possibilities for extensibility to any kind of custom data application, not just Access and Excel. We can't wait to see what you do with this feature! Please share your experiences and feedback in the comments section below.

View the original article here

Filter Your Data with Access 2013

AppId is over the quota
AppId is over the quota

This post was written by Doug Taylor, a Program Manager on the Access team.

As you manage your business in an Access 2013 web app, the amount of information it holds will naturally grow over time.  The default views and navigation are great for getting started quickly, but eventually, wading through all of that information by scrolling through lists may not be the most efficient setup.  Is there an easier way to create a different view of the data?  In Access 2013 web apps you can create a customized filtered view in just a couple of minutes.

For example, let's say you work for a marketing company that deals with local Car Dealerships and you have created an Access app to track the projects you manage for your customers.  If so, you might end up with a set of Projects that looks something like the example below.  Here you have a list of all your projects from A-Z. 

Over time, you will have quite a few projects to manage, so you may start to break them into categories as we have above.  Each project in this list belongs to a category: Research, Marketing, or Design.  As you can see, the Templeton Triangle project shown above is a Research project.

Now, one of your research assistants - let's call him Dave - asks if you can create a view to help him see an overview of current and upcoming Research projects.  So, you take a minute to talk with Dave and the two of you decide that a view like the one shown below would be great for him.  This is a list of all Research projects, sorted by Priority and Start Date.

To create a view like this, start by creating a new query - open up the app in the app designer and click on Advanced->Query in the ribbon.

This will open the Query designer where you can setup some rules to filter and sort a set of data.

Choose the source table for your query.  If you want to see some of your Customers, then you would likely choose the Customers table here.  In our example, we're going to select Projects. 

Once selected you'll see the query design screen.  Here you can double click on fields from the Projects table to add them to your query. 

Selecting fields this way determines which of them you would like to see and use in the new view.  As we decided with Dave earlier, we're going to select the following fields:

IDPriorityStart DateProject NameStatusCustomerCategory

NOTE: If you would like to EDIT from the view, then you MUST INCLUDE an ID field in the query as we have done here.

Now that you have chosen the fields, there are two things left to do. 

First, Dave wants to see the Projects in PRIORITY order first, followed by START DATE.  To do this, fill in the "Sort" box in each of these fields as Ascending (i.e. A to Z).

Second, since Dave is a research assistant, he's only really interested in Research projects.  So, we'll want to filter out any other Project types as well.  To do this, find the Category field in the query and add the CRITERIA "Research" (including the quotes).  This tells Access to only show Project that have a category of Research. 

When you're finished, the query should look like the example below.  Now you can save it, and give it a name.  We've named our example "Projects_Research Only".

Great!  Now that we're done with that, we can go about building the view.  Go back to the App Designer home screen, navigate to where you would like to add the view, and click on the  button.  Give the view a name, select Datasheet for the View Type, and be sure to select the Query you just made as the Record Source.

Now, when you click "Add New View" you will have a nice summarized view of your Research projects, sorted by Priority and Start Date. 

And that's how to design a simple filtered view in Access 2013! 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.

View the original article here

Introducing Access 2013

AppId is over the quota
AppId is over the quota

The Access team is proud to introduce the Access 2013 public preview, which will make it easier than ever for everyday people to organize the data in their lives and businesses using Access apps. On this blog, you'll be able to learn about the improvements included in this new release.

Access has always been a great tool to help you organize and run a small business or a team. This release focuses on bringing Access databases to the web, making them more useful than ever. Your database can be hosted by Microsoft through Office 365 and securely accessed from any tablet or computer—even if the device doesn't have Access installed.

Getting started is easier, too, taking just 60 seconds to get your first Access app up and running. With little or no additional effort, you'll end up with a finished app that is both functional and beautiful—automatically—thanks to an enhanced user interface. Finally, we've made some big improvements under the covers to make your apps faster and more extensible. Your data is now stored in a full-fledged SQL Server database. When Microsoft hosts your database in the cloud, we'll use SQL Azure; when you host it yourself on your network, you can use SQL Server 2012. Advanced users will love the fact that they can directly connect to SQL Server with familiar tools for powerful analysis and integration.

Here's a peek of some of the things we'll be talking about:

If your Office 365 plan includes SharePoint, you can host Access 2013 databases with no extra setup required. Microsoft will make sure your data is secure, backed-up, and available, so that you can focus on getting things done. You can try it out by signing up for a preview of Office 365 Small Business Premium or Office 365 Enterprise. Whether you’re a small business or a large corporation, you’ll be able to harness the power of Access 2013 in the cloud simply and easily. Of course, companies also have the option of hosting databases themselves by installing SharePoint 2013 and SQL Server 2012 on their own network.

Search from a library of table templates to help you track the people, things, events, or tasks that you care about. Each table template comes with fields, views, relationships, and data-integrity rules, so you can take advantage of all the great features of Access 2013 with a single click. It’s easy to combine different table templates into a single app or tweak an existing table by adding or removing fields. You’ll get to spend your time customizing your database to meet your  unique needs instead of worrying about repetitive details.

Whether you use table templates, import existing data, or define your own schema from scratch, Access 2013 will provide your database with a great user interface automatically. Without any effort on your part, Access will generate views for your data, including a searchable list view and an Excel-like datasheet. Buttons to navigate between your views and tables come for free, too.  If you have related data—like Invoices and Line Items—Access will automatically create views that show these items together, allowing you to drill-through to get more details. Of course, everything is still customizable, but now you can focus on what's unique about your app. 

Access 2013 web databases work great with SharePoint 2013, which has been enhanced in this release with apps for SharePoint. Because an Access app is just like any other SharePoint app, it’s easy to deploy, manage, and share securely. There are no additional passwords or logins to juggle because security is controlled through the same infrastructure. Users can discover and share Access apps through the public SharePoint App Store or a private App Catalogue. Installing an app takes just a few clicks. Corporate IT can control everything centrally using familiar SharePoint tools. Best of all, anyone with a web browser and an internet connection can use your app, even if he doesn't have Access installed on his device.

One of the biggest improvements in Access 2013 is one you may not even notice—except that you're whole app will be faster, more reliable, and work great with large amounts of data. When Access databases are published to SharePoint—whether on-premise or through Office 365—a full-fledged SQL Server database is automatically created to store the data. Advanced users who are already familiar with SQL Server will be able to directly connect to this database for advanced reporting and analysis with familiar tools such as Excel, Power View, and Crystal Reports. Everyday users can rest assured that their apps are ready for the future if they ever need to enhance them with advanced integrations or migrations. Check out the Access 2013 developer center for more details.

 

We're looking forward to introducing you to what's amazing and new about Access 2013. Stay tuned!

View the original article here

Moving and backing up your Access 2013 web apps

AppId is over the quota
AppId is over the quota

This post was written by Bob Piper, a Program Manager on the Access team.

When files live on your computer's hard drive, there's a risk that they could be damaged, lost, corrupted, or deleted. To protect against this, most savvy businesses make copies of important files in separate locations. In a similar way, Microsoft takes many precautions to make sure your Access 2013 web apps on Office 365 are backed up and always available. However, making your own backups is still a good idea. How can you make a copy of your apps that can be restored to a different location?

Access 2013 web apps, just like all SharePoint apps, can be stored in a special type of file known as an App Package. You can create backup copies of your databases in App Package files and restore them by uploading them to any SharePoint 2013 site. Knowing how to do this would also be useful if you wanted to move an app from one Office 365 account to another.

When you visit your Access app in the browser, you will see a gear icon in the upper-right corner of the screen.  Clicking this gear allows you to open the app in the Access 2013 client.  In the client you will be able to customize the app, or, in this case, save it as a package.

With the app open in the client, simply click on "File" in the upper left corner, and choose "Save as".  Then click "Save as Package". 

You will be asked to name the app package, and you can decide whether to include data. If you uncheck this box, the resulting app will contain all the views, tables, macros, and queries in your app, but none of the records that are personal to your business. This could be useful if you wanted to share the design of your app with another person without exposing your business' data.

After you click OK, you can decide where to save the app.  A moment later, your app will be saved to that location.

Now that the App Package file is saved to your machine, you can load it onto any SharePoint Online site. To do this you will be using the "App Catalog." The App Catalog is a place that stores the apps that are available for someone to add to a SharePoint site. In order to make apps available on the catalogue, you'll need to have Owner or Administrator permission for the site.

In the version of SharePoint included in Office 365 Small Business Preview, you'll need to go to a special URL to access the catalogue. To find this location, go to any page on your site. You'll notice that the URL in your browser will look something like this:

https://mycompany.sharepoint.com/_layouts/15/start.aspx#/SitePages/Home.aspx

Just remove the portion after "...sharepoint.com", and replace it with "/sites/AppCatalog" so it looks like this:

https://mycompany.sharepoint.com/sites/AppCatalog

Then click "Apps for SharePoint" in the menu on the left:

On the Apps for SharePoint page, click Add new item, and upload your app using the dialog.  When complete, you'll see the app in the list:

Your app is now available to add to your site. Go to the "Site Contents" page from the menu on the left. From there, when you click "Add an App"...

...you will see the app under "Apps you can add":

Just click the app and then click Trust It:

You'll be taken back to the Site Contents page, the app will load, and you are all set to begin using your app on the account.

Now that you know how to package your app and restore it on any site, you'll be able to make backups of your Access 2013 web apps and the data inside. This will enable you to keep snapshots of your app at any given time. You'll also be able to move the apps that you design using one account to any other account.

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.

View the original article here

Moving data forward into Access 2013

AppId is over the quota
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.

View the original article here