Results 1 to 6 of 6
  1. #1
    Lynn Cohen is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    30

    Question Split database - unexpected behavior

    Non-programmer here.

    I manage a split Access 2013 database. The back-end file and a front-end file live on a shared drive. (Users call these files "master files.") Users have their front-end files in folders in their home drives. (Users call these "local copies.") Users can see the tables that the back-end holds, by selecting "tables" in the navigation pane, but can open them only in "read-only" mode. The shared drive and home drives are backed up nightly.

    As the db manager, I'm the only person who works in the back-end file, and also the only person who opens the front-end file on the shared drive.

    Because it fits the nature of the users' work, we have one set of front-end and back-end files for each calendar year. Currently users are working in the "2016 database" and the "2017 database." This structure has worked without a hitch for about 12 years now.

    About a month ago, I discovered some anomalies in the database function.



    1. When trying to open those tables from the "local copy" (front-end file), the database does not give the warning that the tables are "read-only."
    2. I can in fact open the tables via the navigation pane in the front end, and have full "write" privileges.
    3. I deleted three queries from the "2016 database" and discovered they had been deleted from the "2017 database" as well. The front-end and back-end for both "2016" and "2017" were linked appropriately.


    I have talked with my networking group to see if perhaps any system-wide updates were made that might have caused this unexpected behavior. The investigated, and found that not to be the case. I hid the tables in the user's "local copies" as it seemed to be the only security measure that I - a non-programmer - could take.

    I'm stumped and so are my network folks. Any ideas?

    Thanks,
    Lyn

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    in response to your specific points
    1. Opening the tables will not produce the 'read-only' warning, you only get that when you go to the design view of the table
    2. Opening the tables and editing the data is not a function of a front end database, by default a user can edit data on a front end. I do not even know if you restricted access on the back end to read only for your users if you could block them from updating records to any of the tables, you might be able to but I doubt it.
    3. Without knowing what code is running when you open your '2016' version vs your '2017' version (i.e. is it the same database just with different filtering options applied when the database is opened for instance) I can't comment on this part but it sounds very much like it's the same front end with different filtering applied if you delete objects from 'one' and it happens in 'both' front ends.

    If you want to prevent your users from editing your data at all you would have to restrict it in your user interface or right clicking on the 'front end' database and checking the 'read only' box in the properties window. That should prevent your users from doing anything but looking at data.

  3. #3
    Lynn Cohen is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    30
    Thanks for your speedy reply.

    1. For the past 12 years (since we first split this database) when a user tried to open the tables from the front-end file - not in design view , just by double-clicking on the table name in the Navigation Pane - the user would get the "this is read-only" warning message. Now, we don't.

    2. Yes, of course a user can edit data in the front-end file, and the users are set up with various input forms to do that. The problem is, a user shouldn't be able to open tables in "write" mode from the front-end file, and now they can.

    3. No special filtering has been applied to the '2016' vs the '2017' files. The '2017' files are a copy of the front-end and back-end of the '2016' files. Those copies were made back in June, and the front-end and back-end of the '2017' files were linked to each other. That's the first thing I confirmed when I observed the problem with deleting queries.

    I didn't say I'm interested in preventing users from editing data. I'm interested in find out why they now have permissions to tables that they didn't have, before about a month ago. The only real security this organization applies to this database is, the "master files" live on a shared drive and our SOP is that no one but me works directly in them. So if users can access the tables with "write" perms - a situation that wasn't the case until mid-August - then we've got a problem.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Try what I suggested, right click the front end and make it read only and see what happens, see if you can alter data etc. If you can't that'll be the simple solution, without being familiar with your network etc there is no use me trying to guess what has changed I can only try to help you get back to the 'protection' you're looking for.

  5. #5
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I agree with rpeare and your post has me wondering also:

    a. how did you make the tables read only? ... please explain that point. If you are not able to - and that is okay - then going forward I would recommend that you disallow the navigation pane from being available to end users in order to keep them out of tables. If you wish to present tables in a read only format - set up a form with no edit option - this is the correct design. Whatever method was set up before isn't ideal - even though it worked for you for a long time.

    b. Your item 3 is not possible [ "deleted three queries from the "2016 database" and discovered they had been deleted from the "2017 database" as well"]. You have some sort of cockpit error. Deleting queries in 1 front end has no affect whatsoever in another front end, regardless of the year, as they are totally separate files. So you have some sort of mix up that is not understood or being explained to us. Perhaps the 2017 was actually a short cut to the 2016 front end??

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just curious, but if all users access the Back End in read only mode--how do back end tables get their data?
    Can you tell us more of the subject matter and overall processes?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-29-2015, 01:17 PM
  2. Unexpected error from external database driver (1)
    By Icon in forum Import/Export Data
    Replies: 3
    Last Post: 05-15-2015, 12:40 PM
  3. Replies: 4
    Last Post: 11-27-2013, 09:51 AM
  4. Replies: 2
    Last Post: 09-25-2013, 12:40 PM
  5. Replies: 3
    Last Post: 12-26-2011, 10:45 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums