MS Access Vs MS Excel

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.

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.

select file

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.

no new data

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.

sample field and data

This Download file contains:

2 tables, 3 query, and 3 Forms

table for no duplicate import from excel

 

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

Form #2 - Export Using Docmd.OutputTo Method

Form #3 - Export Using Docmd.Trasfer Method

Form #4 - Export Using Recordset method

Form #5 - Fill in Excel Form

Form#6 - Import Using Docmd TransferSpreadsheet Method

Download file contains:

2 tables, 1 query, 6 Forms, 1 Reports, and 1 Module

table list