Excel vs Access VBA

[grichey]

Hello folks,
I have a project that I think access would be better suited to due to the volume of data. I have never used vba in access. Do any of you excel monkeys have any experience w/ access? Is it about the same or very different or.. ? I'm just trying to evaluate the length of time said project will take and how off the beaten path for me it will be.

Thanks

[Oorang]

Syntax = Easy. Knowing what you don't know about database concepts... Tricky:) VBA in general is a pretty easy language you just have to take the time to learn a new object model. I tend to use access to store and display data, and well as providing an input interface but will find that for very serious data analysis you will end up exporting your reports to excel anyway.

To me the time to go to access is when you have data that cannot be stored in a flat file anymore. Once the data has become relational it's time to put it in a DB.

I'd recommend reading these:
http://mvps.org/access/tencommandments.htm
http://en.wikipedia.org/wiki/Database_normalization
http://support.microsoft.com/kb/207793

[grichey]

The goal of the project is basically taking 3 gigantic lists of information and compiling them into one as they all share a unique identifier.

ex. list1 has name and #, list2 has address and #, list3 has title and #.

Each list is probably around a million or 2 rows. There is now limits at all on size in access if I remember correctly.

[Oorang]

Yah, I'd say that's a clearcut job for a database. There a few limitations to Access, but the only one you might be likely to encounter is the 2GB limit. But even then you can work around that by linking to the files instead of importing them.

Limits are here: http://www.databasedev.co.uk/access_specifications.html

Another possibility to consider is SQL Server Express. It's free and shouldn't have any issues with size:
http://www.microsoft.com/sql/editions/express/default.mspx

It limitations are in the terabytes:
http://technet.microsoft.com/en-us/library/ms143432.aspx

[gwkenny]

Can't remember any limits that I've run into, but after a certain size, just about everybody I know migrates their "access" database to sql, oracle, etc...

Seems there's a ceiling at which point Access begins to slow (don't know about 2007). For me, I guess if the database is going to get anywhere near 30mbs +, I get someone to move it to another platform (usually sql).

The hard part about doing the vb is just learning a knew app object model. As Oorang pointed Aaron pointed out, it's the concepts that's the hard part. Sounds like your DB is pretty simple so it doesn't sound like a problem. But for large db's, a good architect is really worth their pay.

Time? This is your first foray into Access? It's tough to know how long it will take you. Think about how long it would take in Excel, then multiply it by 4 would be my rough guess. It all depends on the learning curve.

Personally, I really recommend going ahead and doing it in Access. It's definitely worthwhile knowing and you've got a "simple" project to learn the concepts of database work. It can allow you to add significant value to your clients. Personally I find db work boring, but knowing the concepts have allowed me to critique db jobs intelligently and positively participate in meetings.

[grichey]

Time? This is your first foray into Access? It's tough to know how long it will take you. Think about how long it would take in Excel, then multiply it by 4 would be my rough guess. It all depends on the learning curve.


Nah this isn't at all my first foray. I've built loads of databases in access. I've just never done any vba.

I will look at the sql express links.

Thanks!

[gwkenny]

"I've built loads of databases in access. I've just never done any vba [in Access]."

Wow. Definitely a better man than I!

This'll be a slam dunk.