Using excel as a backend? - A few Questions

[JGalletta]

1. Possible?
2. A good idea?
3. Easy?
4. How?
5. Other options?

The reason I'd like to do this is to provide a low budget solution for handling data that might otherwise reach the 2GB size limit of an access database.

 

[tehNellie]

1) yes - create a linked table to the sheet.

2) Depends. I tend to create linked tables to spreadsheets to import data from them.
I don't believe you can write data to a linked Excel spreadsheet and any alterations to the data(types) would need to be done in excel rather than Access. You also can't index a linked Excel sheet so it will offer worse performance than a native Access table when querying.

3) Yes, see 1

4) See 1

5) Split a back end Access Database, approaching it's 2GB limit, into two (or More) back end databases and link to them via a front end Access Database

 

[Kiwiman]

Howzit

2. Tried this in a previous role and had continual problem with shared wrokbook always corrupting when multiple users accessing. Quite possibly our knowledge on getting this to work properly.
5. Consider SQL Server as a back end option. IF you are approaching a 2 GB limit in Access likelihood it is also too big for an excel backend as well