Excel to Access User form and Database (1 Viewer)

Mr Zeal

New member
Local time
Today, 23:39
Joined
Jul 2, 2020
Messages
1
Hi
I recently created an excel user form for a team of 7 people to use to input data into a single datasheet. They can update/edit/delete/add data using the form and update the main sheet directly from it. Unfortunately someone decided, at the last minute to advise that they would like to use the form at the same time so my excel user form doesnt work for them "read only etc" is there anything I can do to create some kind of connection from excel to access so that they can use the form at the same time and I dont have to re do the whole thing in access ( I have limited/no experience in access) I guess what I am thinking is something like a main form which is the master then several of the same sheet for each individual
 

Isaac

Lifelong Learner
Local time
Today, 16:39
Joined
Mar 14, 2017
Messages
8,738
First, it sounds like you may benefit from a simple Access database. Might be worth the learning. While there is a LOT of advice on how to do things really right in Access, you might be ok by just treating your first project as simply as possible - the whole purpose will be one form to put data in one table. Just get a feel for how it works. If you decide to go this route, be SURE to employ the "split" database model, no matter how simple it is. It's very easy to implement, and if you don't do this, you'll defeat the entire purpose of going to Access and maybe even make things worse.

Alternately, I've had a lot of success in deploying shared Excel workbooks on a network with userforms and automation. As long as you make sure that your code automatically SAVES the workbook before & after all data entries/updates, you can generally get away with it working "okay". One user's version Saving the workbook means that another user's version will "see" the most recent entries so the code can operate properly - as long as the second user also Saves prior to reading the data. (I can't remember at the moment if that second part is needed; I think so).

It's a lot of time spent Saving, so make sure your workbook is as small as possible, and above all, make it an .xlsb instead of ANY other file extension you may have it as now. General rule of thumb: Take any workbook of a significant size, re-save it as .xlsb, and you'll see the file size be reduced by around 40%. That rule holds true on most but not all workbooks. Once I discovered this handy fact, everything I did in Excel was xlsb. Time-to-Save reduced accordingly.

It's not perfect, and clearly a database would be a better option and perform better, but those are my thoughts on some options you have.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:39
Joined
Feb 19, 2013
Messages
16,553
why not just give each user their own copy of the excel file?

Then if required have code which updates the excel file from the database

edit: got the wrong end of the stiock - just realised the db doesn't exist

I developed a system a while back where users all over the world had excel files with code which a) updated a centrally located access file and b) could draw down data input by other users. Worked well but was generally agreed, excel does not make a good input method since the file has to be locked down tight to prevent illegal data entries (e.g. text in numeric/date fields) and prevent users making changes to the file such as adding or deleting columns.
 
Last edited:

Users who are viewing this thread

Top Bottom