Excel data vs Access database

[Robert Crandal]

Each employee records their weekly work data in an
Excel workbook. These workbooks contain hidden
VBA code and uses Forms and buttons, etc.
(BTW, each workbook is a minimum of 260K+ in size.)

If I need to check past data, I simply navigate through
the file and folder system and find the correct .xlsm
workbook file(s) and peek at the data that I'm interested in.

The above method seems ineffecient to me. Does anyone
think it would be better to use an Access database for this?

I thought about creating easy-to-use input forms in Access,
then our employees can store everything in a single database
file. Someone told me that the Access database file would
be too big for this. I'm not sure if that's true, especially
if I create new database files for each new year, for
example db2013.accdb, db2014.accdb, etc. etc...

Does anyone have an opinion about this? Do you think
the Access approach would have smaller yearly data files?
Is one approach more efficient than the other?

Id' appreciate any thoughts here. Thank you.

 

[witek]

Each employee records their weekly work data in an
Excel workbook. These workbooks contain hidden
VBA code and uses Forms and buttons, etc.
(BTW, each workbook is a minimum of 260K+ in size.)

If I need to check past data, I simply navigate through
the file and folder system and find the correct .xlsm
workbook file(s) and peek at the data that I'm interested in.

The above method seems ineffecient to me. Does anyone
think it would be better to use an Access database for this?

I thought about creating easy-to-use input forms in Access,
then our employees can store everything in a single database
file. Someone told me that the Access database file would
be too big for this. I'm not sure if that's true, especially
if I create new database files for each new year, for
example db2013.accdb, db2014.accdb, etc. etc...

Does anyone have an opinion about this? Do you think
the Access approach would have smaller yearly data files?
Is one approach more efficient than the other?

Id' appreciate any thoughts here. Thank you.

access is not best solution.
install SQL Express and add UI to that.
it can be anything, excel, access with linked tables, and the best .net app.
for output you can still use excel linked to sql

 

[(PeteCresswell)]

Per Robert Crandal:

Does anyone have an opinion about this? Do you think
the Access approach would have smaller yearly data files?
Is one approach more efficient than the other?

Beyond a certain point, Excel spreadsheets get out of hand IMHO. As you
noted, hidden code is one downside.

OTOH, you have the spreadsheets in place and they're working. Developing
an Access app to replace them is going to take man hours.

OTOOH, the searching for historical data sounds like a minor nightmare
to me.

Personally, I'd do it if I could.

Single Access back end on a file server (or an SQL database), distribute
the Access front end to each user's PC.

If somebody wants the data in spreadsheet format, write Access VBA to
create a spreadsheet on demand.

 

[tskogstrom]

Inefficient for you or the staff?
Could sharepoint tables be used instead, or SP excel service? But this also depends on how tricky formulas, vba functions, etc you might use. SP tables have formula limitations, and might need some code to make it work.

Access demands all users to have the license for it.