Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I need ideas!! Please...

    I have just recently started a new job and taking over an access 97 SR-2 database which needs some major updating. It's set up like this now. Most have a runtime version installed which links to the backend on the server where all the tables are. The full version apps also link to the same table on the network.
    They can rarely go without this database for ANY length of time. So I will have to be comeing in early or staying late to do updates. What would be the best way to distribute the databases this time? Reinstall the new updated runtime version on each pc...about 40 or so. Or should I use replication for the front end. So that I can work on the design changes throughout the day and then at the end of the day just replicate them all?? Anyone have any idea what I'm saying?
    Thank you very much for taking time with me here! [img]/forums/images/smilies/smile.gif[/img]
    Jols

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: I need ideas!! Please...

    Front end replication requires a great deal of care and feeding in order to keep it from devouring you AND your database. The method you chose depends in part of what kind of changes need to be made. If you're changing design, you CANNOT safely work on it while anyone else is in the database, period. Trust me on this one. Replicated or not, it will bite you eventually.

    If it's replicated, it will bite you immediately, since replicas sync design changes first and then data. You NEVER try to sync both at once unless you're really into pain. If the design changes remove a field, for example, the data that may have been entered into that field before the design changed will cause errors when the system tries to sync it to a field that no longer exists. Front end replication also doesn't like multiple back ends, and you can get some very strange results from them, so you would need to use local lookup tables in the front ends.

    If you have the Office Developer's edition (I assume you have, since you are distributing a runtime version), you could look into a network location for your runtime version setup. I never tried it with the Office 97 Setup Wizard (which is lousy, by the way), although it works fairly well with the Office 2000 Packaging and Deployment Wizard. That would allow you to put the updated front end setup in a network location and then notify the users to run the setup for themselves. That would probably cause you fewer headaches than front end replication, which requires at least three generations of replicas to work properly and safely and still breaks fairly often.
    Charlotte

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    LA, California, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: db Replication

    I am using Access 2K SR1. I have been looking into replication as a possibility for my users so I read through a lot of the posts here regarding replication. I have a database used by several users simultaneously on a network, but some of the users travel (now usually just one, but in the near future it will be around 3) and enter records involving several tables while on the road. Currently I use queries which determine whether a record is new and then append it to the network database.
    <img src=/S/question.gif border=0 alt=question width=15 height=15> My question is about your comment regarding synchronizing design and data changes at the same time. If I make design changes to the backend data part of my database while someone is on the road, is there any way to avoid the problem you are talking about, or should I just not use replication? Can I make design changes that do not affect current tables without a problem (e.g. creating new tables and relating them to existing tables)?
    Thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: db Replication

    Creating new tables isn't a problem because no one can put data into something that isn't there. Changing field names or removing fields or tables is a definite problem because Access syncs the design changes first and then blows up when it tries to sync data into those missing fields or tables.

    If your users travel, you may want to look at partial replicas, unless they need the whole thing wherever they go. If they only need a subset of the data (i.e., a sales rep has a territory which doesn't usually overlap other rep's territories), then a partial replica stratifies the dataset they get and that reduces collisions when they actually sync back to the design master.
    Charlotte

  5. #5
    Lounger
    Join Date
    Mar 2001
    Location
    LA, California, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: db Replication

    Thanks very much for the quick response. I will look into partial replicas also. Can you think of anywhere off the top of your head that you'd recommend for me to look ? If not, don't bother doing any research, I have 5 million sites bookmarked and I'm going to be getting a newer Access book soon (I still have one of F. Scott Barker's Access '97 books, though I'm using 2K and don't refer to the book much).
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: db Replication

    Look on the Microsoft site for their white papers on replication. You can also download the partial replica wizard from there.

    If you're buying books and don't have it yet, make sure you get the Access 2000 Developer's Handbook, by Getz, Gilbert & Litwin. The thing is a mammoth 2 volumes and 3000+ pages, but it covers just about everything you need to know about Access.
    Charlotte

  7. #7
    Lounger
    Join Date
    Mar 2001
    Location
    LA, California, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: db Replication

    Thanks again. I have seen that book recommended many times here and elsewhere - looks like it's unanimous, I'll have to get it. <img src=/S/read.gif border=0 alt=read width=19 height=33>
    Regards,

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: I need ideas!! Please...

    I may be missing something here, but it seems like you are making more out of this than need be. I'm assuming all the runtime users have a similar copy of the frontend. All you need to do is have them do is copy down from the network the new version of the frontend and relink to the backend tables. The only time you would have to re-install Access was if you used features in new version not previously installed, such as certain ActiveX controls, etc.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need ideas!! Please...

    Thanks for the reply Mark.
    Someone told me that I could add an icon to the desktop of each user that would fire off some vb script and would copy the latest version of the front end to the users local hard drive...replacing the older one.
    By having a updated date table in the master on the network and the same table locally for each user. When they click the icon it checks the date from the working database on the server and if the last updated field is greater than the last updated field locally it copies down the latest mdb replacing the old one. Would you have any idea on how to write some vb script to do this?
    Thanks again
    Jols

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: I need ideas!! Please...

    >>Would you have any idea on how to write some vb script to do this?<<

    Sorry, I don't know how to use vb scripts from desktop. What I typically do is create a .bat file (I'm an old DOS guy at heart, I guess) and put it in each user's startup folder. This way, each time they turn-on their machine, a new copy of the frontend gets copied down from the server to their local drive.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: I need ideas!! Please...

    It's also possible to do this from within Access, but it requires the use of the Shell command, and that seems to break if there are any spaces in your folder names. If you want more information about that method, check <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=44155&page=& view=&sb=&o=&part=&vc=1>this post</A>.
    Charlotte

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need ideas!! Please...

    Djoly,

    Maybe I am reading your post all wrong but it seems to me you are trying to do more work than you need to do.

    I am doing the same thing where I work...everyone has the runtime version on their computer (front end), I maintain the backend. If the users update the forms with Data...this will update the linked tables to the backend db automatically.

    If however, you are talking about updating designs, I can't imagine why you would be updating the designs on a daily basis, but if you are...here is what I would do....make a copy of the backend, make your design changes...rename the OLD backend and name the NEW backend to the name the OLD backend once had...doing this will maintain all links.

    Again, I may have misread your problem.

    Good Luck.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need ideas!! Please...

    D,

    I do this all of the time. I have an ever-evolving database that constantly requires modification. To fix this, however, I've created a module with versioning logic in my database. I have a table on my server side called TblVersion, and a client-side table called TblVersionSlave. Copy the following text and save it as a .bat

    @echo off
    cls
    echo.
    echo Preparing to copy Updated Linked Database to your Computer for the DatabaseName
    pause
    echo This may take a moment, please be patient.
    echo.
    COPY "X:Path1Path2DatabaseName.mdb" "C:WINDOWSDesktopDatabaseName.mdb"
    COPY "X:Path1Path2UpdateDb.bat" "C:WINDOWSDesktopUpdateDb.bat"
    pause
    :cd
    cls
    echo Update Completed see DatabaseName Icon on WIN95 Desktop
    echo Use this Icon to access Database Name
    echo.
    echo

    echo Click X to Exit...


    See the attached file for the actual versioning logic.
    The module will compare the linked table version with the local table version. If the version listed on the linked table is greater than the version on the local table then the module shells out to DOS and launches the batch file, then quits the Access app to allow a copy over.

    Whenever you do an update to your database you log into the server table the changes, and log the identical record into the local table. You put your updated copy on the server that everyone can download from and it will update the database whenever you release a new version. Be sure to put the updated version record on the tblVersionSlave, otherwise your users will get a constant prompt to update. If you have any questions feel free to contact me at CMOTDever@aol.com

    Jeff

    PS, A few things: Charlotte is right about the spaces in the names of folders with a batch file UNLESS the path and file name are included in double "quotes", and for this to work (as I have coded it) you need to put the batch file on their Win95 desktop. (Note-this only works with 9x, not with NT due to the path structure for the desktop. You could modify it to suit your needs, however I have never had to). You will need a Zip program to open the file I have attached. It's 102k so I could not upload it otherwiese. Sorry.
    Attached Files Attached Files

  14. #14
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need ideas!! Please...Sorry Charlotte

    Oops: Coupla' corrections:

    I did not read Charlottes response properly. It very well may be that the shell command bombs when there are spaces in the folder names. I've never run into that problem (i.e. all of my paths thus far have been one word).

    I misspelled otherwise.

    Good Luck
    Jeff

  15. #15
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need ideas!! Please...Sorry Charlotte

    Thanks Jeff!!
    I will have to look through the code and see how I could do something similar. That's exactly what I was looking for thanks again!
    Jols

Page 1 of 2 12 LastLast

Posting Permissions

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