Download Sample Access File: MS Access Vs MS Excel

MS Access can be programmed to work with MS Excel by sending data from MS Access to Excel file and also can save data to Excel then send to another user via Outlook email. The communication between these two programs can be done manually or by using VBA.

There are two Access files in the download zip file.

1. Import from Excel without duplicate

2. MS Access Vs MS Excel

Access File #1: Import from Excel without duplicate

This file will provide a function for importing data from MS Excel to the existing table in MS Access. The column name in Excel must be matched and in the order for the name of fields in table. This file also displays the last ten records from the previously import.

The Import Data from Excel button is provided to open the dialog window to browse to the MS Excel file that you want to import data. Select MS Excel file and click OK to import.

If there is no new data or the data in MS Excel file is already in the Access table then the alert message will pop-up as shown below. This feature is preventing user from importing data from same Excel file twice.

If there is an error while importing the program will open the Error Found form that gives a message for the requirement of importing. This form can be removed or replaced with other message on the bottom section of the code under the import button.

This Download file contains:

2 tables, 3 query, and 3 Forms

Access File #2: MS Access Vs MS Excel

The data from MS Access can be transferred out to MS Excel file, and the other way around. There are 6 forms in this file that provide different ways of exporting and importing data between MS Access and MS Excel.

Form #1 - Attach Excel File to Outlook Email

This form provides a button to convert data from Access Query to MS Excel file that attach it to MS Outlook which is ready to send to recipient as shown below.

This is a datasheet from Access Query with three fields: customer_ID, CustomerName, and Address that will be transfer to MS Excel.

After click on the button above, it will send data from Access Query above to MS Excel file “OutputFromQuery.xlsx” and will open MS Outlook with Excel file attached for email. The Excel file will be deleted after sending Outlook email to recipient or closing the Outlook.

Form #2 - Export Using Docmd.OutputTo Method

There are three buttons on this form that show how to use the Docmd.OutputTo command to export data from table, query, and report to MS Excel file. The output will display differently in Excel file as shown below. This method will save data from Access to Excel to the specified file name and path without opening the MS Excel.

Example of exporting data from table to Excel: The OutputTo command will export data from every field in that table. This method will save all fields from table to MS Excel file a specified path. Every time we click on a button, it will export data and overwrite the existing data on that file (have same filename).

Example of exporting data from query to Excel: By using the query method, we can choose specific fields in the query and export them to Excel file. Per example below, I choose only three fields in query; customer_ID, customerName, and Address.

Example of exporting data from report to Excel file: The output command will export data from every field on report. The format on the Excel file will be similar to the report format on MS Access. This method is not popular for exporting to excel due to the format of report. Instead, exporting report to PDF is more popular to keep the same format on PDF file.

Form #3 - Export Using Docmd.Trasfer Method

There are three buttons on this form that show how to use the Docmd.TransferSpreadsheet command to export data from table, query, and report to MS Excel file.

Example of exporting data from table to Excel: This Transfer command will export data from every field in the table to MS Excel file. Every time we click the export button, it will export data to that file and overwrite the existing data on that file (have same filename).

Example of exporting data from SQL Statement: this transfer command will open a recordset from a SQL statement then export the data to Excel file. The example below is exporting data from SQL statement “Select * from tbl_customer where [state] = ‘CO’” There are only the CO State from customer table that exported to this Excel file.

Form #4 - Export Using Recordset method

This method is the most popular for exporting data from MS Access to MS Excel file. This form shows three different ways by exporting data from table, query, and from SQL to Excel file. We can format the outcome on Excel file by using this method. For instance, we can format a column name to “Bold” letter and make a column width to fit all data automatically. There is only one function that can be used for exporting data from table, query, and SQL. This method will open MS Excel file after exporting and is ready to save or print.

Example of exporting data from Table: the command will open a RecordSet of table then export all fields of each record by using a loop function. The example below is exporting all fields from a table customer. The export function includes a bold letter for column name row and autofit for column width.

Example of exporting data from Query: the command will open a recordset of query then export all records from the specified fields in the query. Per example below, I choose only three fields in query: customer_ID, customerName, and Address.

Example of exporting data from SQL Statement: The example below is exporting data from SQL statement “Select * from tbl_customer where [state] = ‘CA’” There are only the CA State from customer table that exported to this Excel file.

Form #5 - Fill in Excel Form

When you have a MS Excel form created you can pass the data from MS Access form directly to a cell name of MS Excel like A1, B1 or you can rename a cell by using the Define Name command for those cells in MS Excel to a specific name like Customer_ID, Customer_Name, or Address corresponding to the field name in your MS Access file.

Select a record from MS Access form and click on the “Fill in Excel” button to export data from this record to MS Excel form. It can export data from a Memo field in Access to MS Excel cell.

After click on Fill in Excel button, it will open MS Excel file with the data filled in shown in the picture below as specified code under the Event Procedure.

Form#6 - Import Using Docmd TransferSpreadsheet Method

There is only one button on this form that shows how to use the Docmd.TransferSpreadsheet command to import data from a MS Excel file to MS Access table. The example below shows the data from tbl_customer2 table that is imported from an Excel file “CustomerData for Import Test.xlsx” The column name in MS Excel file is converted into a field name of Access table.