MS Access Vs MS Excel...

[CDNWolf]

Excuse my simple / uneducated question...

As I have never used MS Access can you use it like MS Excel in any way, formulas, lookups, etc..

I have been working on a quotation program with 500 rows per quote and I have been advised to use MS Access as it could facilitate my needs better than Excel...

Thank you in advance for your replies...

 

[tweedle]

Acces is an excellent large-data handler.
However, what you have in excel will not directly port to Access. They are different programs with different references to how data is stored, retrieved and calculated.
For instance, vlookup, Index and Match are functions of Excel you will not necessarily find in Access. Acccess does have functions like DLookup (which is also extended to Excel) and is useful for finding single rows in a table.
As far as applying any kind of Excel Userforms, expect to re-write them in Access.
As far as applying VBA code, still a re-write.
So if you're looking to port the program to Access and have immediate results, it won't really work like that.
I don't know what a 'quotation program' is (could be relative to verbiage quotes, could be a pricing quote generator), but either way, 500 rows really isn't that big of a deal for Excel.
Possibly looking at optimizing some of the formulas or functions might be better suited if performance is the issue.

Edit: They actually can work quite well as a pair. Access will import and export to Excel, and Excel can look into Acess and pull out information and generally has a 'better' presentation layer for pivot tables, graphing and the like.

 

[CDNWolf]

Hi Tweedle, thank for your reply,

My Excel file generates a quotation for a hose assembly using vlookups, macros, etc, etc.

The 500 lines / rows are per quotation so the limit of 65000ish lines in Excel 2003 limits the program to only 130 quotations till the Database is full...

As the current file in excel is working I will play with Access a bit and see how I go...

Thanks again for your comments.

 

[tweedle]

Access may indeed be a better long-term approach then.
May find some templates available: http://office.microsoft.com/en-us/templates/CT010142603.aspx#

Also, might could tweak the Excel to use your parts/pricing workbook to generate the quote to another job-centric workbook, but it sounds like you desire to retain your quote data all in one place maybe.

G/L, u know where the forum is

 

[alansidman]

Here is a good explanation

http://forums.aspfree.com/microsoft-access-help-18/access-vs-excel-349267.html

Alan