Using Excel spreadsheet as a frontend for Access Database

I have several Excel spreadsheets that are used for entering data.  CUrrently all data is entered in the spreadsheets and the saved as a spreadsheet.  

SInce I need to do analysis of the data entered (other spreadsheets are used) and issue reports to c lients, I would prefer to put the data in a database.  I know I could recreate the spreadsheets as forms in the database and enter the data that way but the spreadsheets are quite complex and have many columns and lines.

Is it possible to use an Excel spreadsheet for the data collection then click a button and add a record(s) to the database in one or more tables?

What code would be used behind a save button?

LVL 101
Who is Participating?
kittenwhiskyConnect With a Mentor Commented:

This is to append excel data to table as new records.

As OxonDev suggested, you'll probably still need to handle updates of existing records, deleting records, and maybe amend the code in link above to disallow creation of duplicates in your access table.
OxonDevConnect With a Mentor Commented:
Yes.  You can create functions in Excel VBA.  For example you might export your data range to an Access table.  However you'd have to synchronise you spreadsheet records with your Access table records.  That is:
1. Only append  Excel records  not matched in your Access table.
2. Delete Access records not in your Excel records.
3. Update Access records which have been edited in Excel.

You might get away with this for one table, depending how complex your records are, but synchronising more than one dataset and your going to end up in trouble.

I wouldn't advise doing this.  After all Access can be more easily controlled and you can always use it as a readoinly datasource for the XLS file if you need to do statistical analysis or create distributable snapshots to users.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Personally I'd link the Excel worksheets into your Access database and build your reports in that database, using the "live" Excel data.

<off topic>

I would say use Crystal Reports, but I don't think you have the necessary skillset to do that. Maybe when you top 11 million points in the CR Zone, but not with a measly 10,900,000 (and change).


</off topic>
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

mlmccAuthor Commented:
The reports will probably be built in Crystal though I may use Access since the client only has CR7.

Each spreadsheet actually represents a single record.  To be able to update a record, I would need to read the data from the database.  Would it be essentially the reverse of the update process by assigning a data base field to a cell in the spreadsheet?


Just remember, to import an Excel spreadsheet into an Access table, each column must be the same datatype, except for the first row, which may be a string, to represent the field name after import.   The data in the columns has to make sense.  While you can import a spreadsheet where line 100 is the sum of the previous 98 lines, that line will not make any sense when viewed as a field in an Access table.  I think you have to very specific about what parts of a spreadsheet you import.  
mlmccAuthor Commented:
Good idea but the spreadsheet is really being used as a form.  For example, if this was a tax return program the spreadsheet would be the form 1040 complete with all text, lines, boxes, etc.

I can use the idea from kittenwhisky to get the data to the database.  I am trying to decide if it is easier to do the entire thing in the database with forms (recreate the spreadsheets as forms) or write the code to mvoe the data to the database.  The spreadsheets wont be saved but could be recreated from the database.

Looking at the code required to pass the data to the database, it seems to be a tossup as to which will require more effort.

All Courses

From novice to tech pro — start learning today.