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: th-cam.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!
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: th-cam.com/video/DxRpo496w_c/w-d-xo.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.
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
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.
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
@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.
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.
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.
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.
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! :-)
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
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
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
@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.
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.
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?
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?
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 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, 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.
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
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
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.
Please help. I started with the work file from lesson 56 but after i split the database the back-end ends up as a signed practice which i cannot open. I am using Access 2013 but have 2010 installed on another computer as well.
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.
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.
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 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, 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.
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
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?
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 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?
hey steve,really enjoy your tutorials.but just wanted to ask after splitting the database and putting it in a shared folder is there a way to prevent someone(other than the owner) from accidentally or intentionally deleting the back end access file itself whilst still giving users full functionality?
Not really, but if you were particularly clever you could use a 3rd "intermediate" database so the real one was not visible. That intermediate might get deleted but so long as it wasn't holding any data you can just restore any backup and be up and running again in no time. The real solution though is to move to SQL Server which I explain in the advance series.
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! 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.
I am slightly confused on this whole back end and front end. Just to clarify few things, it looked like you have a secured back end file, and a read only front end file. Every time someone opens the front end, the person will need to save a copy to their own desktop to do what they need to do. Does that mean, this copy is directly linked to the back end tables as well? Does this newly saved front end file have access to make any changes to the back end tables through front end interface such as directly clicking on the linked tables and update the tables, or run a query on front end to change tables in back end?
Steven Chen Yes, when someone saves the front end file to their computer they will be saving a fully editable file where there is the possibility they mess with things. The good news is they can only mess with the version they saved an not the file you made for the front end. The bad news all the data in the back end can also be messed with, but really that's the whole point. You want them to be changing data that everyone else uses too.
Thank you for your reply! Is there a way to limit the access, so that front end users can only run queries, forms, etc, but do not have the ability to change the structure of the tables or insert/update data in the tables? We have another platform where the data is being entered by the users however the data integrity is awful, so I am planning to update access table after manually auditing the data after extracting them from the other platform, so the users could only run reports, but never any direct interactions with the data.
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
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.
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.
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 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 :)
Hi Steve, Thanks so much for your really helpful tutorial. Yourr presentation is what I have been searching for times to make my Access application a client-server based one. I am developing a software that potentially contains over 2Gb of data in the near future. I am wonder if Access DB can accommodate that large space. Since I am addicted in programming Access, I really hesitate to change to other platform. Can you please help me on how to deal with this matter? Your sincerely! Cuong Vo
+Tuấn Cường You can move the data to SQL server but keep Access as your front end by using either linked tables, or using a DAO/ADO connection with VBA. I demonstrate how to do this in my advanced series.
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 just want to know what is the most appropriate way to update several tables together (knowing that they should have the exact number of records) Thank you, I am getting better and better at this mainly due to your videos!
Great videos! I'm hoping this tutorial could be the answer I'm looking for: Numerous times in the past I have connected to a SQL server, linked tables into an Access DB, wrote queries and then pulled the query results into Excel over a DSN-less connection, that way end users can open the Excel file and refresh the data whenever they wish without needing to have Access or a DSN on their machine. However, a new client is using Microsoft Azure SQL database which I can connect to fine, link tables, write queries and pull into Excel fine, yet no end users can do the same, even though they have complete permissions to the Access db.. I've tried about 20 different things all to no avail, so I'm hoping splitting the dB will solve the issue, else I will have to go to the SQL server host and ask if they are somehow stopping this working. any ideas, have you come across this before?
I have not since I have not ever used Azure SQL with Access, but I would look into permissions and firewalls. Make sure the user has permission on the SQL Server, and that there isn't a firewall blocking the network traffic.
Programming thanks for the info. Yes I have looked into this, the firewall is open for all users as necessary. The SQL server administrators said it looks at our IP address, although all end users on the local network have the same public ip address so that shouldn't be the issue. I will try splitting the db and see if that helps. thanks.
sir. you uploaded a great video . I taught enough from it. but I ask you that I want to use data base among the two city or country . I am in a different country and user are in other counties so how they can my data base be and fe . I made be and fe according your video plz tell me. simple how can use between two city. ......plz I am waiting a great and sincere respons. ....
You would need a VPN connection between the two networks. This is far more complex than I can explain in the comments. I suggest you talk to your local IT professionals to see how to set up a VPN.
One thing to try would be to host the "back end" on a cloud server. On the client workstation, map a network drive to a share on the remote server that contains the back end file. Then use the Linked Table Manger to link to the mapped drive. There are some tricky permissions details that will depend on what cloud provider you use. I set this up with Amazon EC2 and it was pretty easy.
What I don't like about splitting is that if the back end is password protected you can open one of the system tables in the front end and see the actual password required to open the back end!
Do you have a video on how to create a subform search with multiple dependent comboboxs? I have done the majority of the work but I cannot get the search part to work properly.
Watch my how to video on making a keyword search. You would just have to modify the WHERE clause in your query string to look for the values in each of your textboxes.
awesome thanks for your help! I am trying to watch all of your videos :) Do you also recommend any books ? especally one that would have a list of all the different command names and the action they perform? kinda like a dictionary.
Am I the only one who finds it ludicrous that you can't update the ACCDE file with new forms or reports, etc. after you split your database natively within Access???? I have to buy "Tony's FE Updater" or some crap? That is shocking to me.
ACCDE files are meant to be uneditable. Your ACCDB file should be were you make your edits, then export the file as ACCDE so that it cannot be modified by the users.
Programming how do you export an updated accde file though? That's what I don't understand. Do you just act as if you're splitting the database again? Or is there an EXPORT option I'm not seeing somewhere?
sir .. respect fully say I have no time to go any where I start duty from 8 am to 12 o clock of night and I don't have off in a week I have just only computer and internet . if you tell me about vpn our you upload a video about use database between to city or country . we all will be too much thankful to you bro. or you send me any tutorial or video to my e-mail . plz sir this will be a great kindness for me and all others who want to know that.
I'm afraid I just don't have time to explain everything you would need to know. It involves network infrastructure, active directory, and much more. People spend years learning it. I cannot possibly explain it to you in a video or in the comments.
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!
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.
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. 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.
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 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.
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
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?
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 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.
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.
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?
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?
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
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: th-cam.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!
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.
This guy is so far one of the best access teacher I have seen...vraiment thank you for the video
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: th-cam.com/video/DxRpo496w_c/w-d-xo.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.
You're a thorough, amazing teacher. Thank you.
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
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.
Thank you for sharing this tutorial. Very helpful.
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
@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.
Thanks Steve. Will try out with your guides.... You d man!
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
Can I secure and give permissions to UserTypes without creating an be/fe? Don't really have this option at my work.
Thanks, this really helps a lot.
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.
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.
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! :-)
Do we need to split our database if we have linked tables to sql server?
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
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
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
@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.
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.
Thanks, You are awesome!
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?
amazing video thanks a lot
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?
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.
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.
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, 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.
How is it safe when the user has to have access to the backend file location and the file itself?
Amazing, thanks for the knowledge sharing
Love u dude. It worked.
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
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
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
Please help. I started with the work file from lesson 56 but after i split the database the back-end ends up as a signed practice which i cannot open. I am using Access 2013 but have 2010 installed on another computer as well.
I don't understand the problem. What do you mean "signed practice"?
Sorry. I meant signed package
But I figured out how to change the file extension manually
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.
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.
how can I avoid the topping of data?
how do i auto update an accde 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
+ayam niyazi I'm not sure why that's happening in your environment.
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, 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.
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.
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?
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 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.
hey steve,really enjoy your tutorials.but just wanted to ask after splitting the database and putting it in a shared folder is there a way to prevent someone(other than the owner) from accidentally or intentionally deleting the back end access file itself whilst still giving users full functionality?
Not really, but if you were particularly clever you could use a 3rd "intermediate" database so the real one was not visible. That intermediate might get deleted but so long as it wasn't holding any data you can just restore any backup and be up and running again in no time. The real solution though is to move to SQL Server which I explain in the advance series.
thanks a lot..will look into your sql server series..
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! 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!
I am slightly confused on this whole back end and front end. Just to clarify few things, it looked like you have a secured back end file, and a read only front end file. Every time someone opens the front end, the person will need to save a copy to their own desktop to do what they need to do. Does that mean, this copy is directly linked to the back end tables as well? Does this newly saved front end file have access to make any changes to the back end tables through front end interface such as directly clicking on the linked tables and update the tables, or run a query on front end to change tables in back end?
Steven Chen Yes, when someone saves the front end file to their computer they will be saving a fully editable file where there is the possibility they mess with things. The good news is they can only mess with the version they saved an not the file you made for the front end. The bad news all the data in the back end can also be messed with, but really that's the whole point. You want them to be changing data that everyone else uses too.
Thank you for your reply!
Is there a way to limit the access, so that front end users can only run queries, forms, etc, but do not have the ability to change the structure of the tables or insert/update data in the tables?
We have another platform where the data is being entered by the users however the data integrity is awful, so I am planning to update access table after manually auditing the data after extracting them from the other platform, so the users could only run reports, but never any direct interactions with the data.
Steven Chen I suggest you watch my video on how to Secure the front end. th-cam.com/video/i1n7H686bWI/w-d-xo.html
thank you! i will watch this again. Maybe I didn't understand it first time through.
Hi steve, right now i'm using other's computer as guest, so i don't have access to c drive, steve can i use d drive and use it as server for now?
Helloo steve u there
+bijay rai Yes you can.
+
Programming thank you, i was waiting for ur answer, really thank you steve
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
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.
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.
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 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.
Hi Steve,
Thanks so much for your really helpful tutorial. Yourr presentation is what I have been searching for times to make my Access application a client-server based one.
I am developing a software that potentially contains over 2Gb of data in the near future. I am wonder if Access DB can accommodate that large space. Since I am addicted in programming Access, I really hesitate to change to other platform. Can you please help me on how to deal with this matter?
Your sincerely!
Cuong Vo
+Tuấn Cường You can move the data to SQL server but keep Access as your front end by using either linked tables, or using a DAO/ADO connection with VBA. I demonstrate how to do this in my advanced series.
Hi Steve, how can I contact you?
Sir, if I made an .exe of a database, should I split the database like in this video ? thank you
Yes.
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.
Hi again, can you do a video showing how we can have more than one table update at the same time ?
Mohamed Gibril not in a query.
I just want to know what is the most appropriate way to update several tables together (knowing that they should have the exact number of records)
Thank you, I am getting better and better at this mainly due to your videos!
Mohamed Gibril You could run 3 queries in succession.
I split the database and it works really good! thanks! and I want to ask how many fields should a table have?
Mohamed Gibril You can have as many fields as you want, but you should try to break down your data into different pieces.
Great videos! I'm hoping this tutorial could be the answer I'm looking for:
Numerous times in the past I have connected to a SQL server, linked tables into an Access DB, wrote queries and then pulled the query results into Excel over a DSN-less connection, that way end users can open the Excel file and refresh the data whenever they wish without needing to have Access or a DSN on their machine. However, a new client is using Microsoft Azure SQL database which I can connect to fine, link tables, write queries and pull into Excel fine, yet no end users can do the same, even though they have complete permissions to the Access db.. I've tried about 20 different things all to no avail, so I'm hoping splitting the dB will solve the issue, else I will have to go to the SQL server host and ask if they are somehow stopping this working. any ideas, have you come across this before?
I have not since I have not ever used Azure SQL with Access, but I would look into permissions and firewalls. Make sure the user has permission on the SQL Server, and that there isn't a firewall blocking the network traffic.
Programming thanks for the info. Yes I have looked into this, the firewall is open for all users as necessary. The SQL server administrators said it looks at our IP address, although all end users on the local network have the same public ip address so that shouldn't be the issue. I will try splitting the db and see if that helps. thanks.
very good thank you!
sir. you uploaded a great video . I taught enough from it. but I ask you that I want to use data base among the two city or country . I am in a different country and user are in other counties so how they can my data base be and fe . I made be and fe according your video plz tell me. simple how can use between two city. ......plz I am waiting a great and sincere respons. ....
You would need a VPN connection between the two networks. This is far more complex than I can explain in the comments. I suggest you talk to your local IT professionals to see how to set up a VPN.
One thing to try would be to host the "back end" on a cloud server. On the client workstation, map a network drive to a share on the remote server that contains the back end file. Then use the Linked Table Manger to link to the mapped drive. There are some tricky permissions details that will depend on what cloud provider you use. I set this up with Amazon EC2 and it was pretty easy.
this that which i m looking for
What I don't like about splitting is that if the back end is password protected you can open one of the system tables in the front end and see the actual password required to open the back end!
+Caribbean Man This is true, and why migrating to a SQL server is preferable.
Great!
I love your videos. They are very helpful.
Thank you!
Do you have a video on how to create a subform search with multiple dependent comboboxs? I have done the majority of the work but I cannot get the search part to work properly.
Watch my how to video on making a keyword search. You would just have to modify the WHERE clause in your query string to look for the values in each of your textboxes.
awesome thanks for your help! I am trying to watch all of your videos :) Do you also recommend any books ? especally one that would have a list of all the different command names and the action they perform? kinda like a dictionary.
+Josh Fraser I don't know of any good books because I never read them.
how can I send you my database for testing after I splited
+ayam niyazi I'm afraid you can't.
Am I the only one who finds it ludicrous that you can't update the ACCDE file with new forms or reports, etc. after you split your database natively within Access???? I have to buy "Tony's FE Updater" or some crap? That is shocking to me.
ACCDE files are meant to be uneditable. Your ACCDB file should be were you make your edits, then export the file as ACCDE so that it cannot be modified by the users.
Programming how do you export an updated accde file though? That's what I don't understand. Do you just act as if you're splitting the database again? Or is there an EXPORT option I'm not seeing somewhere?
Hi steve, can I save the database to dropbox?
sir .. respect fully say I have no time to go any where I start duty from 8 am to 12 o clock of night and I don't have off in a week I have just only computer and internet . if you tell me about vpn our you upload a video about use database between to city or country . we all will be too much thankful to you bro. or you send me any tutorial or video to my e-mail . plz sir this will be a great kindness for me and all others who want to know that.
I'm afraid I just don't have time to explain everything you would need to know. It involves network infrastructure, active directory, and much more. People spend years learning it. I cannot possibly explain it to you in a video or in the comments.
999 likes :)
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!
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.
nice video..i learned new things in your videos...keep it up!
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. 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.
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.
thumbs up for the most valuable tutorial. Already subscribed. Thanks a lot
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.
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
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?
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 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.
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.
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?
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?
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