Referential Integrity - How to update a table?

I might have taken a wrong turn trying to do an update on a database
the way I did but maybe somebody can help me out cause I'm stuck..

I have to update 12 tables in a database in a client application of
our company.
Since there were so many changes I didn't want to go record by record
through it.
I included the 12 tables in a 2004.mdb and since everything else's
equal I wanted to delete all the records in the destination table and
insert them from the source table.

There are however many relations between the tables and 'very logical
but annoying' I'm not able to delete the records in the Dest table
because for instance there are records in the 'Order' table related to
the parts table...
I don't want to allow cascading deletes because after the update
things has to be the same as before..

Can I bypass this or is there anyone who has a 'workaround'?

Much appreciated,

Mike


oh.. here's the code:

For Each tdFrom In dbFrom.TableDefs
If Mid(tdFrom.Name, 1, 4) <> "MSys" Then

Set rstSource = dbFrom.OpenRecordset(tdFrom.Name)
Set rstDest = dbTo.OpenRecordset(tdFrom.Name)

If rstSource.RecordCount > 0 Then
rstSource.MoveFirst
End If
If rstDest.RecordCount > 0 Then
rstDest.MoveFirst
End If

Do Until rstDest.EOF
If rstDest.RecordCount >= 1 Then
rstDest.Delete
End If
rstDest.MoveNext
Loop

Do Until rstSource.EOF

With rstDest
.AddNew
For Each fldTo In rstDest.Fields
fldTo.Value = rstSource(fldTo.Name).Value
Next
.Update
End With

rstSource.MoveNext
Loop

'Update ListBox
frmNieuwAssorti.List1.AddItem (tdFrom.Name)
End If
Next

re: Referential Integrity - How to update a table?

Sorry pal, but your stuck. The DBMS will not let you violate referential
integrity. You must execute the deletes in the correct order before you
process the inserts. One way to do this is to create a big delete proc on
the server that takes the primary key of the parent table. Then you can
execute the "inserts" one by one from the client.

"Mike Dole" <m_doolio@hotmail.com> wrote in message
news:fddbbab7.0408030706.39a0fcbe@posting.google.c om...[color=blue]
> I might have taken a wrong turn trying to do an update on a database
> the way I did but maybe somebody can help me out cause I'm stuck..
>
> I have to update 12 tables in a database in a client application of
> our company.
> Since there were so many changes I didn't want to go record by record
> through it.
> I included the 12 tables in a 2004.mdb and since everything else's
> equal I wanted to delete all the records in the destination table and
> insert them from the source table.
>
> There are however many relations between the tables and 'very logical
> but annoying' I'm not able to delete the records in the Dest table
> because for instance there are records in the 'Order' table related to
> the parts table...
> I don't want to allow cascading deletes because after the update
> things has to be the same as before..
>
> Can I bypass this or is there anyone who has a 'workaround'?
>
> Much appreciated,
>
> Mike
>
>
> oh.. here's the code:
>
> For Each tdFrom In dbFrom.TableDefs
> If Mid(tdFrom.Name, 1, 4) <> "MSys" Then
>
> Set rstSource = dbFrom.OpenRecordset(tdFrom.Name)
> Set rstDest = dbTo.OpenRecordset(tdFrom.Name)
>
> If rstSource.RecordCount > 0 Then
> rstSource.MoveFirst
> End If
> If rstDest.RecordCount > 0 Then
> rstDest.MoveFirst
> End If
>
> Do Until rstDest.EOF
> If rstDest.RecordCount >= 1 Then
> rstDest.Delete
> End If
> rstDest.MoveNext
> Loop
>
> Do Until rstSource.EOF
>
> With rstDest
> .AddNew
> For Each fldTo In rstDest.Fields
> fldTo.Value = rstSource(fldTo.Name).Value
> Next
> .Update
> End With
>
> rstSource.MoveNext
> Loop
>
> 'Update ListBox
> frmNieuwAssorti.List1.AddItem (tdFrom.Name)
> End If
> Next[/color]

re: Referential Integrity - How to update a table?

Hi Mike,

If you have a referential integrity set for the tables, then you cannot
bypass it unless you delete all the records from the related table(s). Why
are you trying to delete old records? Maybe you just need to update old
records with the new values?

--
Val Mazur
Microsoft MVP


"Mike Dole" <m_doolio@hotmail.com> wrote in message
news:fddbbab7.0408030706.39a0fcbe@posting.google.c om...[color=blue]
>I might have taken a wrong turn trying to do an update on a database
> the way I did but maybe somebody can help me out cause I'm stuck..
>
> I have to update 12 tables in a database in a client application of
> our company.
> Since there were so many changes I didn't want to go record by record
> through it.
> I included the 12 tables in a 2004.mdb and since everything else's
> equal I wanted to delete all the records in the destination table and
> insert them from the source table.
>
> There are however many relations between the tables and 'very logical
> but annoying' I'm not able to delete the records in the Dest table
> because for instance there are records in the 'Order' table related to
> the parts table...
> I don't want to allow cascading deletes because after the update
> things has to be the same as before..
>
> Can I bypass this or is there anyone who has a 'workaround'?
>
> Much appreciated,
>
> Mike
>
>
> oh.. here's the code:
>
> For Each tdFrom In dbFrom.TableDefs
> If Mid(tdFrom.Name, 1, 4) <> "MSys" Then
>
> Set rstSource = dbFrom.OpenRecordset(tdFrom.Name)
> Set rstDest = dbTo.OpenRecordset(tdFrom.Name)
>
> If rstSource.RecordCount > 0 Then
> rstSource.MoveFirst
> End If
> If rstDest.RecordCount > 0 Then
> rstDest.MoveFirst
> End If
>
> Do Until rstDest.EOF
> If rstDest.RecordCount >= 1 Then
> rstDest.Delete
> End If
> rstDest.MoveNext
> Loop
>
> Do Until rstSource.EOF
>
> With rstDest
> .AddNew
> For Each fldTo In rstDest.Fields
> fldTo.Value = rstSource(fldTo.Name).Value
> Next
> .Update
> End With
>
> rstSource.MoveNext
> Loop
>
> 'Update ListBox
> frmNieuwAssorti.List1.AddItem (tdFrom.Name)
> End If
> Next[/color]

re: Referential Integrity - How to update a table?

Thanks for your help guys!

I didn't want to go through the database table by table, record by
record, check the existence, update the record it it existed, inserted
if not, delete if neccesary, etc, etc.

But at the end I:
- put the (empty) tables who gave me integrity problems in the 'source
database'.

made a script that:
- filled them 'TEMPORARY' with the records from the original database
- deleted these records in the original database
- deleted the records of the remaining tables and inserted the new
ones from my source database.
- Inserted the records from the Temporary tables back into the
original.
- Deleted the records in the temporary tables (in case our customer
would run the script twice..).

It works, you can run it multiple times, but the code's not a pretty
sight to look at..

Might have been easier going through them one at a time and do the
update thing,
why do I always want to take the shortcut when there isn't one??

Anyway if it works under all circumstances and it's stable I'm ok with
it.

It'll probably be the last vb6 app. because the program's will be
obsolete in a few months...
The last year I did all my projects in vb.net, doing the same thing
with datasets would be much easier I guess.
But that's out of the question considering the problems I would run
into with clients not having the framework and all...

Thanks again,

Mike

re: Referential Integrity - How to update a table?

"Mike Dole" <m_doolio@hotmail.com> wrote in message
news:fddbbab7.0408040806.51460133@posting.google.c om...
| Thanks for your help guys!
|
| I didn't want to go through the database table by table, record by
| record, check the existence, update the record it it existed, inserted
| if not, delete if neccesary, etc, etc.
|
| But at the end I:
| - put the (empty) tables who gave me integrity problems in the 'source
| database'.
|
| made a script that:
| - filled them 'TEMPORARY' with the records from the original database
| - deleted these records in the original database
| - deleted the records of the remaining tables and inserted the new
| ones from my source database.
| - Inserted the records from the Temporary tables back into the
| original.
| - Deleted the records in the temporary tables (in case our customer
| would run the script twice..).
|
| It works, you can run it multiple times, but the code's not a pretty
| sight to look at..
|
For this or future tasks, you might look into a series of update,
append, and delete queries. If the records are identified with primary
keys, for each table you can run:

1. An update query, joining existing records in both tables, updating
all data values from source to the target.
2. An append query, left joining one table to the other, setting a where
condition of PrimaryKey Is Null in the target, which will append any new
records.
3. A delete query, right joining one to the other, where PrimaryKey Is
Null in the source, which will delete records in the target that no
longer exist in source.

Typically you would do the deletions in a table order so that dependent
data is deleted first (i.e. delete the orders, then the customers), and
do the appends in the reverse order (add the new customers, then add
their new orders).

(from comp.lang.basic.visual.database)