Linking an Access database and an Excel worksheet

[Thirith]

I'm currently working on a database we might use to manage funding proposals. We've got two groups of people working on the proposals: scientific staff mostly deal with individual proposals, doing eligibility checks, evaluations etc., while admin staff have more of a need to look at the entire list, sort it by date, type etc. We used to do all of this in an Excel worksheet, but there are a number of reasons why Access might be the better option at this point, not least the number of proposals we've already handled.

However, I'm finding out that Access' datasheet view is much less flexible than Excel in terms of how the data is presented, from layout issues to the range of options you get with conditional formatting. Since admin buy-in is essential with respect to whether we'll start using Access or not, I've looked into this and found out that you can create a dynamic link between an Access database and an Excel worksheet, which in theory sounds perfect: we could build the whole thing in Access, use bespoke forms for the scientific staff, while the admin guys could use Excel for their tasks.

I do have a couple of questions about this, though:

• How well/reliable is this kind of dynamic link?
• How easy is it to implement?
• Would it be possible for admin to work on the worksheet while others work on individual records on Access?
• What are the kind of things we'd have to keep in mind if we went this way? What can easily go wrong, what doesn't work or works differently from what we're used to?

Any information would be very useful to me and much appreciated.

 

[ranman256]

Access IS the better option.
you would stop using excel. Each user would get a copy of the Front End app, and the Backend tables would sit on a network so all can access them.
All can access the data at the same time.
Once you learn Access methods and stop thinking in Excel terms, youll see what a great product it is.

 

[Thirith]

Thanks. I see what you're saying, but a) the decision isn't mine alone and b) as far as I can see, Access' datasheet view has a number of shortcomings compared to the options Excel offers. That's why I'm looking for more concrete information on what exactly you get if you link an Access database and an Excel worksheet. The very general information I've found makes it sound like you can more or less use Excel as a more flexible datasheet-style front end, but I'm sure that's only partly the truth. Hence my more specific questions.

Edit: Basically I'm asking about the functionality mentioned here: https://support.office.com/en-us/ar...0c-4bec-40ce-8cdf-fb4edb723525#ID0EADAAA=2016

 

[xenou]

The flexibility you want from Excel is just the thing that will be difficult to manage. It's not really a very good front end for Access generally speaking, especially if you don't know a lot about both Access *and* Excel.

What Excel can do well is pull data from Access. Then you can crunch the numbers in Excel.

 

[UncleCatwoman]

One thing that Access does a whole lot better than Excel is multi-user access. One person in Excel usually makes the file ReadOnly for everyone else.

If the admins are only using Excel for reporting (which it sounds like from your description), your best bet is to use Access but build an Excel file for reporting and a process in Access to open the Excel file and refresh the data in it so they can do whatever they want with it.