When it comes to working with external data, Excel files are among the most common files of external data that you work with in Access, so the new Access capability to specify the data types as you are importing the data is a great benefit and time saver.
Importing and exporting to Excel follows the standard process that you are familiar with for text files. The wizard walks through the steps and provides options for specifying field types and even saving the specifications. This creates an ImportExportSpecification object that contains all the information necessary to repeat the process. An import specification contains the link to the source file, the name of the destination database, the table name, and if it is an append or create function, the primary key information, field names, and all of the details necessary to import the data.
Reusing a specification is fast and easy and avoids the potential for input errors. You can easily schedule these routine processes as Outlook tasks so that they occur on time. That's just one of several opportunities to integrate with Outlook; you'll see more in a moment. First you'll want to know that copy and paste is better than ever as a quick way to add records to a table in Access. You can copy and paste an entire spreadsheet into the datasheet view of a new table, and Access not only creates the fields, but is a lot smarter about field type recognition. You get as many rows and columns as you paste—no more being surprised by missing columns and rows, and you don't need to delete the initial blank rows to get started. Couple that with more design capabilities in Datasheet view, and you have a powerful combination that particularly appeals to power users.
Was this article helpful?