Hi
In my database there are 3 tables, linked with referential integrity.
The master table is Customers - which is linked to Items (one to many) and
Payment (1 to many).
I receive a file of transactions from our website and I can import the
Customers record in because it will reject any existing transaction
numbers automatically. If records are added to Customers, I want to add
the corresponding lines to Items and Payment. If the addition to Customers
fails - due to the transaction number already exisiting - I don't want the
corresponding lines adding to the other 2 files - because they will
already exist too.
What is the easiest way to make this happen?
I don't know about easiest, but ...
You need a way to identify which records have been successfully added. You
could do this with a helper table to record the primary keys of those
records, capturing the information in the execution of the append query that
adds the customers. Here's an example scheme, assuming that the primary key
of Customers is an autonumber or long integer field named CustID:
Table: CustomersAdded
------------------------------------
CustID (long, FK: Customers)
Query: qryCustomersAdd
------------------------------------
SELECT Customers.*, CustomersAdded.CustID As AddedID
FROM Customers INNER JOIN CustomersAdded
ON Customers.CustID = CustomersAdded.CustID;
Your code sequence to append the customers, items, and payments can now be:
With CurrentDb
' Clear any records of previously added customers.
.Execute "DELETE FROM CustomersAdded", dbFailOnError
' Add only the new customers from table/query NewCustomers,
' recording their IDs CustomersAdded.
.Execute _
"INSERT INTO qryCustomersAdd ( AddedID) " & _
"SELECT NewCustomers.*, NewCustomers.CustID " & _
"FROM NewCustomers LEFT JOIN Customers " & _
"ON NewCustomers.CustID = Customers.CustID " & _
"WHERE Customers.CustID Is Null", _
dbFailOnError
' Add the Items corresponding to the customers that were added.
.Execute _
"INSERT INTO Items " & _
"SELECT NewItems.* " & _
"FROM NewItems INNER JOIN CustomersAdded " & _
"ON NewItems.CustID = CustomersAdded.CustID", _
dbFailOnError
' Add the Payments corresponding to the customers that were added.
.Execute _
"INSERT INTO Payments " & _
"SELECT NewPayments.* " & _
"FROM NewPayments INNER JOIN CustomersAdded " & _
"ON NewPayments.CustID = CustomersAdded.CustID", _
dbFailOnError
' Optionally, empty CustomersAdded now:
' .Execute "DELETE FROM CustomersAdded", dbFailOnError
End With
I may have made a syntax error or two, but something along those lines ought
to work.
An alternative would be to do it all using recordset logic. The above
should be faster, but it does require that you create the CustomersAdded
table and qryCustomersAdd.
