QUESTION: Can MS Access perform the calcs that are performed in excel? Does Access use formulas or is Access?

Question

QUESTION: Can MS Access perform the calcs that are performed in excel? Does Access use formulas or is Access merely a database with data residing in it and does not use formulas to perform calculations? If you do not know the answer, what course of action would you suggest I take? Many Thanks!!!

I have some excel files that are in the neighborhood of 280 meg and will grow perhaps to about 500 meg. There is no linking or even referencing. There are about 25k lines of data in 5 columns with nested if/then’s/and’s/or’s for each of these 25k lines going out pretty far into the double alphabet of column titles. (Note: I have larger files with far fewer calcs that open/save/close faster, and with no problems, than the smaller files.)

My problem is excel does not perform the routine copy/paste command when I copy a certain block of, say, 10 columns of cells with formulas for the 25k lines. For the 1st 1-3 copy/pastes excel tells me there are not enough resources to perform the command but gives me the opportunity to “Continue without undo?” to which I select “ok” and the copy/paste appears to execute correctly. However, the next time I try to copy/paste, excel tells me merely there are not enough resources to perform the command and I am NOT given the opportunity to “Continue without undo?”. Excel copies some of the lines but not all and then when I later try to open the file it tells me there is corrupted data.

My laptop has 2gig of ram and/or virtual memory. I even bought a new laptop with 4 gig of ram and/or virtual memory and I got the same results as described above.

Someone told me to use MS Access to accomplish my excel calcs. But my experience with Access is with tables and loading data to an accounting system.

QUESTION: Can MS Access perform the calcs that are performed in excel? Does Access use formulas or is Access merely a database with data residing in it and does not use formulas to perform calculations? If you do not know the answer, what course of action would you suggest I take? Many Thanks!!!

Answer 1

Access is a database application (small scale compared to the likes SQL Server/Oracle) and Excel is spreadsheet application. The confusion usually arises because both have spreadsheet like data entry screens. 1) Access stores data in tables that can be related to each other (relational database) while Excel uses flat spreadsheets 2) Access like other database apps can use the SQL language to query but excel has none 3) Access can store much more data than excel can hope that helps a bit? all the best on your test!

Answer 2

MS Access can do the calculations done by Excel and more by using Access Visual Basic which is easier to use than VB in Excel. Access will also allow you to weed out duplicate fields more easily. Using OLE you can import parts or an entire spreadsheet into Access in native Excel form or, preferably, convert it to an Access MDB database. You haven't said which version of Excel you are using. Excel 2003 will only use 1GB of memory for calculations etc. Sounds like you are hitting the ceiling of the amount of cells that your Excel can handle so conversion to Access is not an option, it's a necessity. Alternatively you could try a later version of Excel, e.g. Microsoft Office 2007 version which is able to handle bigger spreadsheets, number of calculations etc. but conversion to Access would still be desirable. Another alternative would be to import the spreadsheets into Crystal Reports and manipulate it that way.

Answer 3

Access does indeed use formulas & calculations. They are similar to, but not exactly the same as, Excel. For instance, where Excel uses IF, Access uses IIF. Where Excel uses TODAY(), Access uses Date(). Access has a very useful function ISNULL() parallel to Excel's ISEMPTY() function. Unlike Exel, you build your formulas into queries which you then run. I suspect that this would be quicker than Excel.



I am amazed at the size of your files. What kind of data are you keeping in there? Is there a lot of duplicated data? One advantage of Access is that you only need to enter each piece of data once, because each table can have multiple relationships with other tables. For instance, you would only enter each person's name once.



I have worked with both Excel and Access extensively. You can learn most of the functionality of Excel in a couple of weeks. Access takes years to learn, it's vastly more complex.