Control Access Database via Excel UserForm

[Mabbie]

Hi,

I'm new, and I'm not sure if this has been asked before in this forum (if it's been inquired before, please direct me to the right forum/discussion thread) or if I even chose the right forum for this (kindly direct me to the right forum).

I have a project of reducing worksheets that we're using for various activities, by dumping them into one file. The initial idea is to use an Excel sheet for the job. The problem however is that we have more than 15 people who needs to use the file/tool simultaneously. And we've encountered a lot of issues with sharing an excel file (ex: the file is corrupted when the file is used simultaneously).

In our line of work, we access another computer remotely for crucial tasks. And that 2nd computer contains MS Access. Other teams in our office use it to track their tasks. But my team hasn't used it before (most of our members hadn't even heard about MS Access). The 2nd plan for my project was to create an MS Access database instead of an MS Excel file, since MS Access seems more built for file sharing.

Since we don't have MS Access in our primary computers, and most of our team members are not well-versed with MS Access and are more familiar with MS Excel navigation, I was wondering if it's possible for us to create an Excel Userform that can route data into an MS Access Database.

I've done some legwork about this, and I've read how it's more efficient to use MS Access for everything. I agree with that, but given the circumstances above, I'd still like to explore the idea of using MS Excel as a front-end, and MS Access as a back-end.

--Sorry if this is a mouthful, but I would greatly appreciate any urgent reply to this concern.

Thanks!

 

[Kyle123]

Yes, you can. It works surprisingly well, though you will obviously have trade offs.

You will take a performance hit by not using Access directly, though how noticeable this is depends on on your scenario
Development is more painful, controls in Access are designed differently than Excel to make them much easier to use when developing applications - Excel controls are pretty dumb. This can be overcome, but it requires you to have a better understanding of SQL and write more code
It is possible (and good practice) for users to have a client access version of the database on their machine - so the data stays central and the input is done locally. This does not require Access to be installed on those machines, only the one which contains the data. This is usually the best way to go since you get the best of both worlds.
More of an Access issue in general, concurrency may be an problem depending on your database design, although Access supports multiple concurrent users - supposedly a lot - in practice I've almost always run into issues with people updating the same table at the same time.

I'd advise you to build in access then deploy the client front end to all your users and skip Excel altogether. If you simply *must* use Excel, then skip Access and go straight to SQL Server - the express version is free and it makes development from Excel much easier (unless Access has started supporting Stored Procedures), it will also reduce concurrency errors

 

[alansidman]

Adding to Kyle's explanation, there is also the possibility to use Access Run Time. I am not sure how this would work with a split data base and multiple users, but it may be a viable option. Look at this link on user Run Time.

HiTech Coaching for Access Databases and more ...

The explanation is about 1/3 of the way down the page.

 

[Mabbie]

Thank you so much @Kyle123 and @alansidman!
That really helped me a lot with my project. We'll explore the idea of creating the tracker entirely in MS Access and distributing Run Time Access to the users instead of using an Excel Userform.