Hi all - I am trying to create a single sql statement that will delete unmatch recoreds. It is a fairly simple action on the surface, however it involves a subquery that is a UNION. I keep getting errors. Can someone point me in the right direction?. I can create the UNION query, and then create another query that uses the Union query. However, I would really like to combine the two into one SQL statement so it can be placed in VBA without any queries saved in the db. Here's the "delete" query: - DELETE tblActMap.Job, tblActMap.Phase, tblActMap.Act, tblActMap.S_Key, qryAllActivities.S_Key
-
FROM tblActMap LEFT JOIN qryAllActivities ON tblActMap.S_Key = qryAllActivities.S_Key
-
WHERE (((qryAllActivities.S_Key) Is Null));
-
Here's the UNION query (qryAllActivities in the above): - SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key
-
UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key
-
UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key;
-
Again, I would like a single SQL that combines the two. My thought is that this would be a Subquery, but I can't get it to work...
Thanks in advance.
Does this work? - LEFT JOIN (SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key
-
UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key
-
UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS qryAllActivities
5 5414
Does this work? - LEFT JOIN (SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key
-
UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key
-
UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS qryAllActivities
Rabbit - Yes and no. It's much closer than I got. Funny how when you see the answer it seems obvious. I am still getting the following error:
Runtime Error '3128'
Specify the table contining the records you want to delete.
Here's the code I used: - strSQL = "DELETE tblActMap.Job, tblActMap.Phase, tblActMap.Act, ActList.S_Key " & _
-
"FROM tblActMap LEFT JOIN " & _
-
"(SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key " & _
-
"UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key " & _
-
"UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) " & _
-
"AS ActList " & _
-
"ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));"
-
Debug.Print strSQL
-
db.Execute strSQL, dbFailOnError
-
(Note: I changed "qryAllActivities" to "ActList" while testing...)
The debug.print statment gave me this: - DELETE tblActMap.Job, tblActMap.Phase, tblActMap.Act, ActList.S_Key FROM tblActMap LEFT JOIN (SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS ActList ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));
-
and if I copy/paste it into a blank sql edit screen and run it, it works fine. Is it something to do with the db.Execute?
You'll be able to tell if it is a db.execute problem by substituting the following for the db.execute statement: - DoCmd.SetWarnings False
-
DoCmd.RunSQL strSQL
-
DoCmd.SetWarnings True
The RunSQL method runs an action query in a very similar way to running it in the query editor (which is why you need to set warnings off, as otherwise the DB would ask for confirmation of the delete).
On occasion I've found that an apparently valid update or other action query fails to run from the Execute method of the application object, yet runs fine from the RunSQL method of the DoCmd object. If that's the case here it's another quirk of the database engine I guess.
-Stewart
Stewart & Rabbit -
Thanks for the input, both of you were very helpful. Using the "DELETE ..." sql code and DoCmd.RunSQL command (Stewart) gave me the error:
Runtime Error: '3027':
Database or Object is Read-Only
I did some help-file reading and from what I can tell the recordset generated from the SQL code creates a read-only recordset (rs.Updatable = False) because of the joined subquery. To solve the problem, I used the following: - strSQL = "SELECT tblActMap.Job, tblActMap.Phase, tblActMap.Act, tblActMap.ActTitle, tblActMap.S_Key " & _
-
"FROM tblActMap LEFT JOIN " & _
-
"(SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key " & _
-
"UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key " & _
-
"UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS ActList " & _
-
"ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));"
-
-
'Debug.Print strSQL
-
Set rs = db.OpenRecordset(strSQL)
-
If rs.EOF And rs.BOF Then
-
'No records to delete
-
Else
-
rs.MoveLast
-
rs.MoveFirst
-
Do While Not rs.EOF
-
strSQL = "DELETE tblActMap.* FROM tblActMap WHERE (((tblActMap.S_Key) = '" & rs!S_Key & "'));"
-
'Debug.Print strSQL
-
db.Execute strSQL, dbFailOnError
-
rs.MoveNext
-
Loop
-
End If
-
This code uses the SQL Rabbit help with as a SELECT rather than a DELETE, and then deletes the records from the tblActMap one at a time using the rs to identify records to delete. We will never have very many records to delete (larges I have seen is around 25 records), so this process will runn fast enough. I would prefer a "one sql statement" solution, but it doesn't look like it's going to happen.
Thanks for all your help.
For whoever wants to know... I found the solution to the original question. The answer was to put "DISTINCTROW" in the SQL Statement so Access would know to ignor the non-primary key joined table. Here's the solution: - strSQL = "DELETE DISTINCTROW tblActMap.* " & _
-
"FROM tblActMap LEFT JOIN " & _
-
"(SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key FROM tblMnth GROUP BY tblMnth.Job, tblMnth.Phase, tblMnth.ActID, tblMnth.S_Key " & _
-
"UNION SELECT tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key FROM tblProj GROUP BY tblProj.Job, tblProj.Phase, tblProj.ActID, tblProj.S_Key " & _
-
"UNION SELECT tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key FROM tblWkly GROUP BY tblWkly.Job, tblWkly.Phase, tblWkly.ActID, tblWkly.S_Key) AS ActList " & _
-
"ON tblActMap.S_Key = ActList.S_Key WHERE (((ActList.S_Key) Is Null));"
-
db.Execute strSQL, dbFailOnError
-
Thanks again to Rabbit and Stewart for their help.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ms |
last post by:
Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one
of the duplicates to be imported into the live table. A unique record is based
on a composite key of 3...
|
by: KT |
last post by:
Is there any one click solution that would do the trick? I would like
to create a button, so the person who maintains the database can
perform clean up work to delete duplicate records which...
|
by: jeff |
last post by:
Dear all,
i want to delete all records in a table (ms access database)
now my method used is not good, i have to loop all record in the datatable
and delete it one by one.
however, i can't :
|
by: Theodore70 |
last post by:
I am trying to delete duplicate records in an access table using VB. Not a programmer here, but this was one of the codes that I found on the web. I made some modifications to it, but it still did...
|
by: nico3334 |
last post by:
I am trying to delete all records from all tables in Access through VB coding. I would like to do it dynamically in case new tables are added. Here is what I have so far:
I have a form with a...
|
by: kcdoell |
last post by:
Hello:
I am trying to write a code that will delete all records found in my DAO recordset Below is the code I have so far:
'Procdure to give the user the ability to delete all records
'for a...
|
by: jedgretzky |
last post by:
I am having trouble creating a query that will delete duplicate records. It seems to me that this isn't that hard of a query, but I just can't seem to get it working. What I have is a table that...
|
by: Dilip1983 |
last post by:
Hi All,
I want to delete duplicate records from a large table.
There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state.
First of all when i tried to rebuild...
|
by: WyvsEyeView |
last post by:
Upon clicking Delete to delete the current record on frmTopics, I want several things to happen:
1) Display a custom message rather than Access's standard "You are about to delete n records" one....
|
by: mark007 |
last post by:
I have two tables invoice and so_salesorderdetail1, I want to delete those records from invoice which are not in so_salesorderdetail1.
these tables don't have any unique key so I am trying to make...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |