excel VBA programmer, what is the value of adding access for data

[AMDiesen]

I currently program using VBA in excel. All the input and output data
created is stored in csv files. The bulk of the inputs are price files
dating back 30 years and updated daily. This data is then manipulated in
countless ways and the output is stored in multiple files in multiple
directories. What would be the value of using access to store the input and
output files? Thank you for your time.

 

[trevosef]

I currently program using VBA in excel. All the input and output data
created is stored in csv files. The bulk of the inputs are price files
dating back 30 years and updated daily. This data is then manipulated in
countless ways and the output is stored in multiple files in multiple
directories. What would be the value of using access to store the input and
output files? Thank you for your time.

Hi AMDiesen,
Access is a relational database. It allows data to be linked (i.e.
can build data relationships) easily and have built-in data integrity
check. It comes with query tools to extract and manupilate the data
and reporting tools to generate print reports. I would use Access if I
have to create multi-dimensional queries and create multiple reports.
Provided I don't have too many complex calculations with the data. You
can easily create Data Input forms (similar to UserForms in Excel)
from the defined data tables and/or queries. Excel is a great tool
for calculations but rather limited (other than through VBA
programming) when it comes to extracting and handling multi-
dimensional data. However, Access is bundled with MS Office
professional.

Regards
trevosef

 

[Bob Phillips]

I disagree with a lot of what Trevor said. I don't like using Access forms
and reporting facilities, and Excel is anything but limited (pivot tables,
charts, conditional formatting, data validation, etc., etc?)

What Access does give you is a relational database, which can be very useful
in helping to describe the data and the relationships of that data. You can
actually create Access database even if you don't have Access, as Access is
really two products, the database and the Access reporting and data entry
facility. Creating an Access database, you can then use Excel to analyse
that data.

The advantage over CSV files? All the data in one place. Greater cross file
analysis. Data integrity. And so on.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Nigel]

I only ever use Access as data storage for any Excel applications that
require a large set of data to be accessed or created. I used to use CSVs
but they became difficult to maintain, especially where the application had
multiple data tables to store. The benefit over CSVs is that MDB files have
the data structures, relationships and data integrity. My Excel application
creates the Access MDB then maintains the data adding rows, editing existing
data and so on, I can link tables and present the data back as a single
related set, which can be read back into Excel. The user does not need to
have Access (the application) installed, however I can open the MDB using
Access and take a look at the data etc. independently which also overcomes
the 65k row limit (pre Excel 2007) and not having to shift the data into
Excel first.

 

[AMDiesen]

Thanks Nigel, Bob, Trevosef