Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data vs Access database
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data vs Access database
Robert Crandal wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data vs Access database
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. -- Pete Cresswell |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data vs Access database
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP: VBA Code to extract data from an SQL Server database and put it into Access Database | Excel Programming | |||
Convert MS Excel data to MS Access database | Excel Worksheet Functions | |||
Extract Data from an Access Database in to Excel | Excel Programming | |||
Excel as Data Entry into Access Database | Excel Discussion (Misc queries) | |||
excel to get data from an access database | Excel Discussion (Misc queries) |