cannot delete from specified tables

S

SuzyQ

I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 
D

Douglas J. Steele

Try

DELETE * FROM tblActivitiesForSchedule
WHERE ActivityID NOT IN (SELECT DISTINCT ActivityID
FROM tblActivitiesForSchedule)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SuzyQ said:
I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left
join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query,
there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the
records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text
true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 
J

Jerry Whittle

The problem is with the Left Join. You need an Inner Join if you want to
delete both the parent and all the child records. The easiest way to handle
this is to enable Referietial Integrity in the Relationship Window between
these two tables and enable Cascade Delete. Then delete the parent record
which will automatically delete any child records.

If you want to delete based on the child records, use a subquery to find the
parent records first.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


SuzyQ said:
I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 
S

SuzyQ

the child records have already been deleted - that's why I'm checking for a
null value in the child's ID field.

What's wierd is if I use the results from my delete query and change it to a
select query in the query builder, I can select those records and delete them
without a problem, but if I leave it as a delete query in the query builder,
I get the same error message when I try to run the query.

Jerry Whittle said:
The problem is with the Left Join. You need an Inner Join if you want to
delete both the parent and all the child records. The easiest way to handle
this is to enable Referietial Integrity in the Relationship Window between
these two tables and enable Cascade Delete. Then delete the parent record
which will automatically delete any child records.

If you want to delete based on the child records, use a subquery to find the
parent records first.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


SuzyQ said:
I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 
D

Dale Fye

It would help, if you are going to put an error message number in your
questions, if you would also most the error message text (so we don't have to
look it up).

You could use

DELETE * FROM Table1
WHERE TABLE1.ActivityID NOT IN(SELECT DISTINCT ActivitiyID FROM Table2)

or

DELETE * FROM Table1
WHERE NOT EXISTS(SELECT ActivityID FROM Table2 WHERE Table2.ActivityID =
Table1.ActivityID)

You might want to try both of these. The IN clause method (first above)
tends to be somewhat slow for large data sets.

----
HTH
Dale



SuzyQ said:
I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 
S

SuzyQ

I hope this doesn't post twice. There was an error posting the first time.

I spoke too soon. The records seemed to delete from the select query when I
highlighted them and hit the delete key, but they are still in the table, and
rerunning the select query they show up again.

SuzyQ said:
the child records have already been deleted - that's why I'm checking for a
null value in the child's ID field.

What's wierd is if I use the results from my delete query and change it to a
select query in the query builder, I can select those records and delete them
without a problem, but if I leave it as a delete query in the query builder,
I get the same error message when I try to run the query.

Jerry Whittle said:
The problem is with the Left Join. You need an Inner Join if you want to
delete both the parent and all the child records. The easiest way to handle
this is to enable Referietial Integrity in the Relationship Window between
these two tables and enable Cascade Delete. Then delete the parent record
which will automatically delete any child records.

If you want to delete based on the child records, use a subquery to find the
parent records first.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


SuzyQ said:
I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 
S

SuzyQ

I apologize. The error "message" is in the "subject line" I will try your
suggestions.

Dale Fye said:
It would help, if you are going to put an error message number in your
questions, if you would also most the error message text (so we don't have to
look it up).

You could use

DELETE * FROM Table1
WHERE TABLE1.ActivityID NOT IN(SELECT DISTINCT ActivitiyID FROM Table2)

or

DELETE * FROM Table1
WHERE NOT EXISTS(SELECT ActivityID FROM Table2 WHERE Table2.ActivityID =
Table1.ActivityID)

You might want to try both of these. The IN clause method (first above)
tends to be somewhat slow for large data sets.

----
HTH
Dale



SuzyQ said:
I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 
A

AG

Instead of DELETE...
Try DELETE DISTINCTROW...

--

AG
Email: npATadhdataDOTcom


SuzyQ said:
I'm getting the error message 3086 when I try to run the following code -

'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)

The code above results in the following value for strSQL when the error
occurs...

DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left
join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null

When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query,
there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the
records.
Thanks.

The entire routine is below

Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text
true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table

'delete the records added during this run of report
Dim strSQL As String

'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If

'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If

DoCmd.RunSQL (strSQL)

If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"

DoCmd.RunSQL (strSQL)
End If
End If

If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top