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.TableDef s
If Mid(tdFrom.Name , 1, 4) <> "MSys" Then
Set rstSource = dbFrom.OpenReco rdset(tdFrom.Na me)
Set rstDest = dbTo.OpenRecord set(tdFrom.Name )
If rstSource.Recor dCount > 0 Then
rstSource.MoveF irst
End If
If rstDest.RecordC ount > 0 Then
rstDest.MoveFir st
End If
Do Until rstDest.EOF
If rstDest.RecordC ount >= 1 Then
rstDest.Delete
End If
rstDest.MoveNex t
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.MoveN ext
Loop
'Update ListBox
frmNieuwAssorti .List1.AddItem (tdFrom.Name)
End If
Next 4 2315
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_******@hotma il.com> wrote in message
news:fd******** *************** ***@posting.goo gle.com... 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.TableDef s If Mid(tdFrom.Name , 1, 4) <> "MSys" Then
Set rstSource = dbFrom.OpenReco rdset(tdFrom.Na me) Set rstDest = dbTo.OpenRecord set(tdFrom.Name )
If rstSource.Recor dCount > 0 Then rstSource.MoveF irst End If If rstDest.RecordC ount > 0 Then rstDest.MoveFir st End If
Do Until rstDest.EOF If rstDest.RecordC ount >= 1 Then rstDest.Delete End If rstDest.MoveNex t 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.MoveN ext Loop
'Update ListBox frmNieuwAssorti .List1.AddItem (tdFrom.Name) End If Next
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_******@hotma il.com> wrote in message
news:fd******** *************** ***@posting.goo gle.com... 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.TableDef s If Mid(tdFrom.Name , 1, 4) <> "MSys" Then
Set rstSource = dbFrom.OpenReco rdset(tdFrom.Na me) Set rstDest = dbTo.OpenRecord set(tdFrom.Name )
If rstSource.Recor dCount > 0 Then rstSource.MoveF irst End If If rstDest.RecordC ount > 0 Then rstDest.MoveFir st End If
Do Until rstDest.EOF If rstDest.RecordC ount >= 1 Then rstDest.Delete End If rstDest.MoveNex t 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.MoveN ext Loop
'Update ListBox frmNieuwAssorti .List1.AddItem (tdFrom.Name) End If Next
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
"Mike Dole" <m_******@hotma il.com> wrote in message
news:fd******** *************** ***@posting.goo gle.com...
| 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.databas e) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Grant McLean |
last post by:
Hi
First a simple question ...
I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into "access_log", the referential integrity
triggers generate these queries:
SELECT 1 FROM ONLY "public"."application_type" x
|
by: Jimmie H. Apsey |
last post by:
Referential Integrity on one of our production tables seems to have been
lost. I am running Postgres 7.1.3 embedded within Red Hat
kernel-2.4.9-e.49.
Within that I have a table with referential integrity constraints which
no longer work.
I do not know how to disable referential integrity on a column in a table.
I do not know how to...
|
by: Geisler, Jim |
last post by:
So, as far as I know, PostgreSQL does not have any way of verifying the loss
of referential integrity.
Are there any recommended methods or utilities for checking referential
integrity in a PostgreSQL database?
|
by: heyvinay |
last post by:
I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:
Transaction Table
-TranId
-Calc Amount
Table 1 (the amount is inserted into the transaction table)
- Tb1Id
|
by: Jeff North |
last post by:
I'm using Microsoft SQL Server Management Studio Express 9.00.2047.00
and expriencing problems with setting referential integrity on a link
table. The tables' schema is as follows:
-------------------------------------------------------------------
CREATE TABLE competencies (
CID bigint identity(1,1) CONSTRAINT pk_CID PRIMARY KEY,...
| |
by: Andrew R |
last post by:
Hi
I'm creating a series of forms, each with with around 15-20 text boxes.
The text boxes will show data from tables, but are unbound to make them
more flexible.
I want the form to be used for both adding new data and modifying
existing data. I have created a save button on the form.
When the user clicks the save button, the code...
|
by: moskie |
last post by:
Is there a way to run an alter table statement that adds a constraint
for a foreign key, but does *not* check the existing data for
refrential integrity? I'm essentially looking for the equivalent to SQL
Server's NOCHECK flag.
I am able to create equivalent keys in the Relationships screen, as
long as the "Enforce referential integrity" box...
|
by: Wayne |
last post by:
I've inadvertently placed this post in another similar newgroup, and I
apologise if you get it twice.
I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database. The
data in the lookup fields that relate to the backend tables will
never
change, but a descriptor for...
|
by: ApexData |
last post by:
Access2000, using a continuous form.
I’m getting a message that say “you cannot add or change a record
because a related record is required in table Employee”.
This occurs in all my combobox fields that have referential integrity
linked to another table. I do not have the field in the table set as
required, and I do not want to require...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |