Compare Database Versions (Access XP)

Topic

New Reply

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.

Reply | Quote

Replies

Are these db’s split into a front and back end?

Reply | Quote

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.)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.)

Reply | Quote

Are these db’s split into a front and back end?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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. 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.

Reply | Quote

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. 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.

Reply | Quote

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!

Reply | Quote