software

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 amp; 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 Datagt;Import
External Data, or the Datagt;Pivot 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 www.edferrero.com/tutorials.aspx for further information.

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

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

gt; This is something I have never done, but an curious on how it would work.
gt; I
gt; have been looking around here and on Google, but haven't quite found what
gt; I
gt; am looking for. To be upfront my preference would be to just keep this
gt; entire system I am going to discuss in Access, but NO ONE at my work
gt; besides
gt; me knows how to use Access. So we end up with 50MB amp; 30MB Excel files
gt; that
gt; contain one HUGE worksheet that is 47000 lines of data.
gt;
gt; So my question is if I made an overlay of Excel to Access is this
gt; something
gt; that would reduce the size of the Excel files thus increasing performance?
gt; I
gt; looked at the MS Query and can see how to get the basic data into Excel
gt; from
gt; Access, but it seems that the Excel file is still large (ie the query's
gt; data
gt; is imported not viewed)...
gt;
gt; How can I just have Excel view the data in Access, but not store it into
gt; the
gt; workbook?
gt;
gt; Additionally, can someone give me direction on how to also use Excel to
gt; edit
gt; the data in the Access Tables (via DAO preferably), do I just use SQL
gt; APPEND
gt; or INSERT INTO? How does Access know what to do with edited cells?
gt;
gt; I know this is a lot of stuff so I hope some of you have taken the time to
gt; read down this far, thank you in advance for you assistance.
gt;
gt; -Steven M. Britton