1. Problem with appending one table to two tables - referential integrity - help please
Hi there,
First of all, we are using Access 97 clients and SQL 6.5 backend.
What I'd like to do is use an append query to append data from one table to
two other tables. I've already setup a query to show all the fields from
the two tables so I can use that query in the append to destination.
Problem: CustomerID in the Customers table is linked to CustomerID in the
Bookings table - and it is not an auto-number, it is entered by hand from
firstname/lastname fields. I've gotten around that problem by using an
autonumber in the table where I want to append from - and include that
autonumber field in the append query. It seems to work alright if I have
only fields from the Customers table (where customerid is the primary key),
but whenever I add fields from the booking table (which is linked from
customers table via customerid, but the primary key is bookingid - not
really used much) I get the Access 97 key violation error.
There must be some way to add the customerid to both tables at the same
time without the key violation / referental integrity error message. I've
tried already to put no customerid in the append query - doesn't work.
Append CustomerID to both CustomerID fields in the append query - doesn't
work. Append CustomerID only to the Customers table - doesn't work. Append
CustomerID only to the Bookings table - doesn't work, but at least the
error message is different - field not updateable - which is logical of
course.
I tried to define the relationship as cascade update related fields, which
I believe should fix that problem, but I can't seem to select it. My guess
is that's because it comes from the SQL server. On the server side the
Customers.CustomerID is the primary key in the Customers table and a
foreign key in the bookings table.
Thanks for your time & help! :-)
Thomas
--
***********************************************************************
Thomas Fischer The Road goes ever on and on
Planet Holiday Out from the door where it began.
Now far ahead the Road has gone,
Anacott Travel Network Let others follow it who can!
Internet Reservations Let them a journey new begin,
Tel: +66-76 263-239 But I at last with weary feet
Fax: +66-76 263-586 Will turn towards the lighted inn,
My evening-rest and sleep to meet.
http://www.planetholiday.com - J.R.R Tolkien
***********************************************************************
2. foxpro 2.7 wish list
3. Help - Problem appending new table
4. SQL Anywhere and ReportSmith
5. Help on appending new records in SQL Tables
6. West Coast-Progress V6.3->V7 chr-WSI
7. Error 3264 - Cannot Append TableDef or Index PLEASE HELP
8. PICK Programmer needed in SC
9. need help please:joined tables,add new entries based on one table columns
10. Create new table as select Help PLease!!!!
11. Error when Creating New Publication .. Please Help
12. Select into new table and append