Excel and Access together question ?

[scott23]

I have a workbook that is very large with a huge number of formulas.
I also find myself needing a relational database... so im trying to
figure out how to better the performance of my workbook by using
access as the database.

So far ive created a database file and imported all of my data into
access.
I guess my next question is :

How do you use all of my existing formulas within my excel workbook
with the data that will be stored in access ? I want to make sure to
increase my performance so i assuming i only want to quesry access
rather than rewrite the table into excel.

Also, is there a way to use excel as the program for my data entry and
then have a macro export the new record into the db in access, and
ultimately be able to use excel to still calculate and show my
statistics/formulas / etc from the data in access ?

Am i barking up the right tree here ?
Thanks so much in advance.

Scott

 

[Frank Kabel]

Hi Scott
I would try the following 8just as a general direction):
- Use Access as your main data system
- Use MS access for all your data entries (using forms, data
validation, foreign keys, etc.)

----
For calculations / reporting / aggregations / charting you may use
Excel
- use 'Data - External Data' to import relevant parts of your Access
database to Excel
- use pivot tables/charts with MS Access as datasource
- For performance issues: just do a refresh of the 'linbked' Access
data on startup / or manual basis. that is: use a copy of your data in
Excel and refresh the source data only if needed