Control Access Database via Excel UserForm

Mabbie

New Member
Joined
Oct 12, 2014
Messages
3
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,788
Office Version
2019
Platform
Windows
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

New Member
Joined
Oct 12, 2014
Messages
3
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,241
Messages
5,510,013
Members
408,772
Latest member
Bmort

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top