Compare Objects in a Database (1 Viewer)

Status
Not open for further replies.

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:10
Joined
Sep 12, 2006
Messages
15,614
I had 2 copies of a large database, and I wanted to determine whether any objects had changed between the 2 versions.

I wrote this module to store the created and last modified dates of all the objects. (Tables, Forms, Reports, Modules). Tables and Queries are both included in a single container.

This works in A2003. I have not tested it in later versions of Access.

The dbs contains 1 table to store the results, and 1 module containing the sub. It's very quick. My database has about 4000 objects, and all details were updated in less than a minute.

I was originally intending to connect to the database I wanted to compare (hence the columns in the objectlog table for remote database), but I couldn't sort that at short notice. Instead I just ran the same process in both databases, and compared both tables for differences.

[amended upload 15:30 28 March 2016]
 

Attachments

  • ObjectLog.zip
    13.4 KB · Views: 374
Last edited:

MarkK

bit cruncher
Local time
Today, 14:10
Joined
Mar 17, 2004
Messages
8,178
You can also query this from the hidden system MSysObjects table, like . . .
Code:
    SELECT t.Name, t.Type, t.DateCreate, t.DateUpdate
    FROM MSysObjects AS t
    WHERE t.Type <> -32757
        AND NOT Left(t.Name,4)="MSys"
        AND DCount("*","MSysObjects","ID = " & t.ParentId)
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:10
Joined
Jan 23, 2006
Messages
15,364
There was a utility --I think created by Bob Larson -- that could compare 2 databases and all objects. IIRC is was in preparation to merge some files or databases. I haven't seen him on any forum for a long time.

Did some searching and found this
http://btabdevelopment.com/wp-content/downloads/Tools/DatabaseMergeAnalyzer.zip

DATABASE MERGE ANALYSIS TOOL (from Bob Larson site)

This is a tool which I wrote to help me at work where I had to merge two databases (two COMPLEX databases) together and needed to know what objects were in one and not in the other. This is the first, of probably many, iterations of this tool and I will be updating the download as I get it refined further. But I wanted to share it with anyone who might be needing something similar.
 
Last edited:

MarkK

bit cruncher
Local time
Today, 14:10
Joined
Mar 17, 2004
Messages
8,178
FWIW, here's a query that compares rows in 2 MSysObjects tables.
Code:
SELECT db1.Name, db1.Type, 
   [db2].[DateCreate]-[db1].[DateCreate] AS DiffCreate, 
   [db2].[DateUpdate]-[db1].[DateUpdate] AS DiffUpdate
FROM ( 
   SELECT *
   FROM MSysObjects [COLOR="DarkRed"]IN 'c:\[I]< Your File 1 >[/I]'[/COLOR] ) AS db1 
LEFT JOIN (
   SELECT *
   FROM MSysObjects [COLOR="DarkRed"]IN 'c:\< Your File 2 >'[/COLOR] ) AS db2 
ON (db1.Type = db2.Type) AND (db1.Name = db2.Name)
WHERE db1.Type <> -32757 AND Left(db1.Name,4)<>"MSys";
If an object exists in both databases, the DiffCreate and DiffUpdate fields will be present and show the difference in the values between the two files. If there is no change, these fields will show zero. If the DiffCreate and DiffUpdate are null, then the object exists in the first file, but not in the second.

(To compare to your CurrentDb, just remove the IN clause from one of the FROM clauses)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:10
Joined
Sep 12, 2006
Messages
15,614
thanks for the suggestions. I had just googled and noted comments saying the dates weren't available.
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom