none
Access database where linked tables are all read only on only one workstation RRS feed

  • Question

  • I'm working on a new-to-me database which is in production on machines that have Access 2010. The file format is Access 2000. The linked tables are to an Access mdb in the same folder. On my workstation, all linked tables are read-only. The new icon is missing from datasheet view and one can't edit the records.

    I've tried quite a few experiments and haven't gotten anywhere. Nothing useful via google so far.

    All tables are native access and all have a primary key. The app is used by many other people; both standalone dev copies (linked to dev data) and in production over a network. It's just this one workstation that seems to have the issue. This is a run of the mill front end back end split Access mdb. All workstations involved are windows 7. The workstation with the issue is used for many other mdb and accdb applications.

    A new table in data, when linked to, becomes read only in UI.

    A new tables in a new data mdb, when linked to, becomes read only in UI.

    I imported all of the front end mdb into a new mdb; same issue. When importing to a new mdb, it failed to import, due to activex object failing. There are no activex controls in this mdb that I'm aware of; that anyone is aware of (no refs to such).

    Both files are in Access 2000 file format. I converted it to Access 2003 file format; it partially fails, as it can't perform the last step where it renames database.mdb as the real db name; and the resultant file has the same issue.

    I ran decompile on it - didn't help.

    It complies and compacts normally. Very strange! Normally you can get read only links when there is no pk that's not the issue here. Local tables are not read only.

    Anyone seen anything like this?

    Tuesday, August 1, 2017 11:55 PM

All replies

  • Hi, Just as an experiment, can you put a local copy of the BE with the FE and link the FE to the local BE and see if it is still read only?
    Wednesday, August 2, 2017 12:00 AM
  • Yes, that is actually the primary scenario I'm trying to solve. In production, it goes over a network (everyone using one copy, don't ask me how it works as well as it does, I just got here <g>). My workstation is local to local. The dev that gave me the files I have works local to local.

    Shift key etc so no code running at startup.

    The file may be partially corrupted, the convert file format and import to new db operations don't go smoothly. But it seems odd that it only gets weird on my workstation. I'll try another workstation to see if has the same issue.

    And thanks for the reply.

    Wednesday, August 2, 2017 12:07 AM
  • Hi,

    Good idea to isolate your workstation. Let us know what happens when you try it on a different computer. Good luck!

    Wednesday, August 2, 2017 2:24 PM
  • Unexpectedly, relinking and opening the same database on a workstation with Access 2013 results in edit enabled linked tables.

    The original workstation is where I do all of my Access 2010 work. It it peculiar that something about it would mess with an Access 2010 db in Access 2000 file format, and an Access 2013 workstation wouldn't.

    I'll post back if I figure out any more about why this is playing out the way it is.


    • Edited by rusticloud Wednesday, August 2, 2017 3:45 PM
    Wednesday, August 2, 2017 3:27 PM
  • Congratulations! Glad to hear you're making good progress. Good luck with your project.
    Wednesday, August 2, 2017 3:56 PM
  • I'll reopen this subject as the issue has not gone away.

    I decided to try a new vm with windows 7 and office 2010. For a few days there was no read only bug with this application. But now it's come back. I work on the db for 5 min, and it becomes read only. I reboot the vm and for another x minutes it's read-write to the linked data file. This is fully patched windows 7 pro and office 2010.

    I have a notebook with newly installed and up to date windows 7 pro and office 2010. So far it has never shown this behavior with the same db.

    I can't imagine what is making this db so dodgy. I'm posting again in case some new eyes will read this thread and have some suggtestions.

    Wednesday, August 9, 2017 8:48 PM
  • Have you tried performing a C&R on the db? If so, how about exporting all objects into a brand new database file?

    Just a thought...

    Wednesday, August 9, 2017 8:56 PM
  • Thanks for the input again, yes tried those, and many other things, all listed in the original posting. I wouldn't expect you to have re-read all of that. Decompile, export to new, convert to A2003, there are details to all of those attempts. Very strange issue. Works on notebook (not vm) works on all production workstations, works in Access 2013.
    Wednesday, August 9, 2017 9:01 PM
  • Ah, okay. Sorry to repeat. I'll let you know if I think of anything else you may have not tried yet. Good luck!
    Wednesday, August 9, 2017 9:03 PM
  • No problem repeating, long set of messages and factors. I appreciate your wanting to help out.
    Wednesday, August 9, 2017 9:22 PM
  • Hi rusticloud,

    Have you checked about permission? Did you have the permission to edit the mdb field? If you create a new mdb in the same path and get full permission for testing, what result will you get?

    Best Regards,

    Terry

    Monday, August 14, 2017 9:54 AM
  • Terry, there are many other Access dbs in the same folder, linking to local access dbs, and they all behave normally.

    Update on one detail - the db does read only when used with Access 2013. I prev reported that it worked fine in Access 2013, but that didn't last.

    The only pc that reliably runs it is the one machine that has Access 2010 installed on the pc directly, and not running in a vm (virtualbox). I have tons of Access 2010 dbs that run in the vms, so it's another fact that doesn't make sense.

    It's such a strange issue - I can open the database with shift key; first table is edit enabled and sometimes others are too. If I close it and reopen with shift key, all tables are read only. It takes a vm restart to solve it then.

    Saturday, August 26, 2017 3:08 AM
  • Hi rusticloud,

    Could you help to make clear below points?

    >> all linked tables are read-only. The new icon is missing from datasheet view and one can't edit the records.

    Could you open the Access mdb back end and check whether you could add or edit the records?

    >> A new table in data, when linked to, becomes read only in UI.

    >> A new tables in a new data mdb, when linked to, becomes read only in UI.

    Are the FE and BE in different workstation or same workstation? If you create a new database in non-worked workstation, split it, and will it be read-only?

    >> Both files are in Access 2000 file format. I converted it to Access 2003 file format

    What do you mean by both file are Access 2000? Do you want you create database with 2000 file format by Access 2010? Could you create database with Access 2010 format to check whether this issue exist?

    In addition, could you try to access the mdb back end file from non-worked workstation to check whether you have permission to edit the records?

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 30, 2017 9:10 AM
  • I wrote about all of that before. Editing the data file is ok. Both files same workstation. Access 2000 file format but changing that to a later version does not help.

    Your last question doesn't make any sense.

    New dbs don't matter it's just this one db.

    Thursday, August 31, 2017 5:37 AM
  • Hi rusticloud,

    It seems this issue is related with this specific db.

    For linked table, did you create any Form based on these linked table?

    Before editing the linked tables, will this issue exist if you close all other Opened Forms and tables?

    While the linked table is read-only, will the table records be editable from other computers?

    Best Regards,

    Edward 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 4, 2017 7:47 AM
  • This issue still persists. Posting again here in case someone has run into this issue since.
    Saturday, June 2, 2018 5:49 PM
  • In such cases, one area to look at is permissions.

     

    For example, if I walk up to a machine plug in a USB jump drive, and then from my desktop copy the file to say some shared folder on the network, we can often find that no one else can open or use that file? The reason of course is once that file is place on that workstation, then it can inherit permissions that are special to that folder or user on that machine. This issue “usually” occurs on “domain” networks (as opposed to say workgroups – but it not limited to).

     

    What this means is that the file in question has some permissions that “stay” with the file. You can copy it around on that network, but those granted permissions stick and stay with that file.

     

    You also should be using an accDB file format. Not clear why one is still messing with a 2000 or 2003 format file (that is like, 15 or 18 years old now – a really flag in terms of introducing additional problems here).

     

    I mean what other applications are you using today with 18 year old file formats? Again, this should be addressed. The only reason I can think of to use mdb is if the mdb file has user level security in use. (I think 2010, or 2013 is the last version that supports that option anyway).

     

    Anyway, back to the issue at hand.

     

    The other often reason for “read only” is that the folder the back end file is placed under does not have full delete and cerate rights in that folder.

     

    When you open an access file (or a front end that links to the data file), the Access attempts to create a locking file. If that locking file cannot be created, then access defaults to read only.

     

    Now here is the kicker part. If someone else has the back end file opened (say by links from their front end), then “often” that locking file assumes rights granted to that one user! The result is then other users (or perhaps one lame user + workstation that has “different” rights) now will ALSO find that the database is read only (because they don’t have rights or permissions to that locking file). So that locking file can assume rights and permissions that been granted to the FIRST user who opened the back end file. The result is other users get the  access “default” or reduced operation of read only (they also need rights and permissions to that locking file).

     

    The other often reason is even if no one else has opened the file, but they don’t have create rights to that back end folder. So they can read the mdb file, but they don’t have sufficient permissions to create files in that folder.

     

    Another issue for some problem workstations is they have two network setup. (So they have workgroup and say “domain” setup). Access has “some” issues with this setup, and I seen some workstations flip to the workgroup permissions – the result is no use of the back end. This can be often resolved by using the ip address of the server folder as opposed to an unc path.

     

    So a few things:

    Make sure everyone has full rights to that folder where the back end is. Not just read/write, but also create and delete permissions. (The delete is not required, but until everything is working, then yes, given everyone FULL rights to that back end folder – including delete and create file rights). (Often I seen the network admin remove delete rights – and thus the locking file can’t be removed, but worse it gets stuck with some particular user’s permissions – and thus everyone now becomes read only when using Access.

     

    Last but not least:

    You should also attempt to figure why you getting some ActiveX error during an import. I would check the VBA references – see what is set, and remove any and all non-required references. Make sure the code compiles (vba – debug->compile). If no compile errors exist, then you should be able to important to a fresh new accDB file.

     

    And check the mdb file for being “blocked” by windows. When you download, or even just “copy” a file from some folder, often due to workstation permissions, you find you need to right click on the file and un-block it to “set” correct window permissions.

     

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

     

    Sunday, June 3, 2018 2:32 AM
  • It's not any of those things. I've been at this since 1995 so I've been around the block many times. This is very very outside the norm. Perms wise, the domain that it normally runs on is far distant from my developer environment, which is a completely unrelated AD domain. It runs correctly on exactly on pc on my domain; it happens to be a stand alone pc; all other test pcs are virtual pcs. But all use the same login, same unrelated domain. At first I thought it was just one pc that had the issue but since I've found it's just the opposite, one allows edits and the others don't.

    I can open a linked table, and it will show the "new" gold star at the bottom of the datasheet, and as soon as I try to edit a row it'll vanish and the table goes read only.

    I think I mentioned somewhere above that this db is inherited and is due for a massive overhaul. It has terrible RI and my instinct is that Jet/ACE, on some machines, gets conservative with allowing edits because of the loose RI. But why it works on site, and why on on a hit and miss basis here, I don't get it. I've never seen any database behave like this and I have seen hundreds of them.

    There is also a good chance that there is some corruption built in to this database that tosses this wrench into the works. There are othe mis behaviors which are off the map to me that this db comes up with. I've tried all of the usual fixes for corruption and no help.

    I'm mostly hunting for someone that has more perspective on what might trigger jet/ace to go to read only. And I do appreciate your input, this is an extremely odd case.

    Sunday, June 3, 2018 3:44 AM
  • All Good!

    I seen your name around for years - so yes, much assumed you bee around the block, and thus this issue was not going to be "simple" or "obvious" to fix!

    However, I much shared the above since it was/is the ONLY time I seen such issues.

    One more "longer" shot:

    I would create a new blank mdb with 2010, and then import all of the data from that mdb. You may have done this already, but if not give this idea a try.

    The reason is while 2010 "can" handle mdb files, that format is rather old, and the JET engine was updated to ACE - there might be some small "part" or "thing" in the mdb file that is causing this issue due to ACE not handling the mdb format 100% perfect. So a fresh file (and one created by 2010) may well help this. (and if possible move to accDB format).

    Keep in mind that Access does not need nor require the DAO 3.6 reference anymore - it uses it own built in DAO model that is now part of Access. The "long time" external DAO library reference is not required any more and it should be removed. (so in the FE, you  could/should remove the DAO reference if there is still one).

    Given your level of knowledge here, I suspect you are fishing, and hoping someone seen this - and I dare say you are in the "long shot" area of answers since anything obvious and even not so obvious has "long" been exhausted by you.

    I don't have anything beyond the above, but moving out the mdb file format would certainly be my first choice if this is a possible choice.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Sunday, June 3, 2018 6:21 PM
  • The reason is while 2010 "can" handle mdb files, that format is rather old, and the JET engine was updated to ACE - there might be some small "part" or "thing" in the mdb file that is causing this issue due to ACE not handling the mdb format 100% perfect. So a fresh file (and one created by 2010) may well help this. (and if possible move to accDB format).

    Hi rusticloud and Albert,

    About long shots...

    I am not convinced that the .mdb-databases as such are the problem. I still develop in A2003, because one of my most favorite clients has computers (but no budget) that won't accept Office2007 because the OS is too old.

    I have more than 100 applications running, from very simply to complex (more than 25 tables), while users have A2003 - A2016. They all run without any problem. I must admit that I do not use ribbons, because all needed functionality is build in on the spot where the users need it.

    And here is the long shot...

    When I continued my develop work on a new computer, I had a problem with Access after some time. I placed a thread in this forum, but I cannot find it anymore, and the search possibilities are below level in my opinion, unless you scroll through (almost) all threads. Nothing helped: Compact/repair, new installation of Access, new installation of Windows, and all of the common tricks. Finally I re-installed the operating systems, and all problems were solved.

    Could it help?

    Imb.

    Sunday, June 3, 2018 7:03 PM
  • Hi Imb

    In fact the first virtual machine that this read only stuff happened on, I thought, oh this vm and OS have been working hard for 5 years; must be breaking down. I spent a day setting up a new primary Office 2010 vm, OS and all, to replace it, and found the bad behavior of this mdb was just the same. These are all windows 7 pro machines at my location, one of which mostly behaves, but on site they're using windows 10.

    Yeah I don't have experience with Access 2003 actually falling down on the job either.

    I do know that this database is OLD. What I mean is it probably started out in Access 2003 and was later moved to Access 2010. And it may have started out as a clone of another db that was probably from the 90s, so it may have some old cruft buried in it somewhere.

    Albert you suggested the import to a new mdb and yes I tried that in my first week long fight with this thing, and the futility of which was the origin of this thread.

    But one thing that I did not know if that DAO is now built in to Access. Now that is a great thing to learn thank you for that.
    Sunday, June 3, 2018 9:07 PM