Delete Query help 1

[hamking01]

I'm trying to create a delete query from tableA that has the same records in the paste errors table. tableA has records that were previously inputed. New data coming in has information of old data from tableA where the values may have changed. Upon inputting the new data into tableA, records with the same primary key (field: PO) are put into the paste errors table since it will cause duplicates. I've created a delete query having joins at the primary key between the paste errors and tableA. Delete * from tableA, where [paste errors].[PO]. This did not work so I've also tried Delete * from tableA, where [tableA].[PO]. This did not work either. When I switched from design view to datasheet view the correct records are showing up but will not run once I click the "RUN" button. Error that shows up is "Could not delete from specified tables" Error 3086.

There seems to be a permission problem or that the database is "read-only". I am the adminstrator of the database so have all the rights and permissions. The database is not open for read-only. Database is split into front-end/back-end with security setup.

Any suggestions would be greatly appreciated.

 

[scriverb]

Try this:

Delete * from tableA as A  where A.[PO] in (Select B.[PO] from [paste errors] as B);

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[hamking01]

This is what I've done:

DELETE RECV.PO, RECV.UPC, *
FROM RECV
WHERE (((RECV.PO) In (Select B.[Field0] from [Paste Errors] as B)) AND ((RECV.UPC) In (Select B.[Field2] from [Paste Errors] as B)));

I've tried just using "Delete *" but everytime I switch from datasheet view to sql view it always adds the recv.po, and recv.upc automatically. I should've noted that the paste errors table and recv table has 2 primary keys (PO & UPC).

But when I run the above query it returns 3753 records while the paste error table only has 2811 records. The query should be only returning 2811 records, as these are the records that I need to update. Is there something I'm doing wrong in the query?

 

[scriverb]

If you mean that the primary key for both tables is a combination of two fields [PO] and [UPC] then try this. Do not open the query in Design View. Just paste this into SQL and run it.

Delete * from tableA as A  where (A.[PO] & A.[UPC]) in (Select (B.[PO] & B.[PO])as PO_UPC from [paste errors] as B);

Let me know if this works. Make sure we are working with backup datafiles available.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[hamking01]

Thank you. Got it to work. Guess I'll just put the code behind a button since it always changes the sql after changing to design mode.

Another issue. Is there a way to change the data types of the paste errors table. I've already created a button that will delete the paste errors table. But everytime there's a new paste errors table created the data types are always "memo". I need to change some of them to 'text', 'number', and 'date'

 

[scriverb]

It is dependent on how you create the table. If through code then you can set the datatype. I would have to see how this is being done. Why not just create the table and when done with it delete the records leaving the table intact? Why delete and recreate?

A make-table query from existing records will generate a new table with fields of the same data-type as those used in the query.

let me know how you are doing it and we can smooth that process out.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[hamking01]

I get new data that I have to put into the database on a weekly basis. The data has already been formatted to import into my RECV table. Composite keys set to PO & UPC. However, sometimes the weekly data are updated info of the composite keys. So I just import the data and if there are any updated info Access pops warnings that some data can't be imported because of duplicate keys. Access automatically dumps the duplicates into a paste errors table. So I just need to check the paste errors table and find out which are the updates. With your help, I've now been able to delete the old data from the RECV table with the delete query above.

Reason for not creating table specifically, cause it just seems a lot easier since Access will create the table of duplicates for me. Note: When duplicates appear I have Access go ahead and import the non-duplicates and the duplicates are dumped to paste errors table. Also, this is only one of the imports. Other users import into different tables with the paste errors as a duplicate check. So I would were to create a table for this function, I would have to start creating tables for the other functions. It may get to be confusing for the users on which table they should use.

Looking into ALTER TABLE i've tried the following code:

AltDataType = "ALTER TABLE Paste Errors ALTER COLUMN Field1 datetime;"
DoCmd.RunSQL AltDataType

But this didn't seem to do anything. The data type was still the original "MEMO" and not "Date/Time"

Once again, thanx for all ur help on this.

 

[scriverb]

That won't work. I have never been able to modify a field type after it is set. But you can create a new table with the types that you want and then run an APPEND query to move the data from your Paste table to the new table with the correct datatypes. You can modify the field data from strings to numbers to dates etc using ACCESS functions and append the records to the new table as you wish. Then after running your other code just run a "Delete * from yournewtable;" to delete all the records preparing to start over again.

Post back if this sounds like a plan.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[hamking01]

Thanx, did it according to your suggestion. Copy paste errors table to temp table which then copied with correct field types into RECV table. Did everything in the background so users don't have to worry about the additional table.