Auto Compare DB Assets (1 Viewer)

[Reshmi mohankumar]

Can we compare a DB with another Modified/Altered DB(Which is updated with more tables/added columns to exists tables)? if yes what code can i use to integrate of exists linked tables and local tables?
and may it can be like a tool for deployment for updated Backend file. we just run and excute.
Note: I am not doing deletion of any table...!

 

[arnelgp]

it can be done, but will require you to write more code.
if you are going to run this on a third db, comparing db1 tables to db2 tables,
the first thing you need to look, is the MsysObjects table of each dbs.
you select the Name from this table with (Type either 1 or 6)(1 for local table and 6 for linked tables).
as you Open the structure of each table, you can compare their Fields by loop on the Fields property.
there are many example on the net on how to do this.

 

[Reshmi mohankumar]

it can be done, but will require you to write more code.
if you are going to run this on a third db, comparing db1 tables to db2 tables,
the first thing you need to look, is the MsysObjects table of each dbs.
you select the Name from this table with (Type either 1 or 6)(1 for local table and 6 for linked tables).
as you Open the structure of each table, you can compare their Fields by loop on the Fields property.
there are many example on the net on how to do this.

and not even compare only, i need to update with new assets to existing DB..

 

[arnelgp]

of course you can do that, BUT, only when the db is not in used.
otherwise, your Users will not be happy of the outcome.
or you can't get the Modification go through because the table(s) are in use.

you need to do it when nobody is using the db.
add new field to a table, copy the data from the other db to
the new table with additional fields.
re-copy the new table to the old-one.

on the meantime, while waiting for the db to get Free,
you execute a Code that will check each table and compare
to the other db's table. if they are not same structure, you can copy
the name of the table to a new table. then later you can visit this table
to see which tables need to be updated, instead of looping again to the
system's table collection.

 

[Isaac]

If you want to avoid messing with system tables, I have also done this sort of thing in the past by simply looping through the currentdb.tabledefs collection, and looping through the tabledef.fields collection. Did as analysis prior to a system migration to identify fallout.

I'm not sure I would recommend doing the table altering also automatically, it might be wise to have the code output discrepancies for human review first

 

[theDBguy]

Hi. Whose making the design changes? Are the users freely allowed to modify the BE table designs?

 

[Reshmi mohankumar]

Hi. Whose making the design changes? Are the users freely allowed to modify the BE table designs?

BE changes will made by me. and later client DB will be updated. Now manually im doing all the table edits / inserts in client place also. So to overcome manual interaction i need to run a code that all modified DB properties to append the client DB . And BE is a secured with password. client cant open it.

 

[isladogs]

Suggest that you document all changes in order to manage this approach in the future.
You can write code to update the BE at client sites rather than do it manually.
Suggest you research the use of SQL CREATE TABLE and ALTER TABLE at https://www.w3schools.com/sql/sql_alter.asp

 

[Pat Hartman]

Allowing users to change the FE's is what is causing this problem. In a shared database, you don't allow users to modify the application. If they want to create their own stuff, have them do it in a separate database. Using a standard distribution method would automatically prevent this because opening the app would automatically download a replacement essentially wiping out any object changes made by the user.

I'm pretty sure there is a compare tool at www.fmsinc.com Their products are excellent and their prices fair. You could never create anything even close to one of their tools for a few hundred dollars.

 

[isladogs]

@Pat Hartman
The OP is referring to changing the design of the BE tables and stated in post #7 that only he, as the developer, makes any changes.
The thread has nothing to do with FE changes.

 

[Pat Hartman]

Typo: