One of the added benefits of using Microsoft Access databases is how close a relationship it has with Microsoft Excel.
Sharing data between Access and Excel is common practice whether you copy and paste the data or import or link to data tables. However, using the copy and paste data method can have problems not too mention the volume of information collected which can exceed the Windows clipboard that temporarily holds this information.
The most desirable way of using importing data is by using the Import or Link commands within Microsoft Access. Once the data has been imported (or linked), you then treat this like any other data table and continue to build your queries, forms and reports in the normal manner.
Which method; link or import?
You need to know the pros and cons of importing and linking data from an external source.
As mentioned, there are two methods:
- Importing Data
- Linking Data
Use the Import method to physically store data in an Access Table which is a far more efficient way of physically copying (copy and paste) data across applications. This becomes a real ‘stand-alone’ data set and has no dependencies to how data is managed. It should be used as either a one-off routine to migrate data into a new application or perhaps if it is going to be a regular task (a certain frequency), then wrapped up into an Access Macro to automate it (which is very easy to do using import commands available like ‘TransferSpreadsheet’ or ‘ImportExportSpreadsheet’).
Use the Link method to create a connection to an external data source without physically bringing data across. This creates a pointer to the external file and can be viewed as if it were an Access Table. The added benefit here is that data can be maintained by external processes and requiring no access to your database application which helps with security and general manitenance challenges.
In Access, there are various import or link file formats available some of which are listed below:
- Any database created with Microsoft Jet engine (Access database)
- ODBC (Object Database Connectivity) databases such as Microsoft SQL Server, Oracle and bespoke database applications.
- Excel spreadsheets (any version).
- Lotus spreadsheets (most versions).
- Delimited and fixed length text files (in a tabular format).
- Tabular data in HTML (Hypertext-Markup Language) file including XML.
There are other formats that can be used for importing data into Access and can be downloaded and you will need to seek other online references for more information.
With all versions of Access, you have a standard tool to either import or link a table which starts a dedicated wizard tool. So for example, the Excel file format option is chosen, it starts the Import Spreadsheet Wizard tool. You simply follow the prompts previewing the data and complete the steps.
The difference between the versions of Microsoft Access is where you navigate to find the commands. Upto version 2003, you used the database window via the Tables section to locate the New button. With later versions, it’s even easier by simply clicking on the External Data tab on the ribbon bar and make your icon choice.
For a perfect import routine to be clean and successful, you may need to understand some rules regarding how the data is prepared which can be found in my FREE eBook along with other useful techniques to help encapsulate your Access knowledge.