Recordset delete problem.

[Richard Jones]

Please help.

Take a look at this SQL string...

SELECT tblPod_NEW.DEVICEID, tblPod_NEW.PICKNO, _
tblPod_NEW.ADDRESS1, tblPod_NEW.ADDRESS4, tblPod_NEW.TTLWEIGHT, tblPod_NEW.NOMOD, tblPod_NEW.MODS, tblPod_NEW.SIGN, tblPod_NEW.CONTACT, tblPod_NEW.TIMEDATE
FROM tblPod_NEW LEFT JOIN tblPod_DB ON tblPod_NEW.PICKNO = tblPod_DB.PICKNO
WHERE ((tblPod_NEW.SIGN) Is Null)

It is basically 2 tables which contain the same information.
tblPod_DB has a SIGN field with data entered.
tblPod_NEW has a SIGN field with no data entered.

Ive been trying to create a recordset that compares the 2 tables. If a record exists in _DB with data in the SIGN field, the resultant recordset should contain the duplicate entry in _NEW but without the SIGN data. And this then needs to be deleted from the _NEW table.

Ive managed to create the recordset resultant but cant work out how to delete it from _NEW.

Can anyone please help?

[Paul Warren]

Create a query within the database itself ( or view or whatever it's called on your system ) from the SQL statement you've worked out. Open a recordset using that query and delete all the records which are returned using the usual .delete method with the recordset. Using Access the steps would be :

1. Copy the SQL statement to the clipboard.
2. Create a new query in Access using the designer then open SQL view.
3. Paste the query in and then run it to check it works
4. Save the query
5. Close Access

In your code do something like( this is ADO but DAO's not much different ) :

Dim rsTemp as New Recordset

rsTemp.Open "Select * FROM Query1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"

Then just loop through deleting records. Change the 4.0 for 3.51 if you're not using Access 2000.

Hope this helps.

[JHausmann]

Originally posted by Richard Jones

Please help.

Take a look at this SQL string...

SELECT tblPod_NEW.DEVICEID, tblPod_NEW.PICKNO, _
tblPod_NEW.ADDRESS1, tblPod_NEW.ADDRESS4, tblPod_NEW.TTLWEIGHT, tblPod_NEW.NOMOD, tblPod_NEW.MODS, tblPod_NEW.SIGN, tblPod_NEW.CONTACT, tblPod_NEW.TIMEDATE
FROM tblPod_NEW LEFT JOIN tblPod_DB ON tblPod_NEW.PICKNO = tblPod_DB.PICKNO
WHERE ((tblPod_NEW.SIGN) Is Null)

It is basically 2 tables which contain the same information.
tblPod_DB has a SIGN field with data entered.
tblPod_NEW has a SIGN field with no data entered.

Ive been trying to create a recordset that compares the 2 tables. If a record exists in _DB with data in the SIGN field, the resultant recordset should contain the duplicate entry in _NEW but without the SIGN data. And this then needs to be deleted from the _NEW table.

Ive managed to create the recordset resultant but cant work out how to delete it from _NEW.

Can anyone please help?

It sounds like you want to do two things.
1) you want to check tblPod_New for a record match with tblPod_DB.
2) if there's a match to the primarykey field, you want to set the sign field on tblPod_new to null or no data

Assuming that PICKNO is your "uniquekey", the following should set sign in TBLPod_New (for records that exist in TblPod_DB) to empty.

update tblPod_New set sign='' where pickno in (select pickno from tblPod_DB where pickno<>'')