Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: How to edit forms in a split data base

    Our data base has been split to avoid corruption, and after 8 months it appears to be working. Each user has a copy of the data base forms and the data itself is stored on a shared drive. The problem is how do I make changes to the forms when the option to go into design mode is greyed out.

    Thanks.
    Jerry

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Will the changes to the form(s) have to be echoed throughout every users version of the application?

    Are we talking MDB of MDE?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question

    I am opening this thread again because it has not been resolved. The Access 2003 database has been split and the back end resides on a shared drive. The back end file extension is .MDB and the front end is .MDE. Each user has a copy the front end of the database on their harddisk. The question is still "How can I edit the forms when design mode is greyed out?"
    After the forms are revised, then all users will be asked to recopy to their harddisk.
    Jerry

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You hopefully kept a copy of the front end MDB that the MDE was created from. You make your changes to that MDB, create a new MDE and distribute that to users. There are numerous ways to automate that distribution should you need help with that.
    Paul

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    I have code that you can put in the DB when a user opens the front-end of it, it automatically checks to see if there are updated tables. Doing it this way will automatically push the updated forms to the users desktops.

    You set up Access ULS or did you use your own security within the db? If you're using Access ULS, you'd have to be part of the admin group that has the option to edit in design mode, if your just a regular user, then you can't.

    Also, from what I've done splitting dbs to a front and back-end, if you edit the one front-end on your side, then just put the new front-end on the network share and let each user copy it or create a shortcut to it to their desktops.

    **I don't trust the users, in the environment that I work in just because they've proven to me they don't know what they are doing. So I would suggest hiding the front-end on a network share, go to each users machine, set them up, then rehide the front-end on the share drive.**

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You MUST have a copy of the front-end .MDB file to make changes to it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jan 2008
    Location
    Netherlands
    Posts
    2

    procedural consideration

    What we used a lot (apart from the mde and uls discussion) was a ms-access application that copies a new front-end (mde) from the network to the users workspace (e.g. like a c-drive or a network-drive) as soon as the creation date of the copy was older than <X> - days (Actually it also checked on the datemodified of the source and the datecreated of the copy and if the copy did not exist). The name of the copy was a combination of a prefix value and the user name on the network (we kept all user copies in one directory).
    In the copying application we had a table with a single record containing the path and name of the source front end, the prefix, the path where to copy to, and the X-value for the number of days.
    The only item that resided on the users machine was a shortcut to the access application that handled the (automated) copying of the front end.. (we kept the user-copies of the front ends on a network drive).
    As soon as a new release was ready for production we changed the name of the source to match the new release, and every user that logges in after that change automatically gets a new copy of the front end.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I use a vb script which clones the source frontend mdb and opens the cloned frontend mdb (adding the user's login name to the cloned mdb/mde). This way the user is never in the source frontend mdb/mde which I keep in a safe place. If I have new code, I simply copy the new frontend mdb/mde to the source location which the vb script clones off of.

    It sounds like you're doing something similar but I'm not sure why you would need to do any checks for the latest mde/mdb and not just want the users to open the latest frontend mdb/mde code. It seems like a waste of time doing the check and it only takes 1 second for the cloning of the mdb/mde and launching the cloned mdb/mde. All my users have a shortcut to the vb script to do the job. I do the cloning a tad differently though...

    The cloning vb script accomplishes several things for me:
    1. It clones the source frontend mdb/mde and adds the user's login name so I can see exactly when that user last got into the application by looking at the cloned mdb/mde's creation date.
    2. I can tell if the user is in the application by looking at the folder and seeing an *.ldb file on the mdb/mde with the user's name (i.e. MyAppJoeS.ldb)
    3. There is never a conflict of 2 or more user's getting into the same frontend mdb or mde file.
    4. The source mdb/mde file is always in a safe location and is never corrupted. It is just used to clone off of. If the user corrupts the cloned mdb/mde, they just launch the vb script to reclone a fresh copy of the mdb/mde.
    5. The mdb/mde file stays small so if I have make-table queries in the app and the mdb/mde file grows, since it is re-created during cloning, the user gets a fresh new lean copy of the mdb/mde the next time they launch the vb script.
    6. I can copy a new source frontend mdb/mde at any time without having to make all the users close out of the mdb so I can copy new code (especially the ones who open the mdb/mde file and sit in it all day).

    Here's all the code my vb script has in it. I'll copy the vb script, rename it, and simply replace the LUName for a different program mde frontend creation. I have never ever had a problem doing it this way and it's made my life so much easier. I have gotten fancy and had a table storing all the mde locations for each different program frontend and returning that to the LUName variant below in the script but I found this was just a waste of time and simply put the location of the mde for each of the vb scripts. I had one vb script to launch any frontend mde but I found it was simplier just to create a separate vb script for each frontend mde program (so I'll have a separate vb script to launch the cardfile.mde and a separate vb script to launch the voucher.mde, etc..etc..). If I need to move the app, I change the LUName in the vb script for that mde verses doing it in a "lookup" type table database. To each his own though:

    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    LUName = "\\appserver\D\Deployment\Health\CardFile\CardFile .mde"
    newName = Replace(LUName, ".mde", "") & GetUser & ".mde"
    'or newName = Replace(LUName, ".mdb", "") & GetUser & ".mdb" for mdb frontend files.
    retval = 0
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(oldname, newName, True)
    Set objFSO = Nothing
    Dim objShell
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run "MSAccess.exe " & newName, 1
    Set objShell = Nothing
    Last edited by pkstormy; 01-04-08 at 13:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jan 2008
    Location
    Netherlands
    Posts
    2
    The check for modification date is due to slow responsiveness on certain networks (heavy traffic, high load on servers, slow PC's and combinations), so actually i have seen situations where the process of copying of a 20 mb frontend could take up to half a minute, (and another 10 seconds for loading the copy..)
    So in that case a check on datemodified for the source and the creation date of the copy could speed up things given slow "networks". Beware that opening (and closing) the source frontend changes the modification date.

    Public Function fnFileDateModified(filespec) As Date
    On Error GoTo Err_fnFileDateModified
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    fnFileDateModified = CDate(f.dateModified)

    ' use fnFileDateCreated = CDate(f.DateCreated)
    ' in a similar function to return the creation date..

    Exit_fnFileDateModified:
    Set f = Nothing
    Set fs = Nothing
    Exit Function

    Err_fnFileDateModified:
    Select Case Err.Number
    Case 53
    'do nothing, file not found
    Case Else 'not anticipated error
    MsgBox Err.Number & Err.Description & " In fnFileDateModified in modUpdate"
    End Select
    fnFileDateModified = 0
    Resume Exit_fnFileDateModified
    End Function

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not directly related to the question, but should your FE really be 20MB?

    do you /decompile the .MDB from time to time?
    (insert here all the usual warnings about maximum www research first and /decompile a copy you can afford to lose).

    izy
    Last edited by izyrider; 01-07-08 at 14:05.
    currently using SS 2008R2

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you do not have a copy of your MDB front end then aside from trying to recover the data you are stuffed.

    There are some software packages out there that attempt to recover design data from MDE's, but personally I've never used them so can't comment

  12. #12
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    Thank you all for the great tips for managing a split data base. A backup copy of the data base, made prior to splitting the db, was used to create a new front end and everything is working well. I will save all this information in case that the data base ever needs a new fe/be solution due to corruption.
    This is a sales call tracking application that has undergone changes to the forms once or twice a year.
    Consider this one resolved.
    Jerry

Posting Permissions

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