Deleting records in a table that has a one to many relationship with another table

[odrap]

In my database i have a table 'tblClients" who has a one to many relationship with the table 'tblOrders'.
This table 'tblClients' is a 100% copy of a table with the same name that resides in another database. Each moment, the data in my table must be exactly the same as the data in the table in the other database.
Data of several records in the original table can be changed and in the same time new records added,so the updating can mean here updating existing records and in the same time adding of new records. Futhermore , the changing of the data in existing records isn't seldom just changing the value of one column in the table,this is more then often different from record to record. I have no direct connection with the other database. The changed table is sent to me as a attachement of a email in the form of a csv. Making a routine that try to find out for every existing record what is changed in the original table and in the meantime finding out what are the new records, seems to me not so simple and maybe very time consuming.
For these reasons, i believe i'm better of by deleting all records in my table and after that inserting all records of the changed table in my table.
This can be done easily with VBA code. However, because there is a relationship as i mentioned hereabove, the deleting of some records in my table can't be done. What can i do to get round this difficulty. I use MS ACCESS 2007, so making use of replication to solve the whole problem isn't possible here.

 

[Galaxiom]

The change table will not include records that didn't change so deleting your records is not an option.

You need an update query that overwrites the data in your records when there is a record in the change table.

If there are new records in the change table you will also need an append query to add them to your tables.

 

[dfenton]

I use MS ACCESS 2007, so making use of replication to solve the whole problem isn't possible here.

WRONG, WRONG, WRONG.

Access 2007 and the new ACE database engine both fully support Jet replication. It is only the new ACCDB format that lacks replication. An MDB from an earlier version of Access, or an Access 2007 MDB can both be replicated, and all Jet replication features are fully supported.

However, before you go that route, you should evaluate whether or not you can host the app on a Windows Terminal Server (all server versions of Windows starting with 2000 by default include Windows Terminal Support, so this is *very* easy to implement if there's a Windows server in either of the offices and there's reasonable Internet access -- even dialup can be sufficient).

 

[odrap]

Thanks very much

 

[gemma-the-husky]

but you dont need to delete/reinsert

do these steps

a) link (or import) the new data

b) have a query that joins the newdata to the existing data on the primary key.
turn this into an update query that updates all the data in your existing table

c) this didnt deal with any new data so
have a find unmatched query to find any new data in the linked/imported table
turn this into an append query, to load the new data.

I do this as standard in a number of apps

 

[Simon_MT]

I agree with David, why would you want to analyse data changes when these remote locations can do it themselves within one dataset on a Terminal Server.

Simon