Looping through external database

[GeeWhiz7]

Hi folks,
Currently I have a work related excel file (actually 4 copies as the DB grows) that has two parts:
1) A set of vba modules that loops through various arrays on multiple tabs to calculate and summarize results to a main dashboard. That works fine.
2) The raw data arrays from various departments that is getting stored on data tabs is what is used in the loop.

I guess I knew this day would come as more and more data is added where my using excel as the database for the data would bottleneck and slow things down as I’ve almost maxed out and my only solution has been to make more files that contain parts of the overall data. I get frequent hang-ups and crashes so I am wondering the following.

Id like to stick with vba to do computations but I would like to put the data into a database and have excel draw from that database. This is new territory for me, but I am wondering what the gurus out there would recommend as far as a database (SQL, access, other?) that can allow me to:

a) have fast access to grab about 5 columns at a time from the database into excel automated by vba that would then go through the computations from the existing vba code and spit out the results (already captured out to a word document via the code) before starting on the next 5 columns.

b) possibly push a column results back into the database into new columns . If this isn’t possible I can work around it.

I would need need to learn how to access the database from within vba but that is okay. I just have no idea what kind of database would be fastest and most able to have the whole process automated from vba especially since I want to keep things to excel/vba For the automation/computation.

Anybody have a setup they really like for a new user?
Any suggestions are greatly appreciated!

 

[Micron]

Access is quite capable but security is not bullet proof if that's a concern. You probably could secure it against just about all meddlers but chances are that a seasoned pro could get in and poke around. A version of sql server would be better for that but that is a whole different kettle of fish and would have a very significant learning curve IMO.

Your biggest mistake would be designing tables like you would design spreadsheets. In the Access forums we call that Excelitis as it really is a widespread condition. So the most important aspect would be to learn db normalization and be able to apply it. Second would be avoiding all the traps that M$ builds/allows in Access that will (not might) keep you posting in Access forums. Certain features built into Access would prevent you from ever upsizing to sql server if you ever needed to. I think most often, that becomes a requirement when records start approaching the millions in tables. Access has a 2 GB size limit, which is quite huge.

You'd do well to augment normalization principles with tutorials/books/web pages - and there are a gajillion of those. Note that my answer is based on dumping Excel altogether, unless you need robust charting, pivot tables and data analysis that Excel provides. It's great for that, but IMO it will never make a good db tool. It's like driving in screws with a hammer. You can do it, but it kind of defeats the purpose of what a screw does.

 

[GeeWhiz7]

Access is quite capable but security is not bullet proof if that's a concern. You probably could secure it against just about all meddlers but chances are that a seasoned pro could get in and poke around. A version of sql server would be better for that but that is a whole different kettle of fish and would have a very significant learning curve IMO.

Your biggest mistake would be designing tables like you would design spreadsheets. In the Access forums we call that Excelitis as it really is a widespread condition. So the most important aspect would be to learn db normalization and be able to apply it. Second would be avoiding all the traps that M$ builds/allows in Access that will (not might) keep you posting in Access forums. Certain features built into Access would prevent you from ever upsizing to sql server if you ever needed to. I think most often, that becomes a requirement when records start approaching the millions in tables. Access has a 2 GB size limit, which is quite huge.

You'd do well to augment normalization principles with tutorials/books/web pages - and there are a gajillion of those. Note that my answer is based on dumping Excel altogether, unless you need robust charting, pivot tables and data analysis that Excel provides. It's great for that, but IMO it will never make a good db tool. It's like driving in screws with a hammer. You can do it, but it kind of defeats the purpose of what a screw does.

Thank you Micron. I guess I will endeavor to do this via access And will look for some literature to help. I will still need excel for the computational part and the analysis but hopefully I can use both excel and access.

 

[Micron]

If going the Access route, I recommend these subjects as a start:
Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields - Mendip Data Systems