Hello, I split our Access database with the expectation that I would be able to use Designer in accdb, while an end-user views the same object from accde. Unfortunately, I still receive a message that the object cannot be opened because it is opened exclusively by another user. Can you offer a solution? I've spent hours searching for an answer online with no luck.
Thank you,
TAra
Well, the idea when you split is:
You get a FE (front end),
and then you get a BE (back end data file).
Now, you place the compiled accDE FE on each work station.
Since they are (as you noted) using the accDE FE, then you are free to work away on the next great version.
However, the BE? Yes, if users are working and using the FE, it will lock out the BE from design (table changes).
Two solutions:
Schedule a time or after work, you have to make those changes to the BE (data only file).
Or,
Make a copy of the BE, copy local, or to another shared folder. That way you can make changes to that BE, but you need to "keep notes" or keep track of what you did. Since you eventually still have to open the BE production BE, and make the changes to the BE data file.
Of course, you are free to make changes to the FE part - since users each have a copy, and in most cases have a compiled accDE copy. So, your 100% free to make changes to the FE that's on your development computer.
However, the BE? No, you as a general rule can't make changes to the tables and table desings while it is in use. You have to schedule this time off work hours, or as noted, work on a copy of the BE, but then you need some notes or word doc in which you write down what you changed, and you have to remember/make those same change to the production BE when you have that off-hours time).
Now, if you can't for some reason make a change to the FE?
Well, this sounds lame, but re-boot your computer - you might have a stray copy of access running. (I susppose you could also exit access, and check the task manager - see if any stray copies of Access are running).
Now, of course if the FE is not actually on your computer - (very slow and very hard to work on the FE if it is NOT on your computer). And it also very possbile that someone else has the FE open - which again is a good reason why you want to edit local on your computer.
So, EVEN if the development, or the accDB on your computer is linked and connected to the production (live) data BE? You are still 100% free and should be able to change forms + code at will.
Now, of course any change to the tables structure(s) has to be done on the BE, and not the FE. And as noted, that you can't change at all while users are working away.
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Thank you, Albert.
I only have one small table built from within Access, and the dual access conflict did not involve modification of that table. The other tables are linked, and those weren't moved to the backend with the split. I wonder if I need to manually move them somehow.
After splitting the db on our shared drive using the built-in wizard, I made a copy of the accdb file & saved it as accde on my desktop. I opened an object within the accde on my desktop & then attempted to open the same object in accdb on the shared drive but receive the message that it's already being used exclusively by another user.
I made another accde copy & moved it to a different folder within the shared drive. I am unable to open the same object in both accde files, as well. Client Settings are set to shared using legacy encryption for each file.
I'm obviously missing something, but I don't know what. Updating our tables doesn't take long, so I can ask the one other user (my CEO) to exit while I do that once a month or so. My biggest challenge is that I still have much work to do in terms of building out new queries and reports and have so far been unsuccessful in setting up the structure to allow for that.
I greatly appreciate your guidance.
Tara
Well, as a genreal rule, you can't make any changes to a accDE. (that is quite much the whole idea).
A accDE is a compiled application. So all of the forms code, and design parts are stripped out. The same goes for VBA code - it is all removed.
AS a result, then no real changes can be made to the accDE. You can modify the sql save queries, but not much else. And what use is modifying the accDE when it going to be over-written on your next great update.
So, the accDE is your "final" deployed product - as such, there is no practial means to update that accDE once deployed. And worse, if you could? Well, you no doubt over time will re-deploy and roll out a brand new accDE to each desktop, and this can occur quite a few times during development.
So, you can't modify or change anything much in a compiled accDE.
This also means some caution is required here. You need the "source" or so called accDB part to make changes.
We often see posts here that some company is running a Access application. But, they note that they can't find the accDB, and only have the accDE. This is a big problem, since then it means the source file to build the application is missing!!! -- So, the accDB part is the developer and source code part.
The accDE part? It is just a application part, and is only for deployment to each work station - you can't modify that accDE. (and it going to get over written when you deploy v-next anyway).
So like a word, or pdf file that is open? They have to close it before you can give them a fresh copy.
So, you are developing. When done, you create the new accDE. The user(s) are informed that a new version is available. They have to exit, and now go get or copy the new accDE version to their work stations.
You can even I suppose write some code to check for a new version. But the idea is like giving users a new word document, or pdf document. They have to exit that document first, and then the file can be simple over written.
So you are at least now free to modify, test, develop as much as you want. But come deployment time for your next great software release? Then users have to exit the accDE they are running, and copy OVER the accDE with the new one you gave them.
However, since the system is split, then no data gets overwritten, since it just a FE (front end) and not the data part.
So, as above shows, there is no reason as to why you should not be free to create, work on, change, and add new features to this FE, since no other users will be using that FE, or even have a copy of it. Only when you ready for your next great software release? You create the accDE, and that new program is then distributed to each users work station.
Thanks again for your time, Albert. Everything you've said aligns with my understanding of the functionality, but it is not working in that way for me, and I still don't know why. I did not try to edit the accde. I was only trying to view the same object in two different accde files but am prompted that it's been opened exclusively by another user. I have the files titled differently and in different locations. I tested the two accde files just to see what would happen, but my primary objective is to be able to edit the accdb while the accde is in use, but I have the same problem with the accde being in exclusive use.
Tara
Tara:
You can also use my free Auto Update enabling tool to enable auto updating. What that means is that you would place the new file in a specific file folder location accessible to everyone and, after the initial placing of a copy of it on their workstation, you can then make a change in the front end file, change the version number in the front end and backend, compile your ACCDE file and place it in the master location. Then the next time they open the front end, it will tell them it is out of date, it will delete itself and it will auto download the new file and reopen automatically for them.
That tool is located here:
https://btabdevelopment.com/free-access-tools/
Bob Larson
Access MVP (2008, 2009, 2011)
So, on each work station, the location of that FE should be the same, and the name of the file should not change either. If the accDE is not being placed on each work station then this will work fine.
However, if you trying to use a shared folder for running the FE, then this will not work.
So do NOT use a shared folder for running the FE. You MUST place the FE on each workstation. The shared folder for the FE is only for distribution. But, often users will start clicking on and running that FE on the shared folder - and hence it is locked!!!
Since each user has (should have) their own copy, then a accDE you decide make or have can't be locked by anyone else then you.
if you breaking the above rule (allowing users to run FE from that shared folder), then this setup will fall down, since the whole idea here is with each work station having their own accDE, then even the new update you deploy to the shared folder can't be locked, since all users always run their own copy on each workstation.
Now, often we have found that some users just start clicking on and running the FE from that folder. You have to take steps to NOT allow that to occur. As noted, the best approach is to setup some automated way of updating each user or provide them a shortcut that is in fact some kind of batch file that first copies the FE to their local machine and then launches it.
Some accDE you just made can't be locked by your next door neighbors computer, so some big detail is missing here.
Thank you, Albert. It sounds like I have a logistical issue with my file locations. After I've had opportunity to try again, I'll update you.
I greatly appreciate all of your help!
Tara
Hello Again Albert,
I believe my problem was failure to move the accde to a local drive; however, I receive a message that the file must be converted to 32 bit. From what I read, accde files only work in 32 bit. My IT attempted to download the 32 bit version to my PC, but it requires the 62 bit version to be uninstalled. Does this sound like the right solution?
Thank you,
Tara
This is a separate issue/problem of deployment - not that of being able to work and develop your application while others are using it.
The ONE issue of using a accDE that can bite you?
You have to match the work stations version of office in regards to x32 bits, or x64 bits.
In other words, if all workstations use office x64, then you MUST create the accDE with office x64.
And if all workstations are using office x32 bits, then you MUST crete the accDE with office x32.
This is perhaps "one" issue of using a accDE. You can get around this by distribution of a accDB, but that means users will be able to make changes to your application, and mess around.
In some cases, the ability of users to change form designs etc., mess with code etc. might not be a issue, but in most cases you don't want that to occur.
So, it is VERY doubtful that the company has a "mix" of office x32 installs, and that of x64. However, in some cases a company might be in the process of rolling out a new version - and thus you have to deal with that mix for some time until everyone is upgraded to the same bit size of office. In that case, the you will need two versions of Access to create the accDB file. (and as a general rule, you can't run both x32 and x64 bit versions on ONE computer. (so you need a VM, or two machines to manage this issue during the transition.
But, yes, you need the same "bit" size version of office to create the accDE file as to what each work station is running. The data BE part don't matter, but the compiled accDE FE does.
5 people are following this question.
