57. Splitting Our Database (Programming In Microsoft Access 2013) 🎓
HTML-код
- Опубликовано: 2 авг 2024
- A newer version of this series has started for Microsoft Access 2016 here:
• Microsoft Access 2016 ...
----------
Splitting Our Database in "Programming in Microsoft Access 2013". Hosted by Steve Bishop.
In this free beginner video tutorial series Steve will be going over Database Development in Access, creating the User Interface, and coding the backend of the application in Visual Basic for Applications (VBA).
Click here for the full playlist of "Programming in Access 2013"
• Programming in Microso...
Click here for the Work Files of this series:
github.com/Xipooo/Programming...
----------
Become a member today for only $5 a month!
/ @programmingmadeez
When you join, here’s what you’ll get:
* Your name in the credits
* A form to ask me for help on your project
* 10% off in the Teespring store (discount code)
----------
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: / @coffeewithsteve
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: ruclips.net/channel/UCeXAUvo5xxDY_b-lSknPC1A
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!
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.
@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.
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
You're a thorough, amazing teacher. 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.
+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: ruclips.net/video/DxRpo496w_c/видео.html
Thanks Steve. Will try out with your guides.... You d man!
Thank you for sharing this tutorial. Very helpful.
nice video..i learned new things in your videos...keep it up!
+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.
Thanks, You are awesome!
Amazing, thanks for the knowledge sharing
Thanks, this really helps a lot.
Perfect Explanation. Thank you
amazing video thanks a lot
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.
thumbs up for the most valuable tutorial. Already subscribed. Thanks a lot
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.
Love u dude. It worked.
Thanks! Excellent....
Thank you so much sir.
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?
very good thank you!
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! :-)
Great!
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.
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.
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!
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
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 vid
this that which i m looking for
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
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.
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.
Hi Steve, giving permission/access to multiple users the same process for a table and form?
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.
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
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
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.
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.
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!
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.
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
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. 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.
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
@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.
Nice job. Very clear and easy to follow
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
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.
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?
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 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.
Dear Mr steave
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?
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, 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.
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?
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.
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, 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.
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?
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, 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!
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?
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
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.
ok sir. plz give me some clues for vpn and where I can search and get knowledge of it. thanks
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.
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 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?
how can I avoid the topping of data?
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?
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
How is it safe when the user has to have access to the backend file location and the file itself?
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.
I have a quick question, you can still work in this db with multiple users at once?
Yes. Keep the back end file on the network somewhere, and distribute the front end to everyone who needs to use it.
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.
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.
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.
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.
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.
Can I secure and give permissions to UserTypes without creating an be/fe? Don't really have this option at my work.
Explain the process to share a accdb file converting front end to accdr file like special edition of the data base
Thank you!
I don't quite understand your question.
You have a back end file which resides on a share somewhere on your network while you distribute your front end to everyone.
Accdr is just a file extension which eliminates the ability to view the VBA. Just save your ACCDB FRONT END as an ACCDR file and distribute that instead if you want to prevent users from accessing the VBA.
That is my question how can I modify the accdr file and link it to my back end file
I explain a little better in this forum
Can you read it and help me please
www.accessforums.net/access/split-database-share-front-end-please-help-48185.html#post252676
Josue Lemus I think I made the same mistake June did in the thread. ACCDE is an executable with no access to the VBA, ACCDR should be just renamed to ACCDB and it should work.
Do we need to split our database if we have linked tables to sql server?
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.
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.
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. ruclips.net/video/i1n7H686bWI/видео.html
thank you! i will watch this again. Maybe I didn't understand it first time through.
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?
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.
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.
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.
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.
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.