Microsoft Access Data Import/Export
Microsoft Access supports various scenarios of importing and exporting data, to and from MS Access databases, to and from text files, to and from spreadsheets, etc. Before importing, you should create a database that will receive the records.
The primary way to import or export is through the External Data property page of the Ribbon. You can also right-click any object in the Navigation Pane, position the mouse on either Import or Export and click the desired option.
Practical Learning: Introducing Data Import/Export
Importing a Microsoft Access Database
The simplest type of data to import into a Microsoft Access database is another MS Access database. To import a Microsoft Access database:
In the dialog box, if you know the path to the database including its name, type it. If not, click the Browse button to select the source database. Once you have specified the database, click OK. The Import Objects dialog box will display. The objects are organized by categories. Select the desired objects. Besides the objects, you can import or ignore such aspects as the relationships and other Navigation Pane options. To decide, click the Options button and select the desired options:
Once you are ready, click OK. If the objects in the database were valid (and they should be), you shouldn't have any problem.
Practical Learning: Importing a Microsoft Access Database
Exporting a Microsoft Access Database
There are various ways you can export the records of a Microsoft Access database. One way is to formally export a table.
To export an object:
In the first page of the wizard, if you know the path to the database including its name, type it or click the Browse button to select the target database. Once you have specified the database, click OK. This would display the Export dialog box. It allows you to specify the name of the object that will receive the records or the object that would be created. You can also specify what would be exported: only the structure of the table or both the structure and the records:
The above technique allows you to export one object, or one object at a time. If you want to export the whole database, on the Ribbon, click File and click Save As:
This window allows you to select the type of format the new database should have. The window provides eight options you can use. To use an option, either click it and click Save As or double-click it.
If you want to save the database in the current Access 2007-2016 format, select Access Database. This would open the Save As dialog box. Specify the name and location of the new database, and click Save.
If you want to create a Microsoft Access 97-2000 database, click Access 2000 Database. This would create a file with .mdb extension. These are databases compatible with Microsoft Access 97 and Microsoft Office Access 2000. There is nothing bad or wrong with those versions of databases and they are still valuable today. They just don't support some features such expressions, attachments, etc on a table:
In the MDB database versions, expressions cannot be created on tables but they can be created on other types of objects.
Microsoft Access and Spreadsheets
Importing a Worksheet
There are various ways you can import a spreadsheet into a Microsoft Access database. The spreadsheet can come from any application, including Microsoft Excel, Google Sheets, CSV, etc.
If you are planning to import a Microsoft Excel worksheet or a Google Sheet, you should first prepare it. One way is to make sure that the whole spreadsheet is organized as a single table. In this case, the top side of the spreadsheet should contain the categories that can be used/considered as column headers. No non-organized text (text that is not considered as belonging to the table) should display above the headers. The values can then display under each header. Here is an example:
No non-organized text (text that is not considered as belonging to the table) should display below the records).
Once the document is ready, to import it:
In the dialog box, specify the path and name of the spreadsheet and click OK. This would start a wizard. In the first page, select the name of the spreadsheet that contains the data.
Practical Learning: Importing a Microsoft Excel Spreadsheet
Importing a Named Spreadsheet
If the spreadsheet contains a mix of organized and non-organized sections or groups, you should create names for the necessary groups of records.
Practical Learning: Importing a Microsoft Excel Spreadsheet
Copying From a Spreadsheet
Instead of manually importing a spreadsheet, you can select the values in a spreadsheet, copy them, and paste them in a table in Microsoft Access.
Practical Learning: Copying From a Spreadsheet
Exporting to a Microsoft Excel Spreadsheet
to transfer data from Microsoft Access to a spreadsheet involves copying and pasting. To start, in Microsoft Access, you can open a table in Datasheet View then select one, some, or all records. After selecting the record(s), you can copy them, open Microsoft Excel, click the cell that would host the top-left value, and paste.
Another technique consists of exporting, and you can do it without first opening the table:
This would open the Export - Excel Spreadsheet dialog box with the path where the file will be saved. The default folder is the My Documents:
Practical Learning: Exporting Data as a Spreadsheet
Microsoft Access and Text Files
Importing a Text File
You can create a table using data from a text file. If you are creating the (text) file in Notepad, the delimitation of a field is usually done by pressing Tab after creating the fields' content. To import a text file in Microsoft Access:
Exporting a Text File
To save a table as text:
This would open the Export - Text File dialog box with the name of the file using the .txt extension. Simply follow the wizard:
Microsoft Access and XML
Importing an XML File
An XML file is essentially a document made of at least one table. A typical XML file starts with a root node:
<?xml version="1.0" standalone="yes"?>
Under it, the global parent node starts and closes itself:
<?xml version="1.0" standalone="yes"?> <FunFurniture> </FunFurniture>
This global parent node is not the table. Inside that node, you should have a node that would represent a table and it can repeat itself as many times as necessary. Here is an example:
<?xml version="1.0" standalone="yes"?> <FunFurniture> <Employees> </Employees> <Employees> </Employees> </FunFurniture>
Inside each table, you should have the name of each column followed by its value:
<?xml version="1.0" standalone="yes"?> <FunFurniture> <Employees> <EmployeeNumber>924795</EmployeeNumber> <FirstName>Donald</FirstName> <LastName>Tripleton</LastName> <Title>Sales Manager</Title> </Employees> <Employees> <EmployeeNumber>274957</EmployeeNumber> <FirstName>Jeanne</FirstName> <LastName>Wooley</LastName> <Title>Sales Associate</Title> </Employees> <Employees> <EmployeeNumber>684078</EmployeeNumber> <FirstName>Irene</FirstName> <LastName>Polsen</LastName> <Title>Sales Associate</Title> </Employees> <Employees> <EmployeeNumber>297149</EmployeeNumber> <FirstName>Monica</FirstName> <LastName>Jackson</LastName> <Title>Sales Associate</Title> </Employees> </FunFurniture>
After creating and saving an XML file, to import it in Microsoft Access, in the External Data tab of the Ribbon and in the Import section, click the XML File button and follow the wizard.
Exporting to XML
To export a table to XML format, in the Navigation Pane:
The Export - XML File dialog box that comes up allows you to verify the name of the file that will be created and the path where it will go. Once you are ready, you can click OK. A dialog box would come up asking which one(s) of the three files you want to have created:
If you want to create a more elaborate XML application with advanced options, you can click the More Options button. This would close the previous dialog box and open another one:
This dialog box allows you to specify more details on how the table should be exported. After making your selections, click OK.
Microsoft Access and the Web
Importing an HTML File
To import an HTML file in Microsoft Access:
The primary steps to follow are the same for a text document. In the third page of the wizard, to consider and/or apply more options, click the Advanced button:
After making the necessary adjustments, click OK.
Practical Learning: Importing a Text Document
A Microsoft Access Table on a Web Page
You can copy and paste Data from a webpage into a table. To do this:
Some other applications do not support copy and paste. An alternative is to export the table to HTML. To save a table as HTML, in the Navigation Pane:
This would open the Export - HTML Document dialog box.
Microsoft Access and Microsoft Word
There are various ways you can use Microsoft Word with a Microsoft Access database. As opposed to copying from a database table and pasting to a table, you may want the reverse. That is, you can copy a table from a Microsoft Word document. To do this, in Microsoft Word, select the table in a document and copy it. Start a table in Microsoft Access and paste the records.
Mail merging allows you to use data on your database to create letters, labels, envelopes, and other documents that require external data originating from another document. To start a mail merge, in the Navigation Pane:
This would open the Microsoft Word Mail Merge Wizard:
And click OK. This would open the Select Microsoft Word Document dialog box. It allows you to select the letter/document you plan to use. If you don't have that document, click Cancel, open Microsoft Word, create the document and proceed from there. If you have a letter, select it. This would open the letter in Microsoft Word. ail Merge window, if you want to create a letter to be sent out, accept the Letters radio button and click Next. In the second page of the wizard, you will have the choice of creating a new Microsoft Word document or using an existing document. If you want to create a new document, click or accept Use the Current Document. If you click Start From Existing Document, you will be asked to specify the document, in which case you should click Open, select the document, and click Open:
After this, the Mail Merge window would come back to the Use Current Document option. Click Next: Select Recipients.
To insert other types of items, in the Mail Merge window, you can click the Address Block link, the Insert Address Block dialog box would come up. The Insert Address Block dialog box is made of various sections. It allows you to specify a type of greeting and other pieces of information to be inserted in the document. After making the selection(s), you can click OK.
After creating the document and adding the necessary fields to it, you can preview and review it. To do this, in the Mail Merge window, click the Next: Preview Your Letters link. When you do this, the letter appears with the value(s) of the first record. To review the document with the other values, in the Mail Merge window, you can click the previous or the next buttons.
After reviewing the document, in the Mail Merge window, you can click the Next: Complete The Merge link. You can then save, print, and manage the document. In the same way, you can create labels or envelopes.
Practical Learning: Creating a Mail Merge
Sharing a Microsoft Access Database
Distributing the Database
Distributing a database consists of making it available to more than one computer. Both Microsoft Windows and Microsoft Access provide various solutions, One solution is to install Microsoft Access on each computer that will use your database. This may be the most expensive solution but it is the easiest. Most companies use that solution because it is easy either to purchase many copies or get many licenses of Microsoft Office Professional that includes Microsoft Access.
After creating a database, in the server or the computer of your choice (normally, the database can be located on any computer that is a member of your network) where you want the database to be located, create a folder. Copy the database file into that folder
Access the drive that holds that folder. To share it, right-click it and click Share:
Click the arrow of the combo box in the File Sharing window and select Everyone:
After selecting Everyone, click Add. Click the down-pointing arrow on the right side of Everyone:
Click Share. If you receive a message box, click Continue.
In another computer where you want to access the database,
open a file utility such as Windows Explorer. Click Network. Click the name of
the computer to show its shared folders. Open the folder that contains the database. Right-click the name of the database, position the mouse on
Send To, and click Desktop (Create Shortcut)
As an alternative, you could right-click the database and click Create Shortcut, then copy that shortcut where you want the database to be accessible. To open the database from that computer, double-click the shortcut.
Importing or Splitting a Database
Importing data allows you to get information from an external source and insert it in your database. Microsoft Access can accept data from various applications. To import a file or objects, on the Ribbon, click the External Data tab:
In the Import section of the Ribbon, click the button that corresponds to the type of object you want to get. What happens depends on the type of file you are trying to import.
Importing Microsoft Access Objects
To import objects from another Microsoft Access database, on the Ribbon, click External Data. In the Import section of the Ribbon, click the Access button . This would open the Get External Data - Access Database wizard. You should first locate the folder that contains the database, and then select the database.
You can divide your database in two parts. One part would be installed on one computer that can be a server or just another computer. This is referred to as back-end. The other part would be installed in each user's computer. The users computers can then connect to the back-end database that stores the actual database. The users computer have only the graphical interface that allows them to work. To implement this scenario, you split your database.
To split a database, on the Ribbon, click Database Tools:
In the Move Data section, click the Access Database button . This would open the Database Splitter dialog box with four paragraphs and two buttons:
After reading them, click Split Database and follow the wizard.
Practical Learning: Splitting a Database