A continuous flow of information is an integral part of every business that helps you determine the business’s daily functioning, gains and losses. Companies have to handle various types of information about customers, employees, operational costs, profits etc and this is where most businesses depend on a data entry service to help manage any volume of information. Many businesses also use cloud based data storage for storing data but sometimes it can be quite complicated. Another alternative is creating an Access web app which helps in data entry and viewing via a browser. However the interface is limited and the data cannot be manipulated. The data is stored in a cloud-based SQL Azure database.
Importing Access web app data into Excel helps to filter, graph, analyze, report etc and importing data is an easy two step process. The easiest way to get the data from the app is simply by copying the data directly from the data sheet into Excel sheet. This method is suitable only if it is a one time data grab because the data is static. The second method is as follows:
- Have a connection: The first and foremost step is to enable a connection to the web app, which should not be skipped. Open the local web app in Access 2016 and click on the File Tab. From the Manage dropdown click on Enable Read-Only Connection and leave the web app file open. The next step is to paste connection values from the web app into Excel. There are many other connection options also but it is important to choose that option which provides the least access but still satisfies all your needs.
- Create a connection: Once the connection app is ready, create a connection to the data in Excel using the following steps:
- Click the Data tab in an open workbook and in the Get External Data Group, click the Form Other Source drop down and choose From Data Connection Wizard and in the first pane click on Other/Advanced and click Next.
- From the list of various providers, choose Microsoft OLEDB Provider For SQL Server or the SQL Server Native Client and click Next. If you are using Excel 2013 and used native client, the remaining steps may not match your experience.
- The next step is to return to Access and choose View Read only Connection Information to display connection values into Excel Data Link properties dialog. Click OK to complete the connection and view the web app components. In the resulting dialog check the Enable Selection of Multiple Tables option and select the appropriate tables. Make sure that you check the Import Relationships between selected tables option and then click OK. Select only the tables that you need and do not go overboard.
- The next dialog prompts you to specify how and where to store the data. Click on Excel table object in a new worksheet and click OK to import the data. With the data in Excel you can now report and analyze the data using features that aren’t available in the web app interface.
For future access create the connection in an Excel workbook and ensure that you are with the most up-to-date data by clicking Refresh, enter the password and click OK. To open the connection again click on Data tab and select connection and click Open to display the Import Data dialog.
Unlike individuals working with comparatively small amounts of data, businesses have huge volumes of data that need to be organized and systematically arranged for easy access, quick recovery and for preventing loss of data. A reliable data entry company can handle any amount of data and store it in the business’s own repository or in preferred cloud storage.
