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?

mlmcc
LVL 102
mlmccAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OxonDevCommented:
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.
0
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).

LOL

</off topic>
0
kittenwhiskyCommented:
http://www.exceltip.com/st/Export_data_from_Excel_to_Access_%28ADO%29_using_VBA_in_Microsoft_Excel/425.html

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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?

mlmcc

0
GRayLCommented:
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.  
0
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.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.