Results 1 to 6 of 6
  1. #1
    twb60 is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Location
    Archer Lodge, North Carolina
    Posts
    7

    Shared Network Drive Databases

    A few years back a couple of highly proficient contractors created 2 Access databases; one for Estimating Projects and the second for Cost Forecasting Projects. I rate my own Access proficiency at 5 on a 0-10 scale. I use tables to store data, use the Access interface of query design to select the criteria to return the data I am looking for. Pretty good with form design, building macros, reports. The contractors built the Estimating and Cost Forecast databases by writing SQL (correct? or is it VBA?). I am at a disadvantage here, because I have not learned to read it. When I open the main form of either database in design view and view form properties, I see several Event Procedures for On Open, On Unload, On Timer and a Timer Interval value. By clicking the ellipsis for any Event Procedure, Visual Basic opens and displays the SQL (still not sure this is the correct term). Near the top of the VBA, picklist on left allows me to select what looks like any object in the database. As Found selection is of course Form because I got here by following the Form Property Event Procedure for On Open. and the picklist on the right is Open because I got here from the Form Property On Open Event Procedure.
    Sidebar Question: From the previously mentioned picklist at the top of the VBA page, if I select (General) and (Declarations) will this give me the code for the entire database?

    Baking up a little, these 2 databases reside in separate folders on a shared network drive. I was able to reach out to a very competent IT person here where I work who cleaned up the folder permissions; some of the permissions group names were very old, subsequent groups had been added and there were conflicting permissions. I supplied a list of all names for users requiring read/write/modify. I "feel" confident the folder permissions is where it needs to be now. One of these databases links to a few tables in the other; something I explained to the IT person thinking that is a key fact.



    So what is wrong? There are some administrative functions built in the database, such as a list of users that restricts what they can do, like an admin can change data, but you must be a super-admin to make design changes or even to be able to access the administrative functions form. There is a boot function that no longer seems to work for super-admins. The most frustrating thing to me is the record locking file just never goes away. The Cost Forecasting database actually seems to work well for the small group of users and maintains a reasonable file size. The Estimating database record locking file never goes away and the database size grows exponentially, requiring someone to make a copy, take it off line, compact and repair, name something different (like adding the date of compact and repair), bring it back in the network drive folder. Now shortcuts must be modified because the file name is different (added C&R date); not a big deal, just a factor. I am not a huge shortcut fan.

    I realize I have rambled a bit, but I wanted to try to describe what I have, describe the environment and some of the unexpected items I see. Neither database opens properly for me even though I am a super-admin in that table. Since I am a super-admin, I can hold shift and open it from the backside (what I call it). I learned my self rated proficiency of 5 pretty much on my own over the course of years. Can I learn to read and troubleshoot this language the highly skilled contractors built? I am patient and willing to start with step 1. Open to comments. ThanksGeneral Declarations.pdf

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The contractors built the Estimating and Cost Forecast databases by writing SQL (correct? or is it VBA?)
    probably both - sql is what is used in queries, vba is used to provide action to events (e.g. a form opening, a button click, a mouse move etc) and special functions (a complex calculation for example) which may use sql to update tables and/or manipulate the data

    From the previously mentioned picklist at the top of the VBA page, if I select (General) and (Declarations) will this give me the code for the entire database?
    no - just for that module - each form has its own module and you can have general modules as well

    The most frustrating thing to me is the record locking file just never goes away
    it stays until all users are out of the system. If a user drops out in an uncontrolled manner (e.g. network failure) the locking file remains. An easy test is to try to delete it - if it deletes fine - there is nobody in the system, if it doesn't then someone is still in the database. I have come across situations when the file itself becomes locked for some reason - a reboot of the server is then required

    Sounds like your databases are not split (i.e. tables in the backend on the server and all forms, reports, queries and code in the front end, a copy of which resides on each users machine). So if not split or split but users are all sharing the front end, the chances are something has become corrupted (particularly if the front end is a .mdb and not .mde). The first job for you would be to split the databases.

    Can I learn to read and troubleshoot this language the highly skilled contractors built?
    don't see why not, depending on how good your contractors were (i.e. consistent and clear naming convention, good documentation in the code and manual, good structure, etc). Also be aware that XP and 2003 is no longer supported with updates etc, probably not a problem but you may want to consider moving to a more current version.

    database size grows exponentially
    it shouldn't - either you are storing images or you have some very inefficient code for managing the tables. Solve the former by moving to 2007 or later (it stores images more efficiently) or (with coding work), storing the image in a windows folder and just the shortcut in the db. For the latter, you'll need to get learning to determine how it all works

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I interpreted One of these databases links to a few tables in the other to mean they are split. Since I didn't read anything that indicated each user has their own FE (front end) copy I'd suspect it is shared. I'm surprised the developers did not compile the db into an mde.
    The Estimating database record locking file never goes away and the database size grows exponentially
    To me, these two factors indicate abnormal shutdown. The bloat could be from adding images, but I took the comment to mean it's rapid bloat without adding a bunch of new records with embedded files or images. In that case I'd suspect frequent making/over-writing tables with MT queries, too many temporary tables, frequent design changes, over-use of DAO or any of the other typical causes of bloat. IMHO, a new file with a compact date is of little value because that date is insignificant. Of more importance would be an update date (if local tables contain data linked via ODBC) or a design change date, in which case I'd store such dates in a table and present that date on a 'switchboard' or About form. Shortcuts? They are the preferred method for users to access a db AFAIC. Users should NOT be poking around in the folder where the BE or FE is kept. Nor should they be allowed to make copies of the db and dump them on their desktop, which I encountered more than once (until I took care of that). And anyone with a browser can figure out there's a shift bypass, so I'd make that more difficult as well.
    EDIT: forgot to mention that I highly recommend you poke around and learn on a copy of the db - making sure you disconnect that front end from the back end (assuming they are in fact split) and reconnecting to your own BE. Otherwise, you would not be the first person to accidentally delete records from the BE, thinking what they were doing was acting on their own set of tables.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    twb60 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Archer Lodge, North Carolina
    Posts
    7
    I updated my profile to clarify I now use Windows 7 64 bit, MS Office 2010 (my current version of Access is 2010 32 bit); all version decisions are corporate and a long story why they tend to stay behind what is current. To clarify something, the 2 databases I am referencing are 2 entirely separate databases; not split with the front end user interface to a 2nd database containing the data. 1 database does link to a few tables in the other, because the data is relevant. I understand the split and effectively did this in the past with a couple of Access databases I developed my way (queries and macros, no direct language writing). Not having the opportunity to speak with the developers, not sure why they did not split the estimating and forecasting databases. Also not sure why they built 2 separate databases for estimating and forecasting.
    I am able to open the record locking file with notepad to see entries (computer names). A couple of times I checked with each person represented in the notepad file and they did not have the database open. Whether the people improperly shut down (inclined to think this did not happen) or the complexity of the shutdown script (highly suspicious of this), the result is the record locking file prevents me from deleting it. Error message is The action can't be completed because the file is open in another program. IF when this occurred I went to the users represented by their computer name on the record locking file and pulled up Task Manager on their machines, would I see an Access process running? Not sure and due to impractical logistics have not tried this.

  5. #5
    twb60 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Archer Lodge, North Carolina
    Posts
    7
    Considering how forum members are quick to help me, I wanted to provide some details of the resolution. As mentioned initially, the 2 Access databases are on shared network drive. Turns out the folder permissions contained custom groups that presented permissions conflicts. I was able to reach a very helpful IT professional within my company who rebuilt the permissions groups; eliminating the conflicts. The conflicts were preventing the databases from closing the record locking file after the database was closed. The never closing record locking file was responsible for the exponential growth of the database. Turns out after the permissions conflicts were eliminated, the databases are working flawlessly for the users. Thanks for your help all.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks so much for the update. I seem to recall hearing about this before - that a user had permissions to do everything but delete on the network folder, thus the locking file problem. Didn't realize it could cause db bloat though.

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

Similar Threads

  1. VBA code to create a folder on a shared drive.
    By tgall in forum Programming
    Replies: 4
    Last Post: 01-08-2016, 11:39 AM
  2. Shared Drive Access Issue
    By Classic in forum Access
    Replies: 6
    Last Post: 09-23-2014, 06:03 AM
  3. shared DB on local drive
    By mike02 in forum Access
    Replies: 1
    Last Post: 05-20-2013, 01:54 PM
  4. Front end on shared drive, modified date?!
    By redbull in forum Access
    Replies: 3
    Last Post: 09-13-2012, 10:32 AM
  5. Multi Users on shared drive
    By wallen in forum Access
    Replies: 0
    Last Post: 08-25-2008, 08:57 AM

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