Access to Access

[Ian_Johnston_1948]

I am a single user using OneDrive and 365. All of a sudden Access says I can't save edits because there is another user. There isn't. Occasionally the file locking file can be seen in Explorer but I can't delete it as "it is no longer in this location". Any thoughts?

[#2]

@Ian_Johnston_1948 

Well, you've banged into one of the reasons experienced Access developers do not try to run Access from OneDrive. 

There actually must be "another user" of some sort, whether that is you yourself in a different instance or truly someone else wandering along and finding the accdb.

However, that part is just speculation. The fact of the matter is that OneDrive doesn't support running accdbs from that location very well, and definitely not safely.

If you need to share the accdb with other users, there are other, much better ways to do so.

All production Access relational database applications should be split into a "Back End" containing only the tables and a "Front End" containing only the interface and logic objects: forms and reports, queries, and VBA or macros. The tables in the Back End, or BE, are linked to the Front End, or FE. 

The FE needs to be on the local computer, i.e. the computer or laptop you use. If others need to share the relational database application, give them their own copy of the FE. Of course, you the developer also keep a "master" copy of the FE for future maintenance and development.

Do not put the BE on OneDrive either. If it is to be shared, put it on a network in a folder for which all users have appropriate read/write/delete permissions.

With regard to that locking file. It's job is to keep track of which users currently have the accdb open for use. It is created when the first user of application starts and it is deleted when the last user exits.

There are already many technical explanations on the internet regarding the reasons why trying to run Access accdbs in OneDrive (or other "Cloud" locations) is a bad approach, so I'll leave searching out that information to you. 

[#3]

@George_Hepworth 

Well, that is very interesting and useful. Thank you. I am, I accept readily, an amateur. My application has been developed over the years as a glorified, single user calculator. I have run it with the internet disconnected and still had the same problem, which suggests my phantom user is on my disconnected machine. Where to now?

[#4]

Where to? Mostly, what I laid out already.

OneDrive works for other types of files which do not require the type of sharing that Access accdbs require. (As mentioned, there are several good articles around the internet on the technical reasons.) You are using it as a single user? Put it on the local computer. If you need to use it on different computers at different locations, you could put a copy in OneDrive to download at those other locations, but running it should always be done locally to make life less hassle.

You call it a "calculator", which implies storing data is not the main focus.

[#5]

No, it is manipulating data and presenting it in an appropriate form. It is, in principle, a very simple application.

I have disconnected the internet so it is now on a single, local computer but it problem remains. There are no locking or other hidden files.

Thank you for your patience.

[#6]

What problems, specifically, remain?

[#7]

Export all objects include forms,tables,queries and reports to a brand new access .accdb.

Or just copy origin.accdb to new_name.accdb.

Disable one driver,and try the new database.

[#8]

As of last night, exactly the same. I have tried copying everything into a new database, I have checked all the settings, I have turner the internet off, all to no avail. It is really odd. I'm beginning to suspect a cloud upgrade fault (desperate!). Is anyone else getting the same problems?

[#9]

Sorry, specifically: when I try to save an edit of a form, it says there is another user and so my changes will not be saved. I have tried deleting the lock file when it takes a longer than usual time to delete itself.

[#10]

The same: it will not save an edit, because it says there is another user - but it is not one that I'm aware of. Someone fiddling around in the cloud, maybe. I've tried everything the Microsoft assistant suggested until it asked me to change some scary looking software that is way out of my skill set.

[#11]

@Ian_Johnston_1948 

Weirdly, VBA edits can be saved.

[#12]

Please do not try to delete the lock file, i.e. the laccdb, except under one significant condition mentioned below. The lock file should be totally under the control of Access and deleting it with its corresponding accdb open would be a problem. The good news is that you should not even be able to delete it while its parent accdb is still open, so that's not highly likely.

Access creates it when the first user opens the accdb. Access closes it when the last active user closes it. That ensures when the shared accdb (in this case, that should be only the BE, by the way) is in use, Access can manage reads/writes to DATA. It also ensures that users can't change other objects like forms and VBA when more than one user has it open.

The only time I can see deleting an laccdb would be if the accdb crashed, or was closed abnormally, leaving the laccdb open with no parent accdb open. You might even have to reboot the computer to be able to delete it in that situation.

In other words, leave the laccdb alone except for dire problems.

[#13]

@Ian_Johnston_1948 

No, there is something else going on then.

If you can't save records, but can save changes to VBA, there is some other issue at work.

Can you show a screenshot of the error message saying you can not save changes to records?

[#14]

@George_Hepworth 

OK, I will leave well alone. 

[#15]

I will have to check it further but all of a sudden it appears to be working again. I suspect Microsoft.

[#16]

OK, now I can save a file edit but not a vba one! Someone is mucking around with it!

[#17]

@George_Hepworth 

[#18]

OK so now it's not working again.

[#19]

I can save records. Now I can't save changes to to designs of either objects or code. It seems to be changing by the minute!

[#20]

Now it is claiming the database is read only so it won't update! That is new.