MS Access - How to split your database and allow multiple users to enter data at the same time
HTML-код
- Опубликовано: 27 окт 2022
- This tutorial will show you how to split your Microsoft Access database so multiple users can enter data at the same time.
By splitting the database, you can create multiple files, also called front end databases, that will all act as a tunnels to your one central data storage location, also called a backend database.
Creating a front end database will also allow you to accommodate preferences for different users. Some users may want certain reports, queries, and forms that are specific to them, whereas others may not.
You are the man! Fine example of how tutorial videos should be, clear, concise, and no unnecessary gibberish. Thank you!
Thank you! This comment made my day. I appreciate it!
Thank you sooooooo much , GOD BLESS you
You bet. Thanks for watching!!!
Excellent thanks a lot Sir 🇵🇰
Thank you!
in case i have autoNumber column in my database in this case if i have multiple users to record the data "front database", which number appear first in the auto number columns for each of them
I hope I am understanding your question. But the number that appears in your autonumber field should appear in the order the record was entered. So if you entered a new record first, “4” might appear, but if I entered a new record from my front end database 20 seconds later, “5” would appear.
Sometimes it is helpful to track which users made changes and when. I have a video about this that tracks all changes in a form: ruclips.net/video/u2zed8eh19I/видео.htmlsi=7ySxxZ2HpVVicAgj
Hope this help.
Have a question here. I have a database that is into lead management. There are three users who needs to enter data into the database. But they work from different computers. Do we need to have a network drive on which the backend will be there. Alternatively can we have the access database on the cloud?
Hi there, the network connection or LAN is probably the most common. That’s a safe one. You could also move your backend to something like SQL Server and but use MS Access as the front end.
You could potentially host the Access database in a cloud environment, such as on Azure or AWS, using a Virtual Machine. Users can connect to the database using a VPN or other secure connection methods.
This can be combined with Remote Desktop Services for better performance and security.
The local network is probably the safest way to go, but I think any would work. I would avoid anything like Dropbox though for sure as those kinds of applications cause issues for MS Access. Hope this helps!
@@TLDW_Tutorials Thank you very much it helps
i'm using access as a front end for an SQL Server database. Do i need to split the linked tables like in this video to avoid data corruption? or would that be duplicating a step?
That's a very good question - in general, splitting linked tables in Microsoft Access is not necessary to avoid data corruption when using SQL Server as the back end. You should be fine!
@@TLDW_Tutorials thanks
Question: You created a new field called "Favorite Team" on the back end data base, however that field was not visible on the Front End data base. Please explain. Thank -you
Good question! I wanted to demonstrate how to add a new field in the back end, but I wanted to keep the video brief (which is part of the reason I started the channel) so I didn't spend the time adding it to the form. The second you add a field to the back end table and save it, you can then go into your front end databases and add it to your form(s). However, it won't be automatically added to your form(s) in your front end database. Your tables in your front end database are literally a link to the actual tables in your back end database.
To make the new field appear in the form, you'd open the front end database, then your form in Design View -> Form Design -> Add Existing Fields -> [select] "Favorite Team". Hope this helps!
@@TLDW_Tutorials Thank you...this makes sense now. I wish Access would make it visible once the field was added to the back end database.
@@excelemployeeleavetracker1274 I'm with you. It would make things a lot easier.
Can you tell me how this would work on a network drive and what happens to the VBA code i added? Will it go to the other people as well?
Nothing too different will happen on a network drive really, you'll still want to make copies of your front end databases to other people. Just to be clear, the file you created (the one that has "be" added to it by default) is your backend database and you don't want to make copies of that. But your front end database, once you add VBA code to it, just save it and then make a copy of it for other users and it'll have your VBA code in it. During the initial process when you "split" or create a frontend/backend database, the VBA code will stay in your frontend database, so you'd just need to make additional copies for other people. Hope this helps!
Hello, let's say if I put the backend on a shared network folder and make copies of the front end. Will it still work if I copy the front end from the shared folder into another PC in the same network?
Say, I created the backend on PC1's shared folder and made copies of the frontend too.. Then in PC2, I cut then pasted a copy of the frontend from the shared folder.
Good question. You should be fine as long as nothing changes with your access. I did this same exact thing at my old job. Just make sure to copy and paste though, cutting would essentially just move the file not copy it.
Anyway, you should be fine!
@@TLDW_Tutorials Thank you! Will try this.. I'm new to creating db using access, your video is a huge help for me. Easy sub.
@@mastergogoy6166 No problem. So grateful for your sub. Thanks a bunch! Feel free to reach out again in the future if you have questions.
Dear... thanks for your sharing ... but what if we need to open these splits from other computers in local network? ... regards
Great question. That shouldn't be an issue. Let's say you make 3 splits (also known as front end databases) and you have 3 users on your local network. As long as they use their own front end, you shouldn't have any issues. Even if more than 1 person opens the same front end database by accident, it isn't the end of the world. Your data will still likely be fine. There are ways to prevent this - for example, you could password protect each individual front end database or you could write VBA code to only allow certain users by their Windows login name.
Hi Sir nice job! I just have some questions. If all users operate the database at the same time and 1 user added a record to a form, will the other users able to see it in real-time or should they push a refresh command or something, or maybe restart their database just to update their database? Lastly, if the original database happens to have a login form with password, and then I created a multiple users database, will they be able to use the same password on their database because I happen to search from another tutorial that password will be gone after making another user and u have to redo the login form. thank you for your time answering:)
Thank you! Great questions. First, in terms of real time updates, you likely won't see literal real time changes, as in typing, but data should update pretty quick. Think of it more like a website updating. I suppose there may be ways with VBA code to see literal real time changes, perhaps on a on_change event, but that could potentially lead to problems. Now if you and another user are in the same record and one of you updates it, the other person will be notified that the record has been updated. I think a refresh (or requery, depending on your setup) should work just fine. A lot of times, things just update when you go to the next record.
In terms of the password, I think that is generally true. One way around this is to just us a database password: File -> Info -> Encrypt with Password. I guess another way is potentially granting form access by someone's Windows log-in name in the form... This is just for starters... I will think about this more.
@@TLDW_Tutorials i see now this answers my worries, thank u so much sir and for answering swiftly, greatly appreciated!👍👍
@@ryanm.8282 you bet, happy to help!
hi sir, wanna ask u something.. nowadays local network been disable, can this backend and front end be share on sharepoint?
Thanks for watching. Yep, you certainly can. I don't have any videos yet for how to do it (maybe in the future), but the one below should help you. It's not too bad though once you see how it's done. Here's a link to a video:ruclips.net/video/b2bb_3Wg80g/видео.html&pp=ygUUTVMgQWNjZXNzIFNoYXJlUG9pbnQ%3D
Question : I split my access database and put it in my shared folder on a computer in my home network for use via wifi with other frontends on different machines. I have a vb6 application that uses this database. All apps sitting on my windows 11 computers have become terribly slow but on my windows 7 computer the speed hasn't dropped much. Is there a reason for this ? thanks.
Oh no! It sounds like an issue likely outside of Microsoft Access, but it might be a few things if I had to guess: (1) Windows 11 has more running in the background (maybe try disabling some of these on startup), (2) somewhat related I wonder if it’s a memory bandwidth issue that could be resolved through some hardware upgrades, (3) did this happen after a device driver or Windows update? Also a possibility. (4) Windows 11 also uses more bandwidth with things like animation. I doubt it’s the issue but you could try going to settings and disabling animation effects. (5) I don’t know your setup but could it be a Wifi issue that could be resolved with a wireless expander?
If none of these work, I just go to stackoverflow.com and post the same issue.
Hope this gets resolved!
@@TLDW_Tutorials just to clarify - it is the application itself that has become too slow to use and not the computer itself. So slow to the point I am contemplating maybe using sql server as backend (but this is a different thing altogether that I have no idea on how to go about doing). On the windows 7 machine the application isn't too bad to use actually so I'm so confused as to why this is. Maybe I can also post on the other site you mentioned. Thanks.
@@prochefpos ahh, ok, that make sense. I’m a little puzzled why that’s the case. Try stackoverflow, 99% of my questions I’ve had are answered within a day.
@@TLDW_Tutorials thanks again for the quick response.
@@prochefpos You bet!
what if you want add data to the database from a different computer?
That’s essentially what this will allow you to do. All you have to do is make sure you have access to the same folder and MS Access front end database and you should be good.
I have copied couple of them and distributed to two people ( two different systems in my organization) through email after they download they are unable to access that and enter details. Did I do any thing wrong are we need to send the database copy too ?
Try just telling them to go to the folder and open the front end that way. If they can get to the folder but can’t open the file or can’t enter data, they may only have read only access to the folder. Give them full read and write privileges and see if that helps.
@@TLDW_Tutorials ok! Just clear this I am new to access. I have created access data base on my system with tables and forms and now I have created couple of front ends by using spilt to my friends. I want to distribute this two front ends to my friend’s they are not in my organization. How to do that? I have database and couple of frontends on my system. Now I need to share that front ends to my friends systems? If I have shared through email after they download they are unable to access it is showing the path is wrong and issue with server ? How to do that we are not connected through any server. Can you help ?
@@anudeepreddynare4044 Thank you for clarifying. So MS Access does not natively support web-based backends (i.e., one that people outside of your organization can use with you). However, you can migrate your Access database to a web-based database system, such as Microsoft SQL Server or MySQL, which can serve as the backend for your application. Basically, you'd send your data over to SQL Server or a MySQL database and make that your backend and you can still use Microsoft Access as your front end. It's a bit complicated though, especially if you are new to database development. Here's a video that covers how to do that: ruclips.net/video/jZm3KtjG9x8/видео.html
If that's too hard or not possible, you may have to email the MS Access tables and create a system to prevent duplication of efforts/data.
Unfortunately, MS Access does have its limitations when it comes to the web, but usually you can find a workaround.
How i distribute the front end copy to different computer user?
You can just copy the frontend database file and paste it (and then rename it) to make a copy. I demonstrate this at 2:41. Hope this helps!
Hi guys ,i have problem with acess database ,he is created a few year ago (2) and multiple user can use them on sharefolder ,but now when 2 user on same time want to acess give message dont have exlusive right to acess .If any know what is answer please reply ,thanks 😊
Hey there! Thanks for checking out the video. it sounds like 2 people are opening the same front end database. Once you split your database, you will want to make another copy of the MS Access front end database (i.e., your .accdb file). In other words, if you have 3 users, you should have 3 copies and make sure people use their own copies. All of your data will still end up in your backend database, don't worry. Think of it as 3 data pipes that all lead to the same central place.
Try what I said above and hopefully it should do it. Hopefully this helps, if not, let me know and I'll find a way to help you further.
@@TLDW_Tutorials it’s okay but it is local access database and its it is put on shared folder ,it’s work properly with multiple users ,but now give message…
@@nikolaradovic1726 Hmm, ok. If someone else is in a local database while you are, you could still technically use it, but if you tried to create a new table or query while they are still in there, you could get this message. It could also happen if someone else leaves a table, form or query open and forgets to close MS Access before they log off. I've also had people log off without closing the database and it becomes stuck open. To fix this, they had to log back in, reopen their database, and then close it again.
Try to see if any users that use the database can go into it and then close it.
If that doesn't work, this may: support.microsoft.com/en-us/topic/-you-do-not-have-exclusive-access-to-the-database-error-in-access-5da26550-a74d-6d88-f242-dd97d6bb1719
Question: At the office, there is an Access database on a network folder, but when one user on one computer updates a record, it doesn't show for the other user, and vice versa. Will splitting the database, as you show here, solve this problem? Thanks!
Absolutely! Think of the front end system like multiple pipes that all lead to your central data source (your back end database). That will solve the problem though for sure.
@@TLDW_Tutorials Wow, thanks so much for your quick response. Much appreciated!
@@kenotube3160 happy to help!
@@TLDW_Tutorials Hi again. I split the database and created two front ends, one for each user. There was still an issue with sharing, where one user could not see record changes the other user made. But we are now using Egnyte network drives for storing files and data in the cloud. I read that Access was designed for LANs and may not work probably with WANs. Can you think of a workaround? Thanks again.
@@kenotube3160Hi there. No worries! I’ve used it on both a WAN and LAN so I know it’s possible. I just got back from out of town but let me look into this a little tonight and respond back in the morning. I’m on New York’s time zone so I’ll try to get back to you early in the morning. Hopefully we can work this out.
How to distribute the front end and not lose the connection to the back end?
While you are making a copy of the front end, you won’t lose the connection to the back end. The front end database is really just a bunch of links to the backend database, therefore when you make a copy of the the front end database the connection you won’t change. Again, all you have to do is just copy the front end database and paste it like in the video - 2:43.
How can Ms access be link to MySQL online server?
That's a very good question that comes up a lot. In short, you will need to go to External Data -> ODBC Database. It can be a little tricky though. This video covers the entire process pretty well: ruclips.net/video/zu-8T4ERPN8/видео.html
DO you have to have anyting else besides 2 computers? like a server? or a modem? or anything?
Not at all. In fact, you could create a of backend and a few frontend databases on one computer you use at home if different people in your house wanted their own version.
So don't worry, nothing special needed!
@@TLDW_Tutorials its for our office its a small town food pantry and money is ectrimly tight so cant hire anyone can you tell me or give me a playlist to watch so I can understand how to do this mysef? I already have the database made just making a few improvments thank you
@@TLDW_Tutorials can this be done without always being online? I think we would need to be online some or can it be not at all? I need it not to have to deal with internet if at all possable
@@driftingforward You could set up a local area network potentially without an internet connection which would allow this to work. A quick search on RUclips should find a tutorial (I don’t have one on my channel).
But no you don’t need to be online all the time for this. I guess it all depends on the other people that use the database too. Are they always and only using it in the office?
@@TLDW_Tutorials We plan to use in same office, but in a very few days of the yr it will go home with one for snow days in order to reschudule if clients can not come in to pick up food because weather closed so the one at home would reset days thus needing the computer/database at home but only a few times a yr
Can you link a good one to watch?
Can front end change the back end database content? Thank you
By content do you mean can the front end change data in the back end? If so, the answer is yes.
@@TLDW_Tutorials
Situation：There are three people in a company to form a network and share the same database (around 3000 data). Required function: filtering data, update data, save comments with time stamp, insert data
Question：Is it suitable to use microsoft access?
Thank you again. ^^
@@user-hv3sq6fx1o Yes, you should be fine to use Microsoft Access. Just make sure each person uses their own front end database to update and insert data. Let me know if you have any issues getting the time stamp to work. But yes, this should be fine for MS Access!
how do i relink the frontend and backend?
Go to "New Data Source" -> "From Database" -> "Access" -> Find the folder with your Backend database and then the file itself -> Click on "Link to the data source by creating a linked table" -> Select your table(s) -> Hit OK.
@@TLDW_Tutorials Thank you so much!
@@maddyrose3501 No problem! Always happy to help.
What if its coming from SharePoint?
So you are wondering if your data source is from SharePoint if you can still have multiple front end databases? If so, it shouldn’t be a problem. I’ve done it before. Let me know if you have issues though.
How to use Same data base two or more different system at same a time.
I’m sorry, I’m having trouble understanding. Do you mean how do you use two or more versions of the same database at the same time? If so, after you split the database you would just make as many copies of the front end database as you need. If you have 3 users, you would make 3 copies of the the front end database. Let me know if you mean something else. Hope this helps.
See 2:43
As soon as you make the front end database copies, people can start entering data at the same time. Each person just needs to use their own front end database. I usually name the front end database file with the person’s name who will use it. Hope this helps!
how i distribute the front end copy to different computer user?
Hi, sorry I missed your comment. All you do is just make a copy of the front end file database. See 2:43.
How to share this front end tabs to different employees.
Hi! So I wouldn't email the database front end to anyone, because that can cause a bunch of connection and permission issues. Just create the front end copy file, like in the video at 2:43 and send them a hyperlink in an email (but not the front end database itself) to the folder and file (or just tell them the name of the folder and to use the version with their name). If you send a hyperlink in an email (assuming all users have access to the folder), everything should work.
Hello! So i wanna use my access database at my office. I have splitted the database, but how can i share it to other employees.And also when i update the database i want the other employees to see the updates from there own devices. Can you help?
Sorry for the late response. Just make a copy of the front end like at 2:43 in the video. It’s as easy as copy and pasting a new file. Just make sure you update once of them before you do this so they are all updated.