Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Weird "Exclusive access' message problem.

    I recently reorganised the security setup for a client's system and what happens now is that whenever a user starts up, he/she gets the well-known message "You do not have exclusive acess . . . and may not be able to save your work." The fact that the message occurs at all is odd, but the fact that it occurs exactly eight times and then goes away is really odd.

    I did some searches on various forums and found that the message does occur in all sorts of funny ways and apparently causes a lot of angst. I collected a list of things to check and from what I can see, there is no reason why the message should occur at all. For the record, here are the relevant system features and settings.

    1. Both the front-end and back-end are shared databases.

    2. All permissions are assigned to groups, not individual users. There is a hierarchy of four groups; Owners, Admins, Managers and Users. Each user is assigned to an appropriate group and to all groups lower in the hierarchy. Access to objects is assigned to only one group at the appropriate level. I did have Owners and Admins allowed exclusive access, but removed this in the course of my investigations. It made no difference. Now, exclusive access is specified only in the command-lines of the scripts used to open the database.

    3. The backend has complete access by all users, but is hidden away and members of the Managers and Users groups can only view it through the front-end forms, as the database window is turned off, as are the various workarounds to beat this situation.

    4. Each user has his/her own copy of the front-end.

    5. No actual losses of data have been reported.

    I normally access my client's system from a distance, taking control of an office PC through rdp.exe. Quite often, I have to go through the motions of pushing another user off the PC, even though I know that everyone has gone home for the night and there is no one there. Similarly, after I have logged out, the daytime user has exactly the same problem. This makes me wonder if there is something left in the system (a registry entry, perhaps) which makes Access think that I am still around. However, this still does not explain why users without design permissions get the message, which is supposed only to alert a developer that changes may not be saved.

    Any advice will be gratefully received.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's a very simple solution to avoid ever getting the "...exclusive rights" error again. I've dealt with this error on many different network systems.

    http://www.dbforums.com/6274786-post19.html

    On network type systems where the mdb file is shared, so also is the *.ldb file being shared. The *.ldb file (like any file such as a Word Document, Excel File, etc..) can easily get corrupt when you have multiple users sharing that file (ie. over 3 users.) *.ldb files often get corrupted when a user (multi-user environment) has the mdb file open and minized for a lengthy period of time (ie. 4 hours.) This often "locks" the *.ldb file causing problems for other users trying to open the mdb file (hence the error.) Another cause is the user not having WRITE as well as READ permissions to the folder. Or they lose network connection or their connection is flaky. I think you mention them above.

    Thus, having each user in their own frontend mdb file (and *.ldb file) resolves the problem. To do this, a vbs script was created and is posted in the code bank in the above link. The script simply grabs the user's loginID, clones the "source" mdb file with the loginID, and then opens the cloned file (about 5-10 lines of code - you just need to change the LUName to ="location_where_mdb_file_is_located".) The user would open the mdb file via this script every time they wanted to get into it (it automatically fixes bad *.ldb files when cloning.) (you can also move the mdb file and simply edit the vbs script to the new location.)

    When the user opens the mdb file via the vbs script in the example, it first clones a "source" mdb file (or mde file) with the users login name (that it automatically grabs) and then launches the cloned mdb file. This essentially elimates any "sharing" of any *.ldb file (and also increases performance). If a user does corrupt the *.ldb file (ie. they lose their network connection), it doesn't affect any of the other users and the mdb will 're-create' itself when the user simply runs the *.vbs script again. There are many other advantages to having users open a *.vbs script versus the mdb itself (the actual code is only 6-10 lines long, the rest are comments.) The big advantage is being able to copy new code at any time without forcing everyone out (you just send them an email that you copied new code and if they want to see it, they should close out and get back in again (using the vbs script.)) I can also see when every specific user last opened the mdb file along with if they are currently in it (there's an *.ldb file with their loginID.)

    I'll also put in code so the "source" mdb file cannot be loaded directly should a user try to open it and avoid the vbs script (the code automatically closes the source and launches the vbs script.)

    I have never had a "locked by another user" or "exclusive access" type of error since I started using this script 10 years ago for all my mult-user mdb files.
    Last edited by pkstormy; 08-23-09 at 21:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Exclusive access and .ldb files

    Many thanks for that, pkstormy. I think we are all converging towards the same understanding. The way my clients system is set up is that there are a number of copies of the frontend .mdb file set up (numbered 1, 2, 3, etc.), each linked to the single backend. There are multiple PCs (numbered 1, 2, 3, etc.), each of which has a desktop icon, pointing to the corresponding copy of the .mdb file. The script in the icon identifies the user as User1, User2, User3, etc. and applies the appropriate password. I hasten to add that the actual user is identified by their system login ID and they are recorded as having opened that particular route to the database. The front-end is used for a call-centre and the things that the user can do are strictly limited by VBA code and their transactions are audited. My question on this is; would this setup equate to your proposal to clone the front-end program every time, or is there something missing from our arrangement ?
    I am particularly interested in your remark about the .ldb file, which I think might be at the root of my problem, as I have noticed that this file (a separate one for each user) is often still open after the user exits. When I try to delete the file, I get the message that I cannot do this, because another user is still logged in. This might explain why ordinary users get the message that should only be of interest to a high-ranking developer. It might also explain why the message appears multiple times, because it is aggregating the number of previous sessions the developer was logged in.Is there a way that I can force deletion of this file and thereby refresh it on the next login session ?

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    As a follow-up to my previous post, I can report that I downloaded my client's entire database (front-end, back-end and security file) onto my system, set it up exactly as it is on his system (down to the disk drive) and it worked perfectly ! Not a message in sight ! A contributor to another thread suggested that there might have been a copy of Access running as a process and misleading the application, but I have checked it out and there is nothing there. Also the .ldb files which were hanging about and might have been corrupted have miraculously disappeared. It would, therefore, appear to be a system problem which I might hand over to the sysadmin. Any suggestions as to where any unwanted stuff could hang around (registry, perhaps) would be appreciated. Also I wondered whether there could have been a bit of malware at work. Any ideas, you brilliant IT people out there ?

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Jim,

    Regarding front-ends...

    I've done it both ways 1. A separate front-end is on each user's desktop and 2. The front-end is on the network drive and run via a script. I personally prefer the later but here's some advantages of each.

    Here's a couple reasons why I use each way:
    1. A separate front-end is on each user's desktop...

    This works great when you have a situation where the network is slow and you have some users connecting via a VPN connection (since VPN eliminates connecting to a network drive on slow networks.)

    One important note here though is that I DON'T have my backend tables in an MSAccess mdb file on the network (since you then still have the "bottle-neck" of connecting to the network drive which is often the case for many external users - ie. it could be a poor/unsteady user connection (dial-ups), or even a server which has limited memory, harddisk space, unsteady up-time, other connection bottleneck, etc...) Finding the bottlenecks is what is key. This is of course, assuming that the front-end mdb forms are not designed so they take a heavy toll on the connection (ie. poorly designed forms which have multiple, multiple recordsets upon recordsets and subforms within subforms and all records are returned when the form opens (which has to update all the comboboxes, listboxes, subforms, etc..)...etc....) I personally try to keep my forms simple with only 1 or 2 subforms and 1 or 2 listboxes. I also like to return only 1 recordset (ie. criteria where it returns only 1 of the customerID's) to the form (ie. customer where the user first opens a "find" or other form to navigate to that 1 specific person.) This prevents "overload" type of problems on a form and it's subforms and coding, makes the form load fast, and prevents other recordset issues with multiple users.

    There are a couple of solutions to the problem of poor user-connection:
    1. With the front-end on each user's computer, put the backend tables on SQL Server and link them into the front-end mdb (thus eliminating "any" connection to the network drive and you have a direct connection to the data and are only limited by the user's connection (ie. dial-ups are killers) and the actual SQL Server drive's capabilities (which is hopefully a stand-alone computer with a good amount of memory for large datasets.) BUT you may still be limited to any connection the user has to make to get to the SQL Server (ie. if they need to go through a citrix connection, citrix is sometimes tricky for network admins and could cause problems if not a steady connection.) I like using linked SQL Server tables because I can then easily utilize queries and don't have to "code" everything in (such as ADP projects.) Another option (high level) is to write stored-procedures for everything and write code to go directly to SQL Server and bypass ODBC completely. I don't usually do this because it's more time-consuming writing all the stored procedures (or triggers.) I want the quickest way to create and update applications which is to first design the tables in MSAccess (and get all the relationships correct) and then upsize them to SQL Server (and double-check.) I also like SQL Server because I can maintain permissions easily on it without having to worry about an mdw file (since users cant get to code in an mde file.) MDW files became to much of a hassle to deal with and were often more problems then what they were worth. I also utilize a "permissions" type table system as found in the code bank (it's the getuser and permissions example in the 2nd to last page I think.) I'll try to find the link.

    Important Note: If some user's have dial-up and you're dealing with recordsets in the millions, I'd highly consider designing your front-end mdb's to use unbound forms (ie. forms not bound to recordsets with functions to retrieve, write, delete records to the tables driven by buttons on the form.) This eliminates data problems with user "hiccups" in their connection and also provides extremely fast working front-ends (as long as you don't over-utilize comboboxes/listboxes on forms and make their rowsources unbound to a recordset.) You have to keep in mind that EVERY field which has a connection to the data source requires more loading time. If you have dlookup commands or functions returning values on the form (in the control source/default value for the field or in code), this requires a connection time to the data source.

    If data is important, it should live on some sort of server (by itself on it's own computer and not bound to down-time due to other network issues.) I personally like to use SQL Server and simply link the tables into the mdb front-end. Then it's a matter of creating an ODBC Connection for each user (which there's code in the code-bank to automatically do this when you open the mdb.) Keep in mind that if "ANY" table changes are done on any of the tables in SQL Server (EVEN on MSAccess backend mdbs), the tables MUST be refreshed in each user's front-end (or you could have hanging *.ldb files!) Hence the code in the code-bank to automatically create and update the ODBC Connection for the user when they open the mdb file. Note: The user must have the ability on their computer to create new ODBC connections (or they must be done manually). Some network admins remove this ability for users.

    If your front-ends are on each user's desktop and your backend is in an mdb on the network, some-times there is a good reason for this (but most of the time it's due to sufficient permissions system couldn't be implemented in a "general" mdb front-end file or there were constant problems with "locked by another user" or the "explicit" errors you mentioned. Honestly, I've only found VPN connections a good reason for separate front-ends and often see scattered front-ends as a work-around for the errors (which make it more complicated to keep the code updated.)

    Getting back to the front-end on each user's desktop, you now have to look at how you're going to update coding changes (if it's a database which needs periodical updates which is usually the case.) With front-ends scattered throughout several laptops, desktops, etc... For SQL Server, I've found the best way to keep the front-ends updated is to have 2 "version" tables in the mdb files. One is a SQL Server table with a VersionDate field. The other is a local table in the front-end with a VersionDate field. I wrote some code to compare the 2 dates on startup, if they don't match, it prompts the user to download new "source" code from the server (which is a few lines of vba code.) They keep getting the prompt until they select Yes to copy the new source code (which has a "local" and SQL Server table that has matching version dates.) I guess you could also use numbers or some other type system but this is what I found worked the best.

    2. VBS Script...

    I like the vbs script for internal users (and good external connections) since it's easy to update the code. Again, I utilize SQL Server for all my data tables. Some of our users connect in via citrix and then navigate to the network drive and run the vbs script (or open a neat menu system I designed to run any of the vbs scripts.) I have a "Development" folder and "Deployment" folder. I make my code changes in the Development folder, create a new mde file, and then simply copy the mde file to the "Deployment" folder (and then send an email to users to simply close out of the program and open it up again to see the new changes.)

    I also like the other benefits of the vbs script which you can read about in the code-bank. I've never had an issue with anyone having the ability to "hack" or break the loginID it retrieves. I have also used the computername as well as the username.

    As a last note, I haven't had any problems with the systeminfo and forminfo modules I utilize in all my examples in the code-bank regardless of what back-end system the network is (ie. Novell, Linux, Unix). I have though noticed problems with other modules and dll files (such as the mousewheel code.) I try to avoid these to prevent the hassles of trying to update these in the case of separate front-ends.

    Regarding the hanging *.ldb file...
    On a network system, an *.ldb file can take a few seconds to disappear once everyone has closed out (or you MUST hit the F5 key to refresh the folder which is often the case.) On a stand-alone for only 1 specific user on their desktop, it should go away right away. It could take a few seconds if other things are going on (ie. antivirus, anti-spyware, other process, etc..) UNLESS again, some other mdb file has tables linked into it from that mdb file or some user has that mdb file open. If it went away after a night, there is the chance that the backup routines could have fixed it (or most often, a user who had the mdb open or has linked tables to this mdb finally "logged off" or disconnected. (user's are the biggest culprits for hanging *.ldb files.) I've rarely been able to delete an *.ldb files (but did find some network admins who could misteriously delete them without problems.) I would highly recommend compacting/repairing the mdb (or use /decompile parameter when opening) or use the jetutil over trying to delete the *.ldb file. These "should" make it go away. I can't think of another reason that a specific user would have a hanging *.ldb for their frontend unless there's coding or other things going on (I'd really have to see the entire environment.) You may want to run a few tests having the user open/close/open/close, compact/repair, etc... Sorry - I can't help much here.

    Note: You can also set an mdb file to compact/repair on close (it's in the options I think). I wouldn't recommend doing this though. Most users don't have the patiences to wait while it does this and will forcably close it somehow (even pushing their power button off.)

    Hope this helps.
    Last edited by pkstormy; 08-28-09 at 12:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    (I ran out of allowable characters in my previous post)

    You're quote worries me:
    "There are multiple PCs (numbered 1, 2, 3, etc.), each of which has a desktop icon, pointing to the corresponding copy of the .mdb file. The script in the icon identifies the user as User1, User2, User3, etc. and applies the appropriate password."

    I'm assuming by this the password is "embedded" in the icon/shortcut with the username? (hopefully not) The password should never be visible by anyone who could just click on the properties of the icon and see the password? That would be a big IT Auditing problem (I once did some IT auditing for the city). I'm guessing the icon/shortcut is designed to "push" the username to the mdw file with their password? (again, hopefully the actual password is NOT visible in the properties) When using MSAccess security, the mdw file is supposed to automatically recognize the user's loginID and correspond it with their password (ie. you setup permissions in the mdw to create a password that corresponds with the user's loginID) in the security for the mdw, NOT in the shortcut! If you have the user/password in the icon shortcut, your sort of bypassing in a way, the intent of the mdw file and MSAccess security. The properties of the shortcut icon would then have the parameters in it which only to refer to the mdw file. The shortcut thus looks like this to utilize the mdw (and the permissions setup in the mdw) (note: this is the most common way):

    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /wrkgrp "C:\Windows\System\Secure.mdw" "C:\Databases\MyAppName.mdb"

    (but I often put the mdw on the network so instead of C:\Windows\System\Secure.mdw, it's F:\Databases\MyDBName\MyDbName.mdw and then the mdb name.) Note also that often this (C:\Program Files\Microsoft Office\Office\MSACCESS.EXE) needs to be changed to correspond with where MSAccess is installed (which could be different for some users.)

    This prevents the least problems versus having separate mdw files on each user's computer. (again, mdw and MSAccess permissions is a bit finicky and I usually do my best to avoid it. But ideally, you don't really want separate mdw files floating around for 1 mdb file.) Here's an example of how to avoid having to create an mdw (and MSAccess security) and actually has permissions embedded into the mdb itself with a simple security table (all automatically as the code grabs the loginID using the getuser routine - which is secure!!):

    http://www.dbforums.com/6332819-post68.html

    Please try this example (disregard the poor coloring scheme). You'll note that the above example uses just 1 simple security table and the getuser routine. You can expand on this for additional "levels" of security. If the tables in this example were put onto SQL Server (along with SQL Server permissions), you have a very secure system (providing that you prevent users from getting into the backend linked tables in the mdb - again, use an mde to prevent users from getting behind the scenes when/if the code crashes.) You'll also notice that there really isn't much coding (other than what's in the modules). A simple =getuser() securely returns the loginID for that user and can be used for many things such as setting the default "EnteredBy" field to =getuser() and you then know who entered the record. I use the getuser() quite often in all my coding. I prefer this scenario over MSAccess security. Your levels of security are thus:

    Note: Front-end has to be secure so no one can get behind the scenes! (this is key) The example in the above link is sufficient to prevent typical users from getting behind the scenes. Creating an mde file from the example will eliminate the ability to right-click on the form's caption and select design view to get behind the scenes.
    1st line of defence: User must have a valid loginID or the getuser code kicks them out of the mdb. Since LoginIDs are usually controlled by network admin, it's pretty secure.
    2nd line of defence: User must be in security table or the vba code that checks on startup when the mdb opens or it will quit (Note: this security table ALSO controls what forms they can/cannot open or buttons they see on the forms.)
    3rd line of defence: If user is able to bypass interface, they must have an ODBC DSN Connection name (and know password) to open the tables.
    4th line of defence: User must be in SQL Server and have appropriate permissions.

    Quick note on ODBC DSN Names: They must always be EXACTLY the same name for every user! If one user has an ODBC DSN Name such as: LTVCustomerSQL and another user who uses the same mdb/SQL Server tables has an ODBC DSN Name such as: LTV_CustomerSQL, you will definitely have problems (and constantly be refreshing the linked tables for each user trying to figure out why it works when you refresh the tables for that user but then "breaks" the connection for the other user - well, this can drive you crazy until you realize there's different ODBC DSN Names for each user to the same SQL Server tables.)

    Compare these to a "hackable" mdw file (or even easier if the login/password is embedded in an icon/shortcut.) Now if you ever lose the mdw file, you're basically screwed (unless you re-create the logins/passwords by looking at the shortcuts.) That wouldn't be good.



    Also remember regarding a hanging *.ldb file, the *.ldb only hangs around in the same folder that the mdb is opened in. It is also possible an *.ldb could hang around (other than all the reasons in the previous posts) if there's some kind of coding in the mdb file which somehow keeps the user connected but I've rarely seen this kind of coding (and it's usually based upon the computer or network not keeping in synch with the mdb when it's closed!) But I have seen a "daisy-chained-hub" type network setup where there were problems with *.ldb files hanging (among several other problems). Hopefully no major companies are using hubs anymore for their networks and they now use a switchbox.

    *********
    But DON'T try to write any code to automatically remove a hanging *.ldb file (it won't work!!) The CORRECT solution is to find the reasoning behind the hanging *.ldb file versus actually trying to remove it via some coding routine! (although I could possibly see an automated compact/repair routine to fix a possible (but doubtful) extremely slow or problematic network issue which would leave hanging *.ldb files.) This is a work-around though and fixing the network would be the ideal solution.
    *********

    Again, don't forget to check permissions! Wherever that mdb (frontend/backend) and mdw file are located, every user must have WRITE and READ permissions to those folders/locations. It only takes 1 person opening it without sufficient permissions to then lockup the *.ldb file. Especially if a table in your front-end desktop mdb is linked to another mdb someone else uses. That other person would then need WRITE/READ permissions to the computer desktop's mdb file (which could be tricky for other user's permissions to a specific user's desktop mdb file.)

    On older versions of MSAccess, you did have to watch for users trying to open the same mdb file multiple times (I think this was back in version 97). I had some code once to prevent this (since I always use the vbs script which prevents multiple instances, this never happens though so I can't recall.) You can also simply open up task manager to watch and see if you have multiple MSAccess processes running when you shouldn't (if you do, this leads me to believe there's something really wierd going on and I might recommend re-installing or a in-depth discussion with the network admins on what could be going on.) Especially if only 1 MSAccess app was opened yet you see 2 MSAccess processes. (note also: it's good to watch the MSAccess process when you test your mdb/mde file.) I do this to monitor the memory used (which should vary between 10-50 meg - if it gets over 50 meg, I get concerned). What you're looking for is an MSAccess process which continuously grows and grows (ie. 100 meg or more) which is "usually" a good sign that there's code in the mdb file which isn't closing a recordset somewhere (or you have multiple, multiple nested subforms and other things going on which is eating up lots of memory - poor form design becomes apparent when you open that form and see a huge growth spurt in the MSAccess process!) You'll also notice growth in the MSAccess process when new variables are declared and used in that part of the code. I often re-use variable names so that this doesn't happen. For example, I often re-use rs as the name of all my recordsets when writing code to open a recordset. If I used a different variable name each time (ie. rsCustomers, rsAppointments, rsCalllog, etc...), I'd see a fairly substantial continuous growth everytime it encountered the new variables. (MSAccess is not the best at recovering memory once it's used - ie. you don't see a large "decrease" in the memory used in the MSAccess process.) I once had a developer who liked to use different variable names for everything and his applications often exceeded 50 meg (which led to some conversations on him re-doing his code.) When the growth exceeds 50 meg and get's above 75 meg, it's time to do some digging in the code to find out where that growth spurt is happening. For example, you click to open a form and then notice a BIG growth spurt in the MSAccess, process. This tells me to look at something going on when that form opens. (or the big growth rate happens when a button is clicked, etc...etc...) After all, you'd hate to have your application be the culprit for crashing other programs on a user's computer simply because the MSAccess process for your application is eating up all the memory for that user.

    Sorry about all the info (got carried away). Again, I hope this helps.
    Last edited by pkstormy; 08-28-09 at 14:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    (another last note since I ran out of allowable characters again in the previous post)...

    You really have to look at the database size and user connection speeds to know which direction to take in the above scenarios.

    If it's a small recordset size (ie. under 100,000 records) and you have most user's with good connections, it may not benefit to put the data on SQL Server (if it's important data though, my philosophy is that it should be on a server - especially if it's critical/confidential data!)

    If you're dealing with large recordsets and/or poor user connection, then consider SQL Server and especially designing unbound forms (this really, really makes forms fly lightning fast!) It also helps keep the data secure and prevents data problems due to "hiccups" in the connection (since you're basically hitting the data source 1 time with your function to write/retrieve/delete the data.) You don't have a "CONSTANT" connection from the data source to the MSAccess form. Designing unbound forms WILL also help if your backend tables are in an mdb file on the network! BUT designing unbound forms is a rather time-consuming process (about 3 times the time it takes for development) so you have to weigh the pros and cons. You definately want to start with a small project if it's the first time you're designing unbound forms.

    With the possibility of an unstable/slow network itself, having a dedicated SQL Server computer and your front-ends with linked SQL Server tables essentially eliminates that network from the picture (unless of course the SQL Server box has issues or only has very little memory (ie. 256 meg) or SQL Server is installed on the network versus a stand-alone box (don't ever do this! - then you'll have more problems as SQL Server will battle for memory against the network computer itself.)

    If your linked tables stem from an mdb file on the network, you've now brought that network and any problems with it back into the picture!

    SQL Server or even MySQL have free versions. It really pays off for MSAccess developers to learn the basics of database creations on a server. SQL Server does a much better job of handling permissions, record locking levels (ie. first in, last out for updating), as well as backups (you can even configure "tran-log" backups so the data itself is backed up every XX minutes.) I've tested this with restores and it works superbly. The MSAccess UPSIZING wizard (Tools -> Database Utilitizes -> Upsizing wizard) also works fairly flawlesly to make your table conversions from MSAccess tables to SQL Server tables transition without problems (Microsoft did a good job on this part!) It's then a matter of getting the hang of creating ODBC DSN Connections (ie. do I use Windows authentication or SQL Server authentication?) I don't develop everything with backend tables on SQL Server though. Only the ones with many multiple users (ie. roughly over 3 internal users or several external users). Again, you have to take it case by case. Sometimes a simple backend mdb is the best solution (providing it's on a stable network!)

    If security to the data and what users can/cannot see/edit is key, then consider using the permissions technique in the code-bank example. I personally think it's easier to maintain than an mdw file. But if your intent on taking the mdw route, I'd use some of the precautions stated in the previous posts.
    Last edited by pkstormy; 08-28-09 at 14:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    What a splendid lecture !!!

    Thanks for this very useful info, pkstormy. While the system I am working on is an old one and consists only a server and some attached PCs, I am also working on a much more ambitious system for another member of the same business community and this will have offices in three Australian cities (Perth, WA; Adelaide, SA and Canberra, ACT, if you are interested) which are thousands of kilometres apart and will undoubtedly require a network setup and your thoughts on this matter will be taken on board, I can assure you.

    Now, back to the current matter. Yes, some of the passwords are built-in to the icons, but these are for low-level operators (some of them temps) manning a call-centre and who only have access to the features in the forms on display. There are senior people, of course, who do have to put in their passwords, in order to use higher degrees of privilege and see functionality which others cannot.

    A long time ago (it seems), I mentioned getting multiple copies of an error message. There has been a resolution of sorts. My client had a problem with his database and asked me to backload the copy I had been testing. I did this and wonder of wonders, those pesky messages have gone away. Perhaps the copy to another system had some cleansing effect and broke some link or other, but all is now OK. Why, I have no idea.
    Anyway, thanks for all your inputs,guys. It was all very rewarding.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hey Jim,

    Sorry I didn't address the multiple error message before. Sometimes refreshing of the linked tables in the frontend is the main culprit for multiple messages mysteriously appearing (or something changed in the mdw file or the mdw file became corrupt.) (if the mdb worked fine until a specific point.)

    My "guess" (without seeing the setup), is that you'd get multiple copies of an error message each time it couldn't successfully resolve permissions to the backend tables or each time it tried to do something in the code/on the form which conflicted with the permissions in the mdw for WHO that mdw recognizes is the valid user. (ie. a form which has to connect to the data tables and reconcile permissions and couldn't do that for certain tables and the message would pop up each time it tried to resolve issues for a table.) ie - it would popup the error when it tried to load the recordsource for the form and couldn't reconcile the correct permissions. It would popup again when it couldn't reconcile permissions to the table for a combobox/listbox rowsource. It would popup again for any coding to retrieve or write data from a table (and that conflicts with the mdw security for the user assuming it's a good mdw file). Knowing what recordsets are loading when the form loads and comparing this with the number of popup messages that pop up helps (ie. you get the message twice and you have 2 places where it must connect to table XYZ on the form or 2 tables that have had permissions changed in the mdw file or 2 tables which were modified....) This then usually leads back to a field or some other change for table XYZ which didn't get refreshed to the frontend (or even possibly affected the mdw and it's permissions in some way for table XYZ.) Of course any "coding" you do which conflicts against the mdw permissions will also throw up errors (ie. that user is recognized as only allowing read permissions to table XYZ and your code is trying to update table XYZ!)

    If this happened for every user all the time (it used to work fine and there were table design changes), I'd most likely lean towards the tables not being refreshed (besides an incorrectly configured/corrupt mdw file).

    If this happened every time for only 1 specific user, I'd more suspect it's related to coding conflicting with WHO it recognizes is the user and what permissions that user has for their mdw file (since I think you mentioned there are multiple mdw files.) Again, permissions for a user to the folder where the backend or mdw file is located could also be a reason if it's only 1 user. Another possibility is that something else changed with their connection to the source data. Possibly something network-wise with their login account. And again, your code is trying to do something which conflicts with the permissions the mdw recognizes for that login account (or their mdw file got replaced/changed.)

    I was a little confused when you mentioned backload a copy which I'm assuming you copied a good (refreshed) linked table frontend, fixed backend mdb (which wasn't in a "need to compact/repair" state) or restored a good mdw file which corresponded with the table structure and user permissions (or even that there was a problem with that user's login on the network and it got fixed.) MDW files sometimes don't play well with table field/structure or network login account changes. One reason I avoid them. There's a much easier way which requires NO maintainence by me (the less permission-type work I have to do the more time I can devote to developing.)

    I'm only speculating here. There could be other possibilities I'm missing.

    Regarding your icons and the PW embedded in them for different groups of users so to speak, I'd still re-consider embedding permissions into a table in the mdb file! I do this all the time (and then allow "admin" type of users to open the table and maintain permissions for other users which eliminates ME EVER needing to do anything for adding/changing permissions!) You really make your life so much easier when you automatically retrieve the loginID in vba code and then match that to a "permissions" type table to allow certain features on the form available based on what "level" of permission that user has. (We have a similar setup to the one you have. Some of our student temp help just uses 1 login. Regardless, I simply allow designated admin level users (in the permissions table) the ability to add/change other login permissions (whether they change 1 central type login or each individual login) - then depending on the level for that login, they can/can't open certain forms or view/update certain data, etc..etc... - very easy coding.) Other senior logins get higher levels to allow more features (or designated as admin level to change other permissions). Again, I highly encourage that you take a look at the permissions example. When you don't have to deal with the ugly mdw files or ever maintaining permissions again, it eliminates one problem in the picture and much less work for you, regardless of how many users you have. (plus, you really only then need to maintain code for 1 front-end interface which you can then disperse to desktops for the users without worrying if that the code conflicts with mdw security.)
    Last edited by pkstormy; 08-29-09 at 00:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    When is this torrent of really useful stuff ever going to end !!!

    The database in question used to serve a number of organisations and every switch from one client to another involved a relink of a different database. I had removed it, because the current client was not sharing stuff with his friends any more. Perhaps i should restore the linking on startup, even though no switches thereafter will be required.
    The database was written in the early 1990s by an outside consultant and the client tells me that the consultant always used to change the passwords when required. I groped around in the database and would you believe it, I found a nice little form which allows an admin to change passwords. Of course, the consultant never told anybody how he did it, to make sure he would continue picking up fees ! What I am doing is transferring it to another front-end called Management, where only the boss and a trusted aide can get in and carry out database changes, permission changes and other tasks. The front-of-house people won't even be aware of this stuff.
    I have in fact already implemented something very similar in a new database (shortly to go into production) for another client in the same business community (all function caterers), where the 30-odd tables of data are supplemented by about 90 tables of metadata to populate the myriad of list boxes and combo boxes in the front-of-house system. Thus the client can invent a new menu (food, that is) in his back-of-house front-end, stick it on his web site and start taking orders for functions within about 10 minutes. I will add the login management functionality to his other management stuff.
    Actually, all of the reports, etc. are listed in tables and have access codes, so that they only appear in the list boxes where users have permission to run them, so this seems to be in accord with your advice, too.
    I am really comforted that I am thinking along the same lines as an acknowledged expert ! After some conversations with the client's staff I was beginning to wonder if I was overdoing things a bit. It would appear not.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It's beginning to sound like there actually isn't an mdw file involved at all but instead, it was setup to push a login to the mdb file (via the shortcut) which then does then look at a security type table in the mdb to allow that person permissions. This was a simple trick on older mdb's where mdw security was not implemented and the routine to automatically grab the user's loginID was also not implemented. I used to do this until I found the routine to grab the user's loginID automatically (which is much easier!)

    Holding down the shift-key should let you in the front-door so to speak and bypass any startup code to lookup a persons permissions in a table before it can be applied to the 1st loading form. That is unless some coding was put in to disable the shift key (which I usually frown on.) After you're in the front-door, you should be able to track down the recordsource (or code) for the "permissions" form to see what security type table it's looking at.

    (FYI - luckily you're working with an mdb file. I've heard some horror stories where only the mdE file was given to the clients. Since mde files are "unhackable/unmodifiable", this is where a lot of clients got the short end of the stick and changes to their program were not possible since the developer could not be tracked to obtain the mdb file.)
    Last edited by pkstormy; 08-31-09 at 11:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    MSAccess Security

    After digesting the mountain of information dropped in my lap, I have evolved a simple yet (I think) adequate security regime for my client. There is already a table listing operator's real names (for audit purposes) so what I am proposing to do is to set up four levels of security corresponding to the .mdw groups of Owners, Admins, Managers and Users. Each user will be assigned to one of these levels. Features giving access to data, such as combo boxes, list boxes and even pages in forms, will be mediated by the current security level. In other words, users can only see what the system wants them to see, according to their level of privilege. Hence, we should not need the system to check permissions constantly and display error messages denying the user access to reports, forms or whatever.
    Getting back to the dramatic history of this thread, I recently found a situation where one user was denied access to reports while another user with identical permissions, was allowed to run it. Looking back to when I began my intermittent involvement with this system, I remember that I received a CD with files which were all .mde files, accompanied by a set of .dlls. I wonder whether one of those libraries had some security stuff which may have got distorted over the years.
    There were even some .mdb files which were renamed .mde files. The client community consisted of about six separate but collaborating businesses and fortunately, I was able to find one .mdb file with all of the code. From this I had to reconstruct all of the variants for the different businesses. Clearly, the developers had tried to keep the clients dependent upon them in some way.
    (When I started my consultancy, in the early 1980s, I did not get an original project for about three years. All of my time was spent rescuing disenchanted clients). These remarks should therefore be considered a ringing endorsement of pkstormy's advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •