VBA, SQL to select and then delete records 1

[Mke85]

Good day all,

Hoping for some help develop a vba sql statement within Access. This is what i'm trying to do:

I have a query which identifies records that are present in one table [TRACKER] and not in another [CF]: This is the sql statement from the design view:

SELECT DISTINCTROW Tracker.CF, Tracker.Lvl, Tracker.Loc, Tracker].SD, Tracker].eD, Tracker.Status, Tracker.TM, CF.CF, CF.Lvl, CF.Loc, CF.SD, CF.eD, CF.Status, CF.TM
FROM CFSR RIGHT JOIN Tracker ON CF.[CF] = Tracker.[CF]
WHERE (((CF.CF) Is Null) AND ((CF.Lvl) Is Null) AND ((CF.Loc) Is Null) AND ((CF.SD) Is Null) AND ((CF.eD) Is Null) AND ((CF.Status) Is Null) AND ((CF.TM) Is Null));

This query work perfectly fine. I would then like to take the records from the Tracker table that are displayed from the query and delete them. This is where i'm running into the problems.

I've come up with a simple delete statement, however it take a very long time to run:

sqlstr = "delete * " & _
" from Tracker" & _
" where not exists (select * from cf where Tracker.cf = cf.cf)"
'CurrentDb.Execute sqlstr

Is there a better way I could go about completing this task?

Thanks!

 

[dhookom]

Does your Tracker table have a primary key? I typically append the primary key field to a new table and make it a primary key. Then use the new table in your delete query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Mke85]

PK in the Tracker table is CF. PK in the CF table is an unlisted field called ID.

Thanks for the help

 

[dhookom]

I would use the unmatched query to append the CF field into a new table. Make this field the primary key and then create a query like:

First confirm the results with a select query:

SELECT * FROM Tracker WHERE CF NOT IN (SELECT CF FROM [YourNewTableName])

Then create your delete query:

DELETE * FROM Tracker WHERE CF NOT IN (SELECT CF FROM [YourNewTableName])

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Mke85]

I moved to this code instead:

sqlstr = "delete tracker.*, tracker.cf" & _
" from Tracker" & _
" where ((tracker.cf) not in (select cf.cf from cf))"
CurrentDb.Execute sqlstr

Is that an unmatched query as well? I'm not overly familiar with that term.

As well, why would i want to create a new table and put those records there?

 

[dhookom]

You stated your query took a very long time to run. Sometimes it is more efficient to create an intermediary table. I would expect your code to work. Did you try it as a SELECT query first?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Mke85]

By a long time, only about 15 seconds lol but I would still prefer to speed it up if possible or at least find the most efficient way to select and delete the records.

I have my original query that selects the data to be deleted, however i cant seem to figure out how to reference that query, and actually delete those records. My original query is posted above.

I've been able to create other vba sql statements that reference queries to select, update and insert to and from tables but deleting has stumped me.

When referencing my deletion query, i've received the error message that I must indicate which table to delete from.

 

[Andrzejek]

If "PK in the [blue]Tracker[/blue] table is [blue]CF[/blue]. PK in the [blue]CF[/blue] table is an unlisted field called [blue]ID[/blue]. " and those two PKs point to related records, your Delete statement should be:

[pre]
sqlstr = "delete from [blue]Tracker[/blue] where [blue]CF[/blue] not in (select [blue]ID[/blue] from [blue]CF[/blue])"
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy,
I don't believe that is correct. Consider a scenario where Tracker is a master project table and CF are the tasks related to the projects. I'm assuming Mke85 wants to delete any records from the Project table that do not have associated/related tasks in the CF table.

MKE85 is my assumption correct?

Do you see the correct records to delete in this query:

SELECT tracker.*
from Tracker
where tracker.cf not in (select cf.cf from cf)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

You (may be) right,Duane. The CF field is the PK - FK connection between the two tables. (I guess) It is hard to read it where table and a field have the same (short) name...


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I agree with the naming. Someone created naming conventions for multiple reasons and this is one.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Mke85]

Sorry for the response delay gents and thanks for the assistance to this point.

You are correct with what I would like to do Duane. CF is my master tasking table and Tracker is another table with basically the same data as CF however as the records are deleted from the CF table i would like to delete them from tracker as well. CF is a shared table that is used by many users, so i've created other sql statements to select, insert and update all the records from CF to Tracker in order to create a "local" table which only has a few users.

My apologies about he field / table names. They are actually much larger and unique however i've "simplified" them for ease of modification on here, which may have failed lol..

The suggested select statement seems to be giving me problems when i try to run it:

sqlstr = "select Tracker.*" & _
" from Tracker" & _
" where tracker.cf not in (select cf.cf from cf)"
CurrentDb.Execute sqlstr

I'm receiving an error 3065 cannot execute a select query.

Thanks again,
Mike

 

[dhookom]

Mike,
Ignore the code of a minute and paste the suggested SQL into a new, blank query. "Execute" is used for "action" queries.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Mke85]

That worked Duane, thanks.

Now how could I take those selected records and delete them?

Thanks,
Mike

 

[dhookom]

Mke85,
You should use TGML to format you posts like Andy & I use. It makes your posts easier to read.

Try this code. I added [highlight #FCE94F], dbFailOnError[/highlight] which is good practice but optional.

sqlstr = "DELETE Tracker.*" & _
" from Tracker" & _
" where tracker.cf not in (select cf.cf from cf)"
CurrentDb.Execute sqlstr[highlight #FCE94F], dbFailOnError[/highlight]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Mke85]

Duane,

Code works, thanks, however it remains slow like my original code.

I might try to develop a if or case in which it will skip this code if there are no records to delete, to hopefully speed things up a bit when I sync my tables.

Cheers,

 

[dhookom]

Yes 15 seconds is a long time ;-). I provided another solution that might run faster but I don't think you tried it.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016