Board index » delphi » Comparing Two ADO Databases and Restructuring to Match

Comparing Two ADO Databases and Restructuring to Match

Is there anything out there to do this?

That is have a default, empty database in .MDB format with all needed tables
and fields and a currently used .MDB format database and performing a
synchronization of the data structures without affecting the actual data
stored in the second database.

I want to be able to perform database structure updates with little or no user
intervention.

-Johnnie

 

Re:Comparing Two ADO Databases and Restructuring to Match


I usually compare the old table to the new table definition.  If there
are differences, I create a new empty file with the new field
definitions, and then load the old data to the new file, and finally
rename the old file myfile.bak and the new file myfile.mdb.
Quote
Johnnie Norsworthy wrote:
> Is there anything out there to do this?

> That is have a default, empty database in .MDB format with all needed tables
> and fields and a currently used .MDB format database and performing a
> synchronization of the data structures without affecting the actual data
> stored in the second database.

> I want to be able to perform database structure updates with little or no user
> intervention.

> -Johnnie

Re:Comparing Two ADO Databases and Restructuring to Match


(Johnnie slaps himself on forehead)
I had only considered restructuring the file with the data, not using my
definition file as a recepticle for the old format data.  This would be much
nicer as I would not even need to perform scarry restructuring in ADOX or via
SQL.

How do you handle the batch transfer from the original file to the new file
being sure to get all tables and fields?

-Johnnie

Quote
"Richard Grossman" <usenetu...@techIII.com> wrote in message

news:3D1BF51B.8020301@techIII.com...
Quote
> I usually compare the old table to the new table definition.  If there
> are differences, I create a new empty file with the new field
> definitions, and then load the old data to the new file, and finally
> rename the old file myfile.bak and the new file myfile.mdb.

> Johnnie Norsworthy wrote:
> > Is there anything out there to do this?

> > That is have a default, empty database in .MDB format with all needed
tables
> > and fields and a currently used .MDB format database and performing a
> > synchronization of the data structures without affecting the actual data
> > stored in the second database.

> > I want to be able to perform database structure updates with little or no
user
> > intervention.

Re:Comparing Two ADO Databases and Restructuring to Match


Quote
Johnnie Norsworthy wrote:
> How do you handle the batch transfer from the original file to the new file
> being sure to get all tables and fields?

You run through all the records in a loop, within which you run through
all the new fields in a loop, moving the data.  You need to check to see
if the new field exists (is assigned) in the old table.  You can
construct an insert statement for each record, or you can do a
table.insert, assign all the values, and then post.  It looks roughly
like this (from memory):

oldTable.first ;
while not oldTable.eof do begin
   newTable.insert ;
   for fldKtr := 0 to newTable.fieldCount -1 do begin
     if assigned(oldTable.fieldByName(newTable.fields[fldKtr].name) then
       newTable.fields[fldKtr].value :=        
         oldTable.fieldByName(newTable.fields[fldKtr].name).value ;
   end ;
   newTable.post ;
   oldTable.next ;
end ;

Re:Comparing Two ADO Databases and Restructuring to Match


That is what I assumed should be done (from a coder's viewpoint).  I just
thought ADO might have a simple and fast procedure built in the library for
this.  I read up on ADO and found that they stopped using the Jet Replication
Objects in MDAC 2.6 - so my hope of that is gone.

Thanks!

Quote
"Richard Grossman" <usenetu...@techIII.com> wrote in message

news:3D1D3DCD.1080305@techIII.com...
Quote
> Johnnie Norsworthy wrote:
> > How do you handle the batch transfer from the original file to the new
file
> > being sure to get all tables and fields?

> You run through all the records in a loop, within which you run through
> all the new fields in a loop, moving the data.  You need to check to see
> if the new field exists (is assigned) in the old table.  You can
> construct an insert statement for each record, or you can do a
> table.insert, assign all the values, and then post.  It looks roughly
> like this (from memory):

> oldTable.first ;
> while not oldTable.eof do begin
>    newTable.insert ;
>    for fldKtr := 0 to newTable.fieldCount -1 do begin
>      if assigned(oldTable.fieldByName(newTable.fields[fldKtr].name) then
>        newTable.fields[fldKtr].value :=
>          oldTable.fieldByName(newTable.fields[fldKtr].name).value ;
>    end ;
>    newTable.post ;
>    oldTable.next ;
> end ;

Re:Comparing Two ADO Databases and Restructuring to Match


(Johnnie slaps himself on forehead)
I had only considered restructuring the file with the data, not using my
definition file as a recepticle for the old format data.  This would be much
nicer as I would not even need to perform scarry restructuring in ADOX or via
SQL.

How do you handle the batch transfer from the original file to the new file
being sure to get all tables and fields?

-Johnnie

Quote
"Richard Grossman" <usenetu...@techIII.com> wrote in message

news:3D1BF51B.8020301@techIII.com...
Quote
> I usually compare the old table to the new table definition.  If there
> are differences, I create a new empty file with the new field
> definitions, and then load the old data to the new file, and finally
> rename the old file myfile.bak and the new file myfile.mdb.

> Johnnie Norsworthy wrote:
> > Is there anything out there to do this?

> > That is have a default, empty database in .MDB format with all needed
tables
> > and fields and a currently used .MDB format database and performing a
> > synchronization of the data structures without affecting the actual data
> > stored in the second database.

> > I want to be able to perform database structure updates with little or no
user
> > intervention.

Other Threads