Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: czcams.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
Steve, If you're still in this -- thank you! The series is great, and just what I needed. I worked as an accountant and Excel spreadsheet designer for 35 years. I, unfortunately, had a stroke 4 years ago and had to retire. Luckily, I did not suffer any loss of cognition nor memory function. I've practiced hard and can type again, so I have decided, as part of my recovery effort, to study and become expert at VBA (and, perhaps other code languages as well). I think I might some day be able to write code via the internet for clients. I've worked slowly through your Basic series during this past Summer and will go on to the Advanced VBA course in the coming months. it's become part of my daily routine. Thank you. I really never expected to gain so much of my functionality back!
@tyrielle Cole - The entire point of splitting the database is to take your forms, reports, and stored queries OUT of the back-end and put them in their own file. You can now make your changes to your new front end and distribute that to your users with any changes you like.
Steve's voice is very soothing and his presentation is very thorough and explanatory. He takes you through things step-by-step and even suggests at one point if yours isn't set up exactly as he has it, pause this video, do a search to figure it out, then come back when you do. I like that it's not too technical - but, it helps you take care of some pretty technical stuff. I plan to look up more tutorials by this presenter. Thank you.
An even better way to go about this is to attach some kind of USB storage device straight to your router's USB port. Go into the router settings menu and set up the storage device to be shared on the network (or open ports so it can be accessed from anywhere). Store your database back end straight on the attached USB storage and point your front end to it. That way, you don't have to rely on a host computer to be turned on when you wanna access the database, since your router will be on at all times anyways.
Thank you sir, I've been struggling with ability to share an Access DB for two weeks and your tutorial has shed light on what I need to do. All the best!
This is awesome! I've been looking for how to distribute the access database that I made more efficiently without errors. Now I can even upload back end file onto cloud drives such as google drive and one drive, so everyone may access the database remotely. Thank you for your video.
Happy to help! You may want to be a little careful about how you post your data up on cloud drives. The file on a cloud drive is a copy, not the actual file. As such, you will get conflicts between file versions. The best answer is to put the back end database on a network share. If other people not on your local network need access to the back end, you may need to consider a VPN solution. Under NO CIRCUMSTANCES do you want copies of your data file except for backups.
+carol taylor I'm glad they are helping you out. I suggest you look at my "How To" video on user access. You can find it here: czcams.com/video/DxRpo496w_c/video.html
+Don Hunt Yes, you can distribute the front end file to everyone you want. The back end file should not be sent to anyone and remain in one place on the network.
Hi, Steve you are doing a great job, I keep watching your videos again and again, there are awesome, would you please explain for us how make a print button and count how many times the button clicked in a contentious form layout. Thank you.
Valerie McDevitt That won't work. You only want tables in your back end. Forms, Queries, Reports, and your VBA code modules should all be in your front end.
Thank you very much for nice video lessons .. Sir, please would you give answer? My question is the same process for Ms access 2019 and 2021 ? I have completed my program for Indian customers. And it is in Ms access 2019. I am going to buy Ms office 2021 pro plus.
Thanks for the great video sir. I am having one problem with my front end file. After splitting, if I change the location or name of back-end file, the front end file is not opening. Even with holding shift-key, the access application simply opening to it's default screen asking to create a new database but my front end objects not visible... Plz tell me the solution for this
Hello Steve, thank you very much for your videos. It helps me a lot. Is it possible to change the path to the backend by VBA? I try to check the path after starting the frontend. If the backend is not there it should popup a window where I can change the path to the backend. It's for the distribution of the db. So the user can store the files wherever he want.
Yes, you can loop through the tabledefs and set their .Connect property to a connection string that points to the database file or SQL Connection string. Here is some code I found at www.microsoftaccessexpert.com/Microsoft-Access-Code-LinkTable.aspx: Function createAttached(strTable As String, strPath As String, strBaseTable As String) As Boolean '************************************************************************************ '* Create an attached table in the current database from a table in a different MDB file. '* In: * '* strTable - name of linked table to create * '* strPath - path and name of MDB file containing the table * '* strBaseTable - name of table in strPath MDB * '* Out: * '* Return value: True/False, indicating success * '* Modifies: * '* Nothing, but adds a new table. * '************************************************************************************ On Error GoTo CreateAttachedError Dim tdf As TableDef Dim strConnect As String Dim fRetval As Boolean Dim myDB As Database DoCmd.SetWarnings False Set myDB = CurrentDb Set tdf = myDB.CreateTableDef(strTable) With tdf .Connect = ";DATABASE=" & strPath .SourceTableName = strBaseTable End With myDB.TableDefs.Append tdf fRetval = True DoCmd.SetWarnings True CreateAttachedExit: createAttached = fRetval Exit Function CreateAttachedError: If Err = 3110 Then Resume CreateAttachedExit Else If Err = 3011 Then Resume Next End If End If End Function
Hi Steve. I followed your instructions and splitted the DB. I emailed the ACCDE copy to users which I installed it on their desktop, the path points to the BE located in the shared folder on network (RO folder). When users open the ACCDE file from their desktop, the message says "Read-Only" with a button Save As. I tried to save as but users can't enter data in. I even tried another way by changing properties of the form to data entry = yes, Records lock = no locks. It does not help. Could you please kindly help? thanks a lot Steve.
If you would help me to know how to create the same multiuser database with back-end/front-end that allows other users to have "adding/modifying" access on source file(backend) as even if I'm creating "follow up data base" allowing users to create/add more cases? I would be grateful if you would give me some of your time to reply on this
First off, Great video. Exactly the issue I am trying to resolve now. I have a question if you are still answering any. I searched the questions below and cant seem to find the exact answer I am looking for. Currently I am using linked tables already from Excel as my main source of input for my forms. These excel tables are updated daily from an SAP program. Then my Access program pulls that info down into forms and queries. SO... My first question is do you foresee any issues after splitting the database with multiple users on at the same time pulling from those linked "already linked" tables, or is splitting even necessary since they are already linked from Shared folder anyways? My Second question is if I am still modifying and improving the "front end", to improve look and performance. Does each user need to be redistributed the new front end? or since this is literally only being viewed and read with NO NEED to modify by others except myself. is it possible for each user to just not "Save as" and multiple users use it in a READ ONLY format from the Main shared location?
You probably already figured this out, but a copy of the front end needs to be put on each user's C: drive. Whenever you make updates, a new copy will have to be sent out. I agree with Steve that a batch file that users run to give themselves a fresh copy is a good way to go. I'd put the latest front end in a network location, but separate from the back end, and have the script just copy it to their C: drive. You can also make the front end read only in its properties and have the script also put a shortcut to the actual file on their desktop. That way you can set a custom icon on the shortcut, and the actual front end database is hidden somewhere on their C: drive.
Dear Mr steave, I have an access data base split in two parts back end and front end, i want to run my front end file from any computer or mobile via internet browser, I have study some ideas on net, for example share point or publish a data base but can not achieve success, can you help me by create a video about this. Regards, Muhammad Sarfraz from Pakistan.
Having trouble adding permissions to the front end Access DB to a networked computer. WHen I try to add, the only hyperlink is select a principle, and when I click that, it won't let me change locations to find the 2nd computer...what to do?
This video is very helpful, But I have an issue. I work on the virtual system called citrix of our client. As you mentioned in the video, I created back end database in the the shared folder which is located in the network drive, say drive "D". and I created another access file containing forms which I sent to my team members vial mail, so they saved that file into there own desktop. Now when the open their front end file to update data into the back end database. the error message appears stating "can not open \\D:--------(Back end file location)..already in use". why is this happening? please provide some solution.
Hi Steve, thank you for posting this video. I just would like to be clear that splitting the database allows multiple users to utilize and input into the database a the same time???
i dont think uou have to split the db to make i multi user friendly. You can even use the mdb file anywhere and its by default supports multi users. There is vb code to secure forms, accidental table deletion etc.
If you are going to have more than one person working in the application at the same time, then yes, you must split your database. One file should contain the data and it is linked to by a front end file (forms, reports, queries) which gets put on everyone's machine.
Your wording may be incorrect. You "must" split your database is not correct. You can operate with multiple people in the database at the same time, but it is advisable to split the database because bandwidth issues and other factors. However, you are able to run multiple users from the same file, at the same time. Try it out. It will work. It may not work optimally though. The biggest thing you have to consider when running a split design in future changes and getting those changes to all your users. There are helper applications that folks have created out there for Access, but they are not free. Consider your environment and whether it would be better to host SQL Server. I was hopeful that MS would make Access more like SQL, but it seems they are going a different direction. Access is a great tool, but still somewhat limited.
Hi Steve Your video was excellent and I have managed to split the database and can access from two different computers. But when one is using, we cannot open it on another machine. How can two computers view the same database
Hi steve! These video tutorials are great & amazing. Thank you for sharing your knowledge with us. I've been learning a lot. I sent you an email about timestamp, maybe you can help me out. Thank you & keep it up!
Steve, great series, thanks for putting all the work into it. I've inherited an old Access 2003 database that needs a lot of work. It's currently stored (unsplit) on a NAS in a small office that typically has 5 or so users accessing it. I'm trying to speed it up primarily, which I suspect, will involve quite a bit of redesigning and restructuring. I understand that splitting it will add more security to the data, but would I expect to see any sort of performance improvement?
Well no. Performance would be about the same, maybe slightly slower than just running the single file. However what you get by splitting it is that people can be accessing it simultaneously.
My question is what happens if you want to do some designs on the forms or do more programming. Can you still do that on the backend database without causing problems for the frontend users?
@Ezwah7 I strongly advise against that. Your back end should ONLY be the data (tables). You should never have more than one back end file. You can make backups of the back end, but you don't want to distribute different versions of the data around. If you are trying to give certain people access to only certain data, use the login credentials to limit what people have access to.
HOW TO SPLIT THE DATABASE TO I CAN GIVE PERMISSION TO ACCESS MODIFY DATA BUT TO MODIFY THE DESIGN OF MY FORM, LIKE I NEED MY ASSISTANT TO HAVE THE FORM TO MODIFY DATA AS I DO ,BUT NOT TAKING THE ACCESS TO THE DESIGN MODE
I have issue when I split my database. When I tray to do a search information i can update all the information but my combo box don't work. I use VBA code to do all my search. But my combo but work perfect if I don't spill my database.
Your videos are very helpful! Thanks for taking the time to post! Question- are the steps for splitting a database the same if I would like to put the front end on a Share Point site (2010 version)? I would like to have it posted there but limit the access to only a few users. Thank you in advance for any clarification you are able to provide! :-)
Thanks this is really help me to splitting Access database. would you recommend to Back End mdb file and Front ends for users accdb files on critrix server. Thank you
Hi Steve, Is there a way to implement different security levels for different group of people for forms, report, and queries, just like the 2003 mdb? Reason for the different levels is to impose certain group of people withRreadOnlyGrp (on the viewing data via the form but cannot change); another group with CanEditGrp (for data entry and update). Thank you.
Hi Steve. 1. How can we have full editable multiple user access for the fe files 2. Can the fe files be only the forms without the table as I dont want fe users to access the tables 3. What happens when 2 fe users use the forms to access the same table?
Steve..thank you for providing videos on working with MS Access! I have split a database and when I go into one of my forms, I get a message that says Cannot open any more databases. After I click OK, the form appears to open with the correct data. Have you seen this message before and do you know what might be causing it. Also, do you know how I might be able to make the message go away? Any assistance you can provide would be greatly appreciated. Thank you!
I have a question... How is concurrency handled in the database..? Can two persons be using the front-end at the same time changing data in the Database (Back-end) without worring about concurrency..?
Yes, you can have two or more users having separate front end files all changing the same database back end. There are certain methods of record locking that Access does by default to prevent collision. The default for any DAO recordset is OpenDynaset which makes the recordset watch for changes by other users and updates the local recordset. This way you always work with the most up-to-date data. But that doesn't mean your form or report refreshes with the data, it just means that the recordset behind the form or in the VBA code has the latest data.
I am looking to modify my main interface (platform) to the db, but i still need staff to work on the main platform while i am updating it. how do i then deleted the old platform interface and link in the refreshed modified one?
hello steve after making a copy on the work station ...... after users have made inputs will my tables automatically update should i create a short cut icon on their database will it work well with 3 work station
When trying to link my tables to my sharepoint drive, it always says "not a valid file name" when entering the UNC path. Do you know how to get around this?
this is so helpful i wonder if its working on VB.net 2010? if its possible my purpose is to get faster the accessing database access passing to network drive database using this method. can you confirm on my issue?
I'm developing a large application. I've split the database, but I continue to develop more objects. The live data is on the backend. The old front end works withe the backend. What do I have to do to change the front end? I'm guessing I have to import my new tables into the old backend. What are the necessary processes to update the application on the backend and frontends.
David Locke Let me just make sure I understand what you're saying. You have a backend data file, but as you've been developing more you'd made more tables as a part of your front end. Now you want to know how to get those new tables you've created onto the backend. Is that right?
I must say with every honesty that u are one of the best teachers I have ever listened to. Your contents are the best. I am testing out my knowledge by designing database for a high school around my locality that would compute the student's results. Each school year here has three terms. There is a result computation for each term and then the annual result computation and the ranking of students based on the total aggregate of all their subjects ; then compared to the aggregate of other students within the same class to determine the position of each student. That is who would take 1st , 2nd. 3rd etc.. Until the last position. My problem is that I don't know how to write a VBA code that would help me determine the position of the students. The database I designed has computed their results except for their positions. Please sir, I really do need your help. Please reply soon. Thanks
sir I don't understand what you mean by GPA. I explained everything well. I VBA code snippet that I could use to compare the students overall averages against each other to determine who gets what position.. I hope u understand.
Well if they have a GPA (grade point average) then you just sort your students by their GPA. Here is a list of letter grades and their numerical value: A = 4.00 grade points A- = 3.70 grade points B+ = 3.33 grade points B = 3.00 grade points B- = 2.70 grade points C+ = 2.30 grade points C = 2.00 grade points C- = 1.70 grade points D+ = 1.30 grade points D = 1.00 grade points D- = 0.70 grade points WF/F=0 grade points Find the students average GPA by totaling up their grades for each class, then divide that number by the number of classes. That gives you their GPA. Then you can sort from highest to lowest in a query to see who is at the top.
Hi, Thank you for this video. I need to create a front end that only has links to the forms, reports and maybe queries. I've followed your video step by step but the front end file still has links to all the tables and allows the users to edit the tables directly. Thank you.
Elizabeth Tolulope Babalola Your Forms, Queries, and Reports ARE the front end. You only want to link the front end to tables. To prevent users from being able to modify those forms, queries, and reports you'll want to follow my advice for securing the front end in a later video.
Good morning Steve. After successfully splitting the database I have an error while opening the _fe file on the second workstation. Is there anyway I can share a screenshot with you?
Hi Steve, Thanks it was a very good and a informative video. Can i store the backend file to the SharePoint and give the front end to the different users to work on the file. Like updating the data. Vijayan Lawreence
Hello, thank you for this video, as well as all of your other videos! Your effort in making these videos, as well as the willingness to share your knowledge, is very much appreciated. I have two questions I would like to ask regarding the splitting of an Access database: 1) If I am understanding this concept correctly, if I have an Access Database that contains Tables, Queries, Forms, and Reports, and the Forms and Reports may be supported by VBA code, will splitting the database allow uses to still enter data into the tables through the forms that have been set up, while still being able to use and view the reports? 2) After splitting the database, if I store the back end (Queries, Forms, Reports, VBA Code) on a local non-shared directory, and I put the front end (Tables) in the front end and share that on a shared network drive, will users still be able to enter data into the tables through the forms? Thank you if you can help! It will be very much appreciated!
You have the front and back ends reversed. The front end has forms, reports, and queries. The back end contains the data. You pass around a copy of the front end to everyone but leave the back end in one place on your network.
+Programming Okay, now I understand, thank you for pointing that out. So after splitting a database, users will be able to enter data into the back end tables via the front end forms?
hello again, i still can't get this to work in a multi-user environment. I create the front end as shown in the video, but how do i create multiple front ends and distribute them? Can some one please help as this is driving me crazy
+Programming hi, thank you for your quick reply but just to be clear as i am hearing different things from different people when i read forums or general web searches; i save the front end file as in the video, is there only only one front end in the folder? there are 12 people in my section, do they not get there own front end? or do i simply copy the file (as in right click copy) paste it to an email and send it? The reason that i ask is that i'm reading so much conflicting information and i'm trying different things but its just not happening. Please bear with me, i am so close to cracking it
Thanks for your video ~ I did the splitting and the backend file on the server and distributed the frontend on the machines - my question is how can the front end users enter data offline (without being connected to the server)
A more important question would be why would you want to? As soon as someone gets disconnected from the back end, they will no longer be seeing the latest information, and if they were to enter something into the system how would they know it hasn't already been done. There is a way to cache data into some local tables, then when reconnected flush the data from those local tables to the back end database, but honestly you're just asking for trouble doing that. It would be much better to just get everyone set up on a VPN. That way they can be on the network from anywhere.
Steve, I split my database and when a created a new table I see it on the front end but it does not have all the fields. how do I update the table in the front end?
STeve, Excellent Presentation and thanks a lotQuick question. I started at a organization where I believe they are using this concept of Split I see that they is less population who uses Front end and none backend except the programmer who used to work here. If I were to make changes to front end, in terms of adding a new field, or any report change, how can I take the coopy of that front end, backend, merge it and make changes ? Can we do and is that possible I am not sure where the actual file is residing which was used to split the backend and front end
You need to make the front end a file people copy from the network and put on their desktop to run. When you make changes, just post the new front end file to the network and have them re-download it. There is nothing to do to the back end file.
Hi Steve. Thanks so much for the video, it pretty much saved my ass here at my office. I wanted to know if there was a way to make my database accessible to another computer which will also be able to modify it. In other words, if i can have two people in putting data onto the database simultaneously. Any help in this regard would be greatly appreciated.
Yes, in fact that's the whole point of splitting the database in the first place. All you have to do after you split the front end and back end as I describe in this video, is to start handing out you front end version to the people who need it. Do NOT give them the backend file. That's the one everyone will be connecting to simultaneously. Just make sure your backend file is on a SHARED FOLDER which all of the computers in your network can access. They should all be able to use the front end file to read the back end data while others are on it as well.
Thank you for your videos, they are very well done. I have been working along on my own database. I just wish you had mentioned making a backup of your file BEFORE doing the split. How do I make changes to my front end now that it is split? Can I copy the files from the backend over to a copy of the front end to get it back to the pre-split stage so I can continue with development?
All you have to do is open your front end, then right click on the table and select "Convert To Local Table" to bring that table back into the front end file.
Thank you for your useful videos, I have 5 users working in the same time ,but only one user could append new data the other stopped or get this message "could not update currently locked " one of the fields type attachmentthanks for any help
Hi, can you please help just to clarify this as i haven't had to do anything like this before and I need to check before I make any mistakes. I work in a small office where 6 of us access an Access 2010 database on the network F drive. It is currently set up as a single user system where we take turns to use it, which is ridiculous. We need a multi-user system, splitting the database is clearly a good option. But before I make any changes, I just need to confirm a few things about the front end. Is there only one front end file that everyone shares or does each person on the network get their own FE file?
Alan Hunter Each person would get a copy of the FE file and run it from their local computer. The BE file remains on the network drive and everyone would share that file but only by accessing it through the FE you distribute.
Programming Hi, thank you for your quick response. I think that I may have become confused because on the video the FE and BE were in the same file on the shared network. So now all I have to do is copy the FE and email it out. Thank you very much for your help and your very informative video
Tell me how to increase the speed of the daabase, when more than 15 members are using then it's taking lots of time to save, retrieve the data from sharedrive
Hello, Thanks for the great tutorial. However here is my problem. I would like front end users (about ten people (who don't have MS access) to be able to fill in a form but I would like that form, once validated, to put data into one of my back end tables. Any tips on how to do that. Thanks for your help
I have a database that imports data (from linked Excel files), updates tables and spits out reports. The users are not allowed to enter data manually from the database. If I split the data base, all I need is the swwitchboard, correct?
Hi, thanks a lot for the tutorials ! How do you manage the "Enable Content" message if you have a Pop up Login Userform when users open the front end ? What happens is that when they first open the file, they receive the Enable Content message but they cannot press it because of the Pop Up Login Form and they cannout login because the content is not enabled.. thanks !
+Andrei Lungu This is a security feature of Access. Open up the database with Access. Go to File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings. There you can select to enable all macros. Alternatively from the same Trust Center Settings window you can go to the Trusted Locations section and add the folder where your database is located.
You should never expose your tables to users. The data is the most critical part of an application and you should only allow users to view/edit what you want them to view/edit. So build your forms, reports, and queries in one database, and create links to the tables in your back end database.
Hi Steve, Thanks for your videos, they are so great. Just to clarify, if your database has a form that allows users to edit/add records, does the 'read only' front end prevent the user from using that form? Or does it update the back end in the same way as if the file was not split?
I responded to Paul Tax about this. The only reason that we want the "read only" version on the network is so that no one opens it. You want them to copy the file down to their local system and run it. Once they do that it should not be "read only" anymore. Once the FE is on their local system, they will have access to the tables in the backend so make sure you are coding things and limiting access appropriately.
Hi Steve. Whenever I split the database and encrypt it, I can no longer open the front end. Off the top of your head, do you know why? I created it in Access 2013 and i get a debug screen and it takes me to the line in VBA: Set rs = CurrentDb.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly)
Programming I can't get to the front end. After I encrypt the BE I try to open the front end to delete the tables and relink them with the password, but it doesn't even open the front end at all. It tries to open but it takes me right back the the Access Home screen.
gambitbt Hmmm, sounds like something happened to your front end file and it got corrupted. Without actually working with it, I'm afraid I don't know what to tell ya.
hi! Thank you for the video. I place my database folder on the public drive where everyone has access to the folder. The issue i am having is when i split the database, on another user computer they can view the back-end however they can not access the forms; there is an error that say's "not valid path"
Forms, reports, and queries should not be in your back end file, they should be part of the front end which everyone has a version of on their local computer. The back end file should only contain data in tables.
Hello Steve! I hope you are doing well. Aside from participating, the beauty of your videos, is I can save them and refer to them later. My question is, I am contracting at a small company who is using the Back-End/Front-End database format. The database (both ends) are on a cloud drive M:\. They'd asked me to write 3 reports, which I did, on my PC at work. The problem is I cannot Import the reports into the Front-End. I imported a few queries and a couple of tables, I created, but Access "balks" when it gets to the reports. I message is displayed which essentially says, I need exclusive access to make data changes. "Data changes??" It's a couple of reports! I'm not changing anything. The MakeTable Queries I imported, make more data changes than these lowly reports. (the .laccb file is ALWAYS there!), Do you have any ideas as to how I can get these reports into the front-end? The employees there, GO TO the front-end Access to use it. To my knowledge, it is not distributed. Thank you, Steve! And thanks, BTW, for your videos on report writing! Yet to come, I think.
Dear Steve, Before splitting the database, is there anything I should keep in mind regarding changes to the database? I can (for sure) imagine that there will be changed needed to be done on either ends. Are there maybe any changes that cannot be done any more? I think of both tables changes as well as changes for form functions.
Hi Steve. So once the users make a local copy of the front end on their computers, each of those local front ends will be simultaneously linked to the back-end? Secondly, is it sufficient for the user to download the front end just once?
To your first question, yes. The data is in one place but all the front ends share that data. To your second question, yes. They only need to get the front end on their system one time, or each time you release a new version of the front end you may want them to have those changes in it. But the data that is in the tables is shared.
hi, the whole team can access the database on the network F drive but it is only currently allowing one person to access it. i have split the database front end and back end as per the video but it does not work as expected. I copied the front end and emailed it to my colleague, we both connected to the database at the same time to run a macro but i was instantly kicked out due to an exclusive ownership issue. It was my understanding that this would make Access into a multi concurrent system. Have i done something wrong? Could someone please advise
+Programming I contacted the IT support team and they confirmed to me that the folder on the F drive is automatically a shared file and fully supports multi user applications. However, they will not support Access databases so they would not help me any further. I did everything as suggested by the video, so i'm not sure how to resolve this but i nee a fix. Any help would be most grateful
+Alan Hunter I'm saying the file itself is locked for only single user access, not the folder. Try exporting all of the tables from out of it into a new database file, then try linking to that new file.
+Programming hi, thank you for your patience. As you can see, i'm not an Access guru! I feel like am so close, but yet so far away. Just to get this cleared up, as i am desperate to resolve this issue, I should create a new database folder, import all of the tables from the existing database to the new database, then link the new front end to that file? current split database folder -> new folder -> front end
Thank you for this amazing video Steve. I have one question thou, can you split a web based MS Access database? I mean if you don't have a Sharepoint server to host your database on. Thanks
A web database requires Sharepoint. Essentially Sharepoint becomes your back end. I recommend that you migrate your databases to SQL server if you want to make your database more widely available. I cover how to do this in my advanced series.
I have a split database - the front end is on a shared drive as is the back end. I work on this front end on a saved copy of my desktop, then copy that front end onto my shared drive, link the backend, then make sure the linked tables are showing the server address and not my shortcut to the server. I made a change and copied the newest version of the front end to the shared drive. I can access the front end, but when others try to, it skips over the file completely and takes them to the recent database screen. I can not figure out what is different. Nothing has changed on the shared files permissions. I have also tried to unsplit the database and users still can't open the front end/back end combined file. Any ideas?
I had them right click on the file, copy, go to their desktop and paste, then try to open. Same thing, it opens right to the recent files page. I have another "play" database (not split, no userid and password) and pasted that onto the shared drive. They can open that with ease. No issues.
Thank u 4 yr response, two different users access the same MS Access 2013 database at the same time.The users try to change the same record .in this statues the user recived the error message "could not update ; currently locked. addationly one of the table fields is attachment. "
You can't do that nor would you want to allow it. Two people should not be changing the same data at the same time. You have no way of knowing who made the right changes.
I have a few question about this method, I couldn't open the UI gives me a location file problem I linked the new database location but the UI Still not working Please help! I need to finish this job! Otherwise My boss is gonna kill me
Make sure you have full read/write access to the shared folder the Back End is being saved to. If you only have Read access then you'll have problems connecting.
Thank you! All the permissions are set but still not working I was ready and the file that is giving me problems is ext .accdr Basically you take you accdb and change the ext to make it like a special mode I understand that I need to create one accdr with the new splited database but I don't know how? Have you heard something like this Inbox me and I'll send you some pics You will really save me
When splitting my database and saving it the database folder I can type a file name with _be,accdb but I don't have a choice for Save as type, I only have .accdc. I see on your video you have "Microsoft Access 2007 - 2013 Database (".accdb)" in Save as type. This seems to cause problems when trying to open the back end file. I'm using Office 365 Access 2013 Any solutions? Thanks Great videos by the way I have watched most of the series
Hi Steve, Please review my Comments on other videos posted - I need your advice on the following: As you know, when one develop a system, I do it locally and point all files and folders path - as you have indicated; one should use a UNC path when posting the backend. My point here is; I need an efficient way to use variables and modify them to the new location as I move forward with new updates and upgrades, Thanks!
hi Steve i have a major doubt im trying to distribute this files via SSE wizard im quite worried as a lot of variables . can u please make a video or guide me in the direct Regards ps. Thank You for the Videos :)
What if you want multiple users to be able to make read and writes at the same time? i do not understand the purpose of splitting the database up. Users can already view it
+Chance Finley That's what splitting the database allows you to do. One file stores the actual data (back-end) while the other file (front-end) can be distributed to everyone in the office. Since the data is not being distributed but instead it is shared, people can edit data simultaneously.
Chance Finley The FRONT-END is read only. I suggest doing it that way so that no one can change the forms, reports, and queries of the master file that everyone copies. When a person tries to use the front end, since it is read only, it asks them if they want to save a version of it locally to their system. This way everyone gets their own version and cannot change the original version you created. The back end file doesn't get copied, it stays where it is and everyone shares it.
+Chance Finley Ya know, I've never had to try that. You may want to distribute the front end file through some other means if that's the case. Perhaps have a batch file on their desktop that copies the most recent version.
I have a share drive in my company. Can I split my database then keep the back end in the share drive, front end in all users' computers (copy method) ? Right now, everything in one file and it's really slow for users to open the file.
Thank for replying. I got a run time error once a while when the network is too slow, and two computers are opening one table at a time. My forms are mostly workflow forms, they tell me how many parts we have at each station, so the forms need to load data from a lot of tables to do the calculation (Dcount or Dsum). Do you have any tip to stop this network conflict ?
completelystupid87 Use recordsets instead of Dcount and Dsum. You might also want to try using recordsets to gather, display, and edit data instead of bound forms. If you get the data using a read-only recordset you will avoid collisions with other users. When you want to update data, just open up a new recordset, update the data, the close your recordset.
Hi Steve, do I need to follow the same process to get the file onto each users local drive or can I simply ask them to copy and paste it to their C drive or email it to them? Thanks
Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.
Coffee With Steve: czcams.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
Steve, If you're still in this -- thank you! The series is great, and just what I needed.
I worked as an accountant and Excel spreadsheet designer for 35 years. I, unfortunately, had a stroke 4 years ago and had to retire. Luckily, I did not suffer any loss of cognition nor memory function. I've practiced hard and can type again, so I have decided, as part of my recovery effort, to study and become expert at VBA (and, perhaps other code languages as well). I think I might some day be able to write code via the internet for clients.
I've worked slowly through your Basic series during this past Summer and will go on to the Advanced VBA course in the coming months. it's become part of my daily routine. Thank you. I really never expected to gain so much of my functionality back!
@tyrielle Cole - The entire point of splitting the database is to take your forms, reports, and stored queries OUT of the back-end and put them in their own file. You can now make your changes to your new front end and distribute that to your users with any changes you like.
Steve's voice is very soothing and his presentation is very thorough and explanatory. He takes you through things step-by-step and even suggests at one point if yours isn't set up exactly as he has it, pause this video, do a search to figure it out, then come back when you do. I like that it's not too technical - but, it helps you take care of some pretty technical stuff. I plan to look up more tutorials by this presenter. Thank you.
An even better way to go about this is to attach some kind of USB storage device straight to your router's USB port. Go into the router settings menu and set up the storage device to be shared on the network (or open ports so it can be accessed from anywhere). Store your database back end straight on the attached USB storage and point your front end to it. That way, you don't have to rely on a host computer to be turned on when you wanna access the database, since your router will be on at all times anyways.
Thank you sir, I've been struggling with ability to share an Access DB for two weeks and your tutorial has shed light on what I need to do. All the best!
This guy is so far one of the best access teacher I have seen...vraiment thank you for the video
This is awesome! I've been looking for how to distribute the access database that I made more efficiently without errors. Now I can even upload back end file onto cloud drives such as google drive and one drive, so everyone may access the database remotely. Thank you for your video.
Happy to help!
You may want to be a little careful about how you post your data up on cloud drives. The file on a cloud drive is a copy, not the actual file. As such, you will get conflicts between file versions. The best answer is to put the back end database on a network share. If other people not on your local network need access to the back end, you may need to consider a VPN solution. Under NO CIRCUMSTANCES do you want copies of your data file except for backups.
+carol taylor I'm glad they are helping you out.
I suggest you look at my "How To" video on user access. You can find it here: czcams.com/video/DxRpo496w_c/video.html
thumbs up for the most valuable tutorial. Already subscribed. Thanks a lot
+Don Hunt Yes, you can distribute the front end file to everyone you want. The back end file should not be sent to anyone and remain in one place on the network.
Hi Steve, this video solved a major challenge for me. I also want to know how to create Login form for users.
Can you do a video of this also?
You're a thorough, amazing teacher. Thank you.
Hi, Steve you are doing a great job, I keep watching your videos again and again, there are awesome, would you please explain for us how make a print button and count how many times the button clicked in a contentious form layout. Thank you.
The subform is in the same DB and is in the back end. The table on which it's based, however, is in the front end.
Valerie McDevitt That won't work. You only want tables in your back end. Forms, Queries, Reports, and your VBA code modules should all be in your front end.
nice video..i learned new things in your videos...keep it up!
Thank you for sharing this tutorial. Very helpful.
Thank you very much for nice video lessons ..
Sir, please would you give answer?
My question is the same process for Ms access 2019 and 2021 ?
I have completed my program for Indian customers. And it is in Ms access 2019.
I am going to buy Ms office 2021 pro plus.
Great video. I would like to learn how to unlink and relink the BE to and from the FE with just a click of a button
Thanks for the great video sir. I am having one problem with my front end file. After splitting, if I change the location or name of back-end file, the front end file is not opening. Even with holding shift-key, the access application simply opening to it's default screen asking to create a new database but my front end objects not visible... Plz tell me the solution for this
Hello Steve, thank you very much for your videos. It helps me a lot. Is it possible to change the path to the backend by VBA? I try to check the path after starting the frontend. If the backend is not there it should popup a window where I can change the path to the backend. It's for the distribution of the db. So the user can store the files wherever he want.
Yes, you can loop through the tabledefs and set their .Connect property to a connection string that points to the database file or SQL Connection string.
Here is some code I found at www.microsoftaccessexpert.com/Microsoft-Access-Code-LinkTable.aspx:
Function createAttached(strTable As String, strPath As String, strBaseTable As String) As Boolean
'************************************************************************************
'* Create an attached table in the current database from a table in a different MDB file.
'* In: *
'* strTable - name of linked table to create *
'* strPath - path and name of MDB file containing the table *
'* strBaseTable - name of table in strPath MDB *
'* Out: *
'* Return value: True/False, indicating success *
'* Modifies: *
'* Nothing, but adds a new table. *
'************************************************************************************
On Error GoTo CreateAttachedError
Dim tdf As TableDef
Dim strConnect As String
Dim fRetval As Boolean
Dim myDB As Database
DoCmd.SetWarnings False
Set myDB = CurrentDb
Set tdf = myDB.CreateTableDef(strTable)
With tdf
.Connect = ";DATABASE=" & strPath
.SourceTableName = strBaseTable
End With
myDB.TableDefs.Append tdf
fRetval = True
DoCmd.SetWarnings True
CreateAttachedExit:
createAttached = fRetval
Exit Function
CreateAttachedError:
If Err = 3110 Then
Resume CreateAttachedExit
Else
If Err = 3011 Then
Resume Next
End If
End If
End Function
Hi Steve. I followed your instructions and splitted the DB. I emailed the ACCDE copy to users which I installed it on their desktop, the path points to the BE located in the shared folder on network (RO folder). When users open the ACCDE file from their desktop, the message says "Read-Only" with a button Save As. I tried to save as but users can't enter data in. I even tried another way by changing properties of the form to data entry = yes, Records lock = no locks. It does not help. Could you please kindly help? thanks a lot Steve.
If you would help me to know how to create the same multiuser database with back-end/front-end that allows other users to have "adding/modifying" access on source file(backend) as even if I'm creating "follow up data base" allowing users to create/add more cases? I would be grateful if you would give me some of your time to reply on this
Can I secure and give permissions to UserTypes without creating an be/fe? Don't really have this option at my work.
Thanks Steve. Will try out with your guides.... You d man!
First off, Great video. Exactly the issue I am trying to resolve now. I have a question if you are still answering any. I searched the questions below and cant seem to find the exact answer I am looking for. Currently I am using linked tables already from Excel as my main source of input for my forms. These excel tables are updated daily from an SAP program. Then my Access program pulls that info down into forms and queries. SO... My first question is do you foresee any issues after splitting the database with multiple users on at the same time pulling from those linked "already linked" tables, or is splitting even necessary since they are already linked from Shared folder anyways? My Second question is if I am still modifying and improving the "front end", to improve look and performance. Does each user need to be redistributed the new front end? or since this is literally only being viewed and read with NO NEED to modify by others except myself. is it possible for each user to just not "Save as" and multiple users use it in a READ ONLY format from the Main shared location?
You probably already figured this out, but a copy of the front end needs to be put on each user's C: drive. Whenever you make updates, a new copy will have to be sent out. I agree with Steve that a batch file that users run to give themselves a fresh copy is a good way to go. I'd put the latest front end in a network location, but separate from the back end, and have the script just copy it to their C: drive. You can also make the front end read only in its properties and have the script also put a shortcut to the actual file on their desktop. That way you can set a custom icon on the shortcut, and the actual front end database is hidden somewhere on their C: drive.
Dear Mr steave, I have an access data base split in two parts back end and front end, i want to run my front end file from any computer or mobile via internet browser, I have study some ideas on net, for example share point or publish a data base but can not achieve success, can you help me by create a video about this. Regards, Muhammad Sarfraz from Pakistan.
Having trouble adding permissions to the front end Access DB to a networked computer. WHen I try to add, the only hyperlink is select a principle, and when I click that, it won't let me change locations to find the 2nd computer...what to do?
This video is very helpful, But I have an issue.
I work on the virtual system called citrix of our client. As you mentioned in the video, I created back end database in the the shared folder which is located in the network drive, say drive "D". and I created another access file containing forms which I sent to my team members vial mail, so they saved that file into there own desktop.
Now when the open their front end file to update data into the back end database. the error message appears stating "can not open \\D:--------(Back end file location)..already in use".
why is this happening? please provide some solution.
Hi Steve, thank you for posting this video. I just would like to be clear that splitting the database allows multiple users to utilize and input into the database a the same time???
i dont think uou have to split the db to make i multi user friendly. You can even use the mdb file anywhere and its by default supports multi users. There is vb code to secure forms, accidental table deletion etc.
If you are going to have more than one person working in the application at the same time, then yes, you must split your database. One file should contain the data and it is linked to by a front end file (forms, reports, queries) which gets put on everyone's machine.
Your wording may be incorrect. You "must" split your database is not correct. You can operate with multiple people in the database at the same time, but it is advisable to split the database because bandwidth issues and other factors. However, you are able to run multiple users from the same file, at the same time. Try it out. It will work. It may not work optimally though. The biggest thing you have to consider when running a split design in future changes and getting those changes to all your users. There are helper applications that folks have created out there for Access, but they are not free. Consider your environment and whether it would be better to host SQL Server. I was hopeful that MS would make Access more like SQL, but it seems they are going a different direction. Access is a great tool, but still somewhat limited.
Perfect Explanation. Thank you
Hi Steve
Your video was excellent and I have managed to split the database and can access from two different computers. But when one is using, we cannot open it on another machine. How can two computers view the same database
Hi steve! These video tutorials are great & amazing. Thank you for sharing your knowledge with us. I've been learning a lot. I sent you an email about timestamp, maybe you can help me out. Thank you & keep it up!
Steve, great series, thanks for putting all the work into it.
I've inherited an old Access 2003 database that needs a lot of work. It's currently stored (unsplit) on a NAS in a small office that typically has 5 or so users accessing it. I'm trying to speed it up primarily, which I suspect, will involve quite a bit of redesigning and restructuring. I understand that splitting it will add more security to the data, but would I expect to see any sort of performance improvement?
Well no. Performance would be about the same, maybe slightly slower than just running the single file. However what you get by splitting it is that people can be accessing it simultaneously.
My question is what happens if you want to do some designs on the forms or do more programming. Can you still do that on the backend database without causing problems for the frontend users?
@Ezwah7 I strongly advise against that. Your back end should ONLY be the data (tables). You should never have more than one back end file. You can make backups of the back end, but you don't want to distribute different versions of the data around.
If you are trying to give certain people access to only certain data, use the login credentials to limit what people have access to.
HOW TO SPLIT THE DATABASE TO I CAN GIVE PERMISSION TO ACCESS MODIFY DATA BUT TO MODIFY THE DESIGN OF MY FORM, LIKE I NEED MY ASSISTANT TO HAVE THE FORM TO MODIFY DATA AS I DO ,BUT NOT TAKING THE ACCESS TO THE DESIGN MODE
I have issue when I split my database. When I tray to do a search information i can update all the information but my combo box don't work. I use VBA code to do all my search. But my combo but work perfect if I don't spill my database.
Your videos are very helpful! Thanks for taking the time to post! Question- are the steps for splitting a database the same if I would like to put the front end on a Share Point site (2010 version)? I would like to have it posted there but limit the access to only a few users. Thank you in advance for any clarification you are able to provide! :-)
Thanks, this really helps a lot.
Thanks this is really help me to splitting Access database. would you recommend to Back End mdb file and Front ends for users accdb files on critrix server. Thank you
You don't want to use mdb format anymore. Everything should be in the newer formats.
Hi Steve,
Is there a way to implement different security levels for different group of people for forms, report, and queries, just like the 2003 mdb? Reason for the different levels is to impose certain group of people withRreadOnlyGrp (on the viewing data via the form but cannot change); another group with CanEditGrp (for data entry and update). Thank you.
Hi Steve. 1. How can we have full editable multiple user access for the fe files 2. Can the fe files be only the forms without the table as I dont want fe users to access the tables 3. What happens when 2 fe users use the forms to access the same table?
Amazing, thanks for the knowledge sharing
Steve..thank you for providing videos on working with MS Access! I have split a database and when I go into one of my forms, I get a message that says Cannot open any more databases. After I click OK, the form appears to open with the correct data. Have you seen this message before and do you know what might be causing it. Also, do you know how I might be able to make the message go away? Any assistance you can provide would be greatly appreciated. Thank you!
I have a question... How is concurrency handled in the database..? Can two persons be using the front-end at the same time changing data in the Database (Back-end) without worring about concurrency..?
Yes, you can have two or more users having separate front end files all changing the same database back end. There are certain methods of record locking that Access does by default to prevent collision. The default for any DAO recordset is OpenDynaset which makes the recordset watch for changes by other users and updates the local recordset. This way you always work with the most up-to-date data. But that doesn't mean your form or report refreshes with the data, it just means that the recordset behind the form or in the VBA code has the latest data.
I am looking to modify my main interface (platform) to the db, but i still need staff to work on the main platform while i am updating it. how do i then deleted the old platform interface and link in the refreshed modified one?
hello steve
after making a copy on the work station ...... after users have made inputs will my tables automatically update
should i create a short cut icon on their database will it work well with 3 work station
Do we need to split our database if we have linked tables to sql server?
When trying to link my tables to my sharepoint drive, it always says "not a valid file name" when entering the UNC path. Do you know how to get around this?
this is so helpful i wonder if its working on VB.net 2010? if its possible my purpose is to get faster the accessing database access passing to network drive database using this method. can you confirm on my issue?
Thanks, You are awesome!
amazing video thanks a lot
I'm developing a large application. I've split the database, but I continue to develop more objects. The live data is on the backend. The old front end works withe the backend. What do I have to do to change the front end? I'm guessing I have to import my new tables into the old backend. What are the necessary processes to update the application on the backend and frontends.
David Locke Let me just make sure I understand what you're saying.
You have a backend data file, but as you've been developing more you'd made more tables as a part of your front end. Now you want to know how to get those new tables you've created onto the backend. Is that right?
I must say with every honesty that u are one of the best teachers I have ever listened to. Your contents are the best.
I am testing out my knowledge by designing database for a high school around my locality that would compute the student's results. Each school year here has three terms. There is a result computation for each term and then the annual result computation and the ranking of students based on the total aggregate of all their subjects ; then compared to the aggregate of other students within the same class to determine the position of each student. That is who would take 1st , 2nd. 3rd etc.. Until the last position. My problem is that I don't know how to write a VBA code that would help me determine the position of the students. The database I designed has computed their results except for their positions. Please sir, I really do need your help. Please reply soon. Thanks
Do they not have a GPA?
sir I don't understand what you mean by GPA. I explained everything well. I VBA code snippet that I could use to compare the students overall averages against each other to determine who gets what position.. I hope u understand.
you can drop your email address so I can forward the access database file to u so you can make sense of what i'm saying
Well if they have a GPA (grade point average) then you just sort your students by their GPA. Here is a list of letter grades and their numerical value:
A = 4.00 grade points
A- = 3.70 grade points
B+ = 3.33 grade points
B = 3.00 grade points
B- = 2.70 grade points
C+ = 2.30 grade points
C = 2.00 grade points
C- = 1.70 grade points
D+ = 1.30 grade points
D = 1.00 grade points
D- = 0.70 grade points
WF/F=0 grade points
Find the students average GPA by totaling up their grades for each class, then divide that number by the number of classes. That gives you their GPA. Then you can sort from highest to lowest in a query to see who is at the top.
Dear Mr steave
Hi, Thank you for this video. I need to create a front end that only has links to the forms, reports and maybe queries. I've followed your video step by step but the front end file still has links to all the tables and allows the users to edit the tables directly. Thank you.
Elizabeth Tolulope Babalola Your Forms, Queries, and Reports ARE the front end. You only want to link the front end to tables. To prevent users from being able to modify those forms, queries, and reports you'll want to follow my advice for securing the front end in a later video.
Good morning Steve. After successfully splitting the database I have an error while opening the _fe file on the second workstation. Is there anyway I can share a screenshot with you?
Hi Steve,
Thanks it was a very good and a informative video. Can i store the backend file to the SharePoint and give the front end to the different users to work on the file. Like updating the data.
Vijayan Lawreence
Hello, thank you for this video, as well as all of your other videos! Your effort in making these videos, as well as the willingness to share your knowledge, is very much appreciated.
I have two questions I would like to ask regarding the splitting of an Access database:
1) If I am understanding this concept correctly, if I have an Access Database that contains Tables, Queries, Forms, and Reports, and the Forms and Reports may be supported by VBA code, will splitting the database allow uses to still enter data into the tables through the forms that have been set up, while still being able to use and view the reports?
2) After splitting the database, if I store the back end (Queries, Forms, Reports, VBA Code) on a local non-shared directory, and I put the front end (Tables) in the front end and share that on a shared network drive, will users still be able to enter data into the tables through the forms?
Thank you if you can help! It will be very much appreciated!
You have the front and back ends reversed. The front end has forms, reports, and queries. The back end contains the data. You pass around a copy of the front end to everyone but leave the back end in one place on your network.
+Programming Okay, now I understand, thank you for pointing that out.
So after splitting a database, users will be able to enter data into the back end tables via the front end forms?
+COtheLegend yup.
+Programming Okay great, thank you for your help!
hello again, i still can't get this to work in a multi-user environment. I create the front end as shown in the video, but how do i create multiple front ends and distribute them? Can some one please help as this is driving me crazy
+Alan Hunter You just pass around the same front end file.
+Programming hi, thank you for your quick reply but just to be clear as i am hearing different things from different people when i read forums or general web searches; i save the front end file as in the video, is there only only one front end in the folder? there are 12 people in my section, do they not get there own front end? or do i simply copy the file (as in right click copy) paste it to an email and send it? The reason that i ask is that i'm reading so much conflicting information and i'm trying different things but its just not happening. Please bear with me, i am so close to cracking it
Thanks for your video ~ I did the splitting and the backend file on the server and distributed the frontend on the machines - my question is how can the front end users enter data offline (without being connected to the server)
A more important question would be why would you want to? As soon as someone gets disconnected from the back end, they will no longer be seeing the latest information, and if they were to enter something into the system how would they know it hasn't already been done. There is a way to cache data into some local tables, then when reconnected flush the data from those local tables to the back end database, but honestly you're just asking for trouble doing that.
It would be much better to just get everyone set up on a VPN. That way they can be on the network from anywhere.
Steve, I split my database and when a created a new table I see it on the front end but it does not have all the fields. how do I update the table in the front end?
STeve, Excellent Presentation and thanks a lotQuick question. I started at a organization where I believe they are using this concept of Split I see that they is less population who uses Front end and none backend except the programmer who used to work here. If I were to make changes to front end, in terms of adding a new field, or any report change, how can I take the coopy of that front end, backend, merge it and make changes ? Can we do and is that possible I am not sure where the actual file is residing which was used to split the backend and front end
You need to make the front end a file people copy from the network and put on their desktop to run. When you make changes, just post the new front end file to the network and have them re-download it. There is nothing to do to the back end file.
Hi Steve. Thanks so much for the video, it pretty much saved my ass here at my office. I wanted to know if there was a way to make my database accessible to another computer which will also be able to modify it. In other words, if i can have two people in putting data onto the database simultaneously. Any help in this regard would be greatly appreciated.
Yes, in fact that's the whole point of splitting the database in the first place. All you have to do after you split the front end and back end as I describe in this video, is to start handing out you front end version to the people who need it. Do NOT give them the backend file. That's the one everyone will be connecting to simultaneously.
Just make sure your backend file is on a SHARED FOLDER which all of the computers in your network can access. They should all be able to use the front end file to read the back end data while others are on it as well.
Hi Steve, giving permission/access to multiple users the same process for a table and form?
Thank you for your videos, they are very well done. I have been working along on my own database. I just wish you had mentioned making a backup of your file BEFORE doing the split. How do I make changes to my front end now that it is split? Can I copy the files from the backend over to a copy of the front end to get it back to the pre-split stage so I can continue with development?
All you have to do is open your front end, then right click on the table and select "Convert To Local Table" to bring that table back into the front end file.
ok sir. plz give me some clues for vpn and where I can search and get knowledge of it. thanks
Thank you for your useful videos, I have 5 users working in the same time ,but only one user could append new data the other stopped or get this message "could not update currently locked " one of the fields type attachmentthanks for any help
+ayam niyazi I'm not sure why that's happening in your environment.
What if I want to share the back end file with certain employees, and the front end file in the shared folder of the company?
Hi, can you please help just to clarify this as i haven't had to do anything like this before and I need to check before I make any mistakes. I work in a small office where 6 of us access an Access 2010 database on the network F drive. It is currently set up as a single user system where we take turns to use it, which is ridiculous. We need a multi-user system, splitting the database is clearly a good option. But before I make any changes, I just need to confirm a few things about the front end. Is there only one front end file that everyone shares or does each person on the network get their own FE file?
Alan Hunter Each person would get a copy of the FE file and run it from their local computer. The BE file remains on the network drive and everyone would share that file but only by accessing it through the FE you distribute.
Programming Hi, thank you for your quick response. I think that I may have become confused because on the video the FE and BE were in the same file on the shared network. So now all I have to do is copy the FE and email it out. Thank you very much for your help and your very informative video
Tell me how to increase the speed of the daabase, when more than 15 members are using then it's taking lots of time to save, retrieve the data from sharedrive
Hello,
Thanks for the great tutorial. However here is my problem. I would like front end users (about ten people (who don't have MS access) to be able to fill in a form but I would like that form, once validated, to put data into one of my back end tables. Any tips on how to do that.
Thanks for your help
You can install the Access Run-Time on those computers so they can still run your front end file.
I have a database that imports data (from linked Excel files), updates tables and spits out reports.
The users are not allowed to enter data manually from the database. If I split the data base, all I need is the swwitchboard, correct?
+TraceguyRune There is no switchboard in Access 2013.
Hi, thanks a lot for the tutorials ! How do you manage the "Enable Content" message if you have a Pop up Login Userform when users open the front end ? What happens is that when they first open the file, they receive the Enable Content message but they cannot press it because of the Pop Up Login Form and they cannout login because the content is not enabled.. thanks !
+Andrei Lungu This is a security feature of Access. Open up the database with Access. Go to File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings. There you can select to enable all macros. Alternatively from the same Trust Center Settings window you can go to the Trusted Locations section and add the folder where your database is located.
Thanks! Excellent....
So there is no point in splitting your database if for example your access file only has tables in it? Btw good video Steve keep it up.
You should never expose your tables to users. The data is the most critical part of an application and you should only allow users to view/edit what you want them to view/edit. So build your forms, reports, and queries in one database, and create links to the tables in your back end database.
Love u dude. It worked.
Nice job. Very clear and easy to follow
Hi Steve, Thanks for your videos, they are so great. Just to clarify, if your database has a form that allows users to edit/add records, does the 'read only' front end prevent the user from using that form? Or does it update the back end in the same way as if the file was not split?
I responded to Paul Tax about this. The only reason that we want the "read only" version on the network is so that no one opens it. You want them to copy the file down to their local system and run it. Once they do that it should not be "read only" anymore.
Once the FE is on their local system, they will have access to the tables in the backend so make sure you are coding things and limiting access appropriately.
Hi Steve. Whenever I split the database and encrypt it, I can no longer open the front end. Off the top of your head, do you know why? I created it in Access 2013 and i get a debug screen and it takes me to the line in VBA: Set rs = CurrentDb.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly)
gambitbt I think you forgot to link your tables in. Make sure there is a tblUsers table that is linked from your backend file to your front-end file.
Programming My tblUsers is linked to the backend file. I have been trying to figure this out for months. I have got to be missing something.
gambitbt What happens when you try to open the tblUsers table from your front end?
Programming I can't get to the front end. After I encrypt the BE I try to open the front end to delete the tables and relink them with the password, but it doesn't even open the front end at all. It tries to open but it takes me right back the the Access Home screen.
gambitbt Hmmm, sounds like something happened to your front end file and it got corrupted. Without actually working with it, I'm afraid I don't know what to tell ya.
Thank you so much sir.
hi! Thank you for the video. I place my database folder on the public drive where everyone has access to the folder. The issue i am having is when i split the database, on another user computer they can view the back-end however they can not access the forms; there is an error that say's "not valid path"
Forms, reports, and queries should not be in your back end file, they should be part of the front end which everyone has a version of on their local computer. The back end file should only contain data in tables.
Thank you! I found the error! Your video was VERY helpful!
Hello Steve! I hope you are doing well. Aside from participating, the beauty of your videos, is I can save them and refer to them later.
My question is, I am contracting at a small company who is using the Back-End/Front-End database format. The database (both ends) are on a cloud drive M:\. They'd asked me to write 3 reports, which I did, on my PC at work. The problem is I cannot Import the reports into the Front-End. I imported a few queries and a couple of tables, I created, but Access "balks" when it gets to the reports. I message is displayed which essentially says, I need exclusive access to make data changes. "Data changes??" It's a couple of reports! I'm not changing anything. The MakeTable Queries I imported, make more data changes than these lowly reports. (the .laccb file is ALWAYS there!),
Do you have any ideas as to how I can get these reports into the front-end? The employees there, GO TO the front-end Access to use it. To my knowledge, it is not distributed.
Thank you, Steve! And thanks, BTW, for your videos on report writing! Yet to come, I think.
Dear Steve,
Before splitting the database, is there anything I should keep in mind regarding changes to the database? I can (for sure) imagine that there will be changed needed to be done on either ends. Are there maybe any changes that cannot be done any more? I think of both tables changes as well as changes for form functions.
Not really. Just make sure your backend file goes somewhere on the network where everyone will have access to it.
Thank you.
Hi Steve. So once the users make a local copy of the front end on their computers, each of those local front ends will be simultaneously linked to the back-end? Secondly, is it sufficient for the user to download the front end just once?
To your first question, yes. The data is in one place but all the front ends share that data. To your second question, yes. They only need to get the front end on their system one time, or each time you release a new version of the front end you may want them to have those changes in it. But the data that is in the tables is shared.
hi, the whole team can access the database on the network F drive but it is only currently allowing one person to access it. i have split the database front end and back end as per the video but it does not work as expected. I copied the front end and emailed it to my colleague, we both connected to the database at the same time to run a macro but i was instantly kicked out due to an exclusive ownership issue. It was my understanding that this would make Access into a multi concurrent system. Have i done something wrong? Could someone please advise
+Alan Hunter It sounds like the backend database file is locked up as a single access file. Not quite sure how you got it that way.
+Programming I contacted the IT support team and they confirmed to me that the folder on the F drive is automatically a shared file and fully supports multi user applications. However, they will not support Access databases so they would not help me any further. I did everything as suggested by the video, so i'm not sure how to resolve this but i nee a fix. Any help would
be most grateful
+Alan Hunter I'm saying the file itself is locked for only single user access, not the folder. Try exporting all of the tables from out of it into a new database file, then try linking to that new file.
+Programming hi, thank you for your patience. As you can see, i'm not an Access guru! I feel like am so close, but yet so far away. Just to get this cleared up, as i am desperate to resolve this issue, I should create a new database folder, import all of the tables from the existing database to the new database, then link the new front end to that file?
current split database folder -> new folder -> front end
Yes
Thank you for this amazing video Steve. I have one question thou, can you split a web based MS Access database? I mean if you don't have a Sharepoint server to host your database on. Thanks
A web database requires Sharepoint. Essentially Sharepoint becomes your back end. I recommend that you migrate your databases to SQL server if you want to make your database more widely available. I cover how to do this in my advanced series.
How is it safe when the user has to have access to the backend file location and the file itself?
how do i auto update an accde file ?
I have a split database - the front end is on a shared drive as is the back end. I work on this front end on a saved copy of my desktop, then copy that front end onto my shared drive, link the backend, then make sure the linked tables are showing the server address and not my shortcut to the server. I made a change and copied the newest version of the front end to the shared drive. I can access the front end, but when others try to, it skips over the file completely and takes them to the recent database screen. I can not figure out what is different. Nothing has changed on the shared files permissions. I have also tried to unsplit the database and users still can't open the front end/back end combined file. Any ideas?
Have you tried to make a copy of the file on their desktop, then open it?
I had them right click on the file, copy, go to their desktop and paste, then try to open. Same thing, it opens right to the recent files page. I have another "play" database (not split, no userid and password) and pasted that onto the shared drive. They can open that with ease. No issues.
Hmm.. not really sure without seeing it.
Thank u 4 yr response, two different users access the same MS Access 2013 database at the same time.The users try to change the same record .in this statues the user recived the error message "could not update ; currently locked. addationly one of the table fields is attachment. "
+ayam niyazi It sounds like you have two people trying to access the same record on the same table at the same time.
+ayam niyazi yes exactly that
You can't do that nor would you want to allow it. Two people should not be changing the same data at the same time. You have no way of knowing who made the right changes.
this that which i m looking for
I have a few question about this method, I couldn't open the UI gives me a location file problem
I linked the new database location but the UI Still not working
Please help!
I need to finish this job! Otherwise
My boss is gonna kill me
Make sure you have full read/write access to the shared folder the Back End is being saved to. If you only have Read access then you'll have problems connecting.
Thank you!
All the permissions are set but still not working
I was ready and the file that is giving me problems is ext .accdr
Basically you take you accdb and change the ext to make it like a special mode
I understand that I need to create one accdr with the new splited database but I don't know how?
Have you heard something like this
Inbox me and I'll send you some pics
You will really save me
When splitting my database and saving it the database folder I can type a file name with _be,accdb but I don't have a choice for Save as type, I only have .accdc. I see on your video you have "Microsoft Access 2007 - 2013 Database (".accdb)" in Save as type. This seems to cause problems when trying to open the back end file.
I'm using Office 365 Access 2013
Any solutions?
Thanks
Great videos by the way I have watched most of the series
Wayne Ayotte .accdc files are certified files. Those are just like accdb files but with a certificate verifying your authorship.
Hi Steve,
Please review my Comments on other videos posted - I need your advice on the following:
As you know, when one develop a system, I do it locally and point all files and folders path - as you have indicated; one should use a UNC path when posting the backend.
My point here is; I need an efficient way to use variables and modify them to the new location as I move forward with new updates and upgrades, Thanks!
Variables only exist in memory while the application is open. If you want to store something for later use, save it to a database.
hi Steve i have a major doubt im trying to distribute this files via SSE wizard im quite worried as a lot of variables
. can u please make a video or guide me in the direct
Regards
ps. Thank You for the Videos :)
Check out the advanced course when you're done with this series. I go over it there.
What if you want multiple users to be able to make read and writes at the same time? i do not understand the purpose of splitting the database up. Users can already view it
+Chance Finley That's what splitting the database allows you to do. One file stores the actual data (back-end) while the other file (front-end) can be distributed to everyone in the office. Since the data is not being distributed but instead it is shared, people can edit data simultaneously.
But you set it up for read only, how can the users edit the data then?
Does this process still work if all other users only use the runtime instead of a full version of access?
Chance Finley The FRONT-END is read only. I suggest doing it that way so that no one can change the forms, reports, and queries of the master file that everyone copies. When a person tries to use the front end, since it is read only, it asks them if they want to save a version of it locally to their system. This way everyone gets their own version and cannot change the original version you created. The back end file doesn't get copied, it stays where it is and everyone shares it.
+Chance Finley Ya know, I've never had to try that. You may want to distribute the front end file through some other means if that's the case. Perhaps have a batch file on their desktop that copies the most recent version.
I have a share drive in my company. Can I split my database then keep the back end in the share drive, front end in all users' computers (copy method) ? Right now, everything in one file and it's really slow for users to open the file.
Yes. That is precisely the reason why you would split your database in this manner.
Thank for replying. I got a run time error once a while when the network is too slow, and two computers are opening one table at a time. My forms are mostly workflow forms, they tell me how many parts we have at each station, so the forms need to load data from a lot of tables to do the calculation (Dcount or Dsum). Do you have any tip to stop this network conflict ?
completelystupid87 Use recordsets instead of Dcount and Dsum. You might also want to try using recordsets to gather, display, and edit data instead of bound forms. If you get the data using a read-only recordset you will avoid collisions with other users. When you want to update data, just open up a new recordset, update the data, the close your recordset.
Hi Steve, do I need to follow the same process to get the file onto each users local drive or can I simply ask them to copy and paste it to their C drive or email it to them?
Thanks
+Sam Martin No. Once you do this to the front end file that you'll be passing around, it is done.