This is something I have never done, but an curious on how it would work. I
have been looking around here and on Google, but haven't quite found what I
am looking for. To be upfront my preference would be to just keep this
entire system I am going to discuss in Access, but NO ONE at my work besides
me knows how to use Access. So we end up with 50MB & 30MB Excel files that
contain one HUGE worksheet that is 47000+ lines of data.

So my question is if I made an overlay of Excel to Access is this something
that would reduce the size of the Excel files thus increasing performance? I
looked at the MS Query and can see how to get the basic data into Excel from
Access, but it seems that the Excel file is still large (ie the query's data
is imported not viewed)...

How can I just have Excel view the data in Access, but not store it into the
workbook?

Additionally, can someone give me direction on how to also use Excel to edit
the data in the Access Tables (via DAO preferably), do I just use SQL APPEND
or INSERT INTO? How does Access know what to do with edited cells?

I know this is a lot of stuff so I hope some of you have taken the time to
read down this far, thank you in advance for you assistance.

-Steven M. Britton

HI Steven,

To show Access data in Excel, use either of the menu commands DataImport
External Data, or the DataPivot Table.

You can change the options so that the data is not stored with the workbook,
but downloaded every time the workbook is opened. See my pivot table
tutorial at http://www.edferrero.com/tutorials.aspx for further information.

As for moving data from Excel to Access, look at these couple of resources
http://support.microsoft.com/kb/151511/en-us
http://archive.baarns.com/access/faq/ad_DAO.asp

Hope you all had a great Christmas, and Happy New Year to you.
Ed Ferrero
http://www.edferrero.com

This is something I have never done, but an curious on how it would work.
I
have been looking around here and on Google, but haven't quite found what
I
am looking for. To be upfront my preference would be to just keep this
entire system I am going to discuss in Access, but NO ONE at my work
besides
me knows how to use Access. So we end up with 50MB & 30MB Excel files
that
contain one HUGE worksheet that is 47000+ lines of data.

So my question is if I made an overlay of Excel to Access is this
something
that would reduce the size of the Excel files thus increasing performance?
I
looked at the MS Query and can see how to get the basic data into Excel
from
Access, but it seems that the Excel file is still large (ie the query's
data
is imported not viewed)...

How can I just have Excel view the data in Access, but not store it into
the
workbook?

Additionally, can someone give me direction on how to also use Excel to
edit
the data in the Access Tables (via DAO preferably), do I just use SQL
APPEND
or INSERT INTO? How does Access know what to do with edited cells?

I know this is a lot of stuff so I hope some of you have taken the time to
read down this far, thank you in advance for you assistance.

-Steven M. Britton