Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare Database Versions (Access XP)

    Hi.
    Can anyone tell me if they are aware of a utility for Access that will allow me to compare two "versions" of a database? I'm often asked to create a report or query for one of the databases I maintain, and it's a very difficult task to do during they day, because many people are using it. Ideally, I'd like to be able to work on a copy of the current one and then impart my changes to the "active" one. It's difficult to move the database to my desktop to to my work, because people need to keep adding records. But if I work on a copy, I'm afraid that I will forget something when attempting to copy the reports and their associated queries (and, heaven forbid, new forms and fields).
    Is there a third party utility that will let me copy the new stuff, or even everything but the tables or the data in the tables?
    Thanks for your input.
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Compare Database Versions (Access XP)

    Are these db's split into a front and back end?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare Database Versions (Access XP)

    See Microsoft Access third party utilities, products, tools, modules, etc.. Under "Compare Access databases" you'll find a list of tools to compare databases.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Database Versions (Access XP)

    Unfortunately, they are not. They're single MDB files. (There are about two dozen people using them, and that seemed like a good compromise at the time.)
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Compare Database Versions (Access XP)

    Hmmm, I'd split them into front and backends - if only for the convienence of being able to work on the front end separately.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Compare Database Versions (Access XP)

    There are tools available, but I have to say that I haven't found one that actually works. Most of them use the modified dates of the objects as the basis for comparison. Unfortunately, compacting the database changes those values. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> With Access 2000 and later, you can't "impart" your changes anyhow unless you have exclusive access to the database. You would be better off splitting front and back ends and simply distributing a new front end with the changes included.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compare Db Versions - Diff between 2 MDB files

    Not that this reply will help you 3 years later but I have a similar need. This is even with a split database where 1 copy of the front end is on a network share. It is encouraged that the end users copy the front end to their machines for performance & due to one local table where the users can paste data & then run queries against that data, but not everyone does.

    What I do is manual. I am only looking for items (Table, Query, Form, Macro, Module,... but not data or changed items) that someone added to the network version of the front end so I can review if I need to copy these back to my "master" copy of the front end. (if someone made changes to the design & did not let me know or request me to do that, too bad!)

    In both databases I will go to Tools, Options and select to display System Items.

    I will then open, copy & paste the contents of the table MSysObjects from both databases into an Excel Spreadsheet. Each DB's version on a different tab.

    I sort the data by the TYPE and then by NAME.

    On the one from the network share I will do a VLOOKUP back to my master version on the NAME column. Any that don't match, I will take a look at & import into my Master version.


    __________________________________________________ __________________________________________________
    Actually while thinking about it, I just created a query where I do this compare from a linked MSysObjects on the network version to my local or master database doing a left join on Type & Name where the local Name is null. (helps not doing a stare & compare)

    Here is my query - SQL View:
    (database name is Playground, renamed linked MSysObjects to MSysObjects - Network Playground and then set the Hidden flag in properties)

    SELECT [MSysObjects - Network Playground].Type, [MSysObjects - Network Playground].Name, [MSysObjects - Network Playground].DateCreate, [MSysObjects - Network Playground].DateUpdate
    FROM [MSysObjects - Network Playground] LEFT JOIN MSysObjects ON ([MSysObjects - Network Playground].Name = MSysObjects.Name) AND ([MSysObjects - Network Playground].Type = MSysObjects.Type)
    WHERE (((MSysObjects.Name) Is Null))
    ORDER BY [MSysObjects - Network Playground].Type, [MSysObjects - Network Playground].Name;


    Hopefully this will help someone!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  8. The Following User Says Thank You to jhimes For This Useful Post:

    hasse (2016-04-27)

Posting Permissions

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