Using SQL to delete all records from a table

[GraeagleBill]

In opening a form designed to serve two purposes, the RecordSource is changed at open time. When a temporary query is used, the intent is to clear all the records that would have lingered from previous sessions of the app. The SQL DELETE statement fails with a FROM clause error 3131. In the current situation, the query (table) is already empty.

Private Sub Form_Open(Cancel As Integer)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  If we're opening for a new subscriber, we need to bind the controls to
'  a temporary empty duplicate query.  The rs.AddNew code will put the new
'  subscriber where he/she belongs depending on what actions they choose.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*


If Len(Me.OpenArgs & "") = 0 Then
    bolNewSubscriber = True
    Me.lblHeader.Caption = "Profile for ""New Subscriber"""
    DoCmd.RunSQL "DELETE * FROM QTempSubscribers)"
    Me.RecordSource = "QTempSubScribers"
Else
    Me.lblHeader.Caption = "Edit Subscriber Profile"     'EDit an existing subscriber
    Me.RecordSource = "QSubscribers"                     'Our current collection
    Me.Filter = "SubID = " & Me.OpenArgs                 'The subscriber being serviced
    Me.Requery                                           'Okay, just the one record of interest
    bolNewSubscriber = False                             'Boolean flag for the SAVE code
End If


'*=*=*=*=(Initialize required fields)=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
strCompl = Split(strReqCtls, ";")
TestCompl




End Sub

[June7]

What is exact message of error 3131?

If you delete from a query, then it should delete from table the query is based on so why not delete from table?

What exactly is "a temporary empty duplicate query"? What is the SQL statement?

Why do you need to even change RecordSource for a new subscriber? Open form to add mode:
DoCmd.OpenForm "formname", , , , acFormAdd

[Welshgasman]

Perhaps do a dcount on that query first.
I have never tried to delete from an empty query, so no idea what it might do.

[madpiet]

See the syntax error now? ... there shouldn't be a closing parenthesis at the end.

DoCmd.RunSQL "DELETE * FROM QTempSubscribers)"

should be

DoCmd.RunSQL "DELETE * FROM QTempSubscribers"

[GraeagleBill]

What is exact message of error 3131?

Invalid FROM clause

If you delete from a query, then it should delete from table the query is based on so why not delete from table?

Correct. The SQL fails even if a substitute the name of the table the query is based.

What exactly is a "a temporary empty duplicate query"? What is the SQL statement?

Sort of a goofy way of putting it. The temporary table is essentially a structure copy of the subscriber table. The query based on that table is what I referred to as the "temporary empty duplicate query"

DoCmd.OpenForm "formname", , , , acFormAdd

The difficulty I had in doing exactly as you suggest is that new subscriber was being entered into the subscriber table with incomplete information. As I have it now, the new entry is subjected to a field-by-field check before being allowed to save. Maybe there's a better way to control that?

[GraeagleBill]

I think copy/paste did it to me again!!!

[Welshgasman]

No, not really. It should show us what you have. If you type it out again, you might get it right.

[GraeagleBill]

Just gives the usual Access warning "About to delete........"

[GraeagleBill]

No, not really. It should show us what you have. If you type it out again, you might get it right.

Not sure which post you're referring?

[GraeagleBill]

Okay June, the whole scheme be it the best approach or not, was not functioning properly because the code needs to run at the end of the OnLoad event. Too soon in the OnOpen.

[madpiet]

if you can specify the table name where the data is, you can pass it kind of as a parameter and build the Delete statement on the fly.
using CurrentDb.Execute instead of DoCmd.RunSQL will let you trap errors and allow some to bubble up if you want...

<insert aircode siren here>

public function ClearTable( byval strTableName as string )

'-- should likely check here to see if the table exists but there's code around that does that. I think the error message is 3265 if it doesn't exist

public function ClearTable (byval strTableName as String) as boolean

blnSuccessful = False
strSQL = "DELETE FROM [" & strTableName & "];"
CurrentDb.Execute strSQL, dbFailOnError
blnSuccessful = True

end function

[Welshgasman]

Not sure which post you're referring?

#6, right after your post.

[madpiet]

When a temporary query is used, the intent is to clear all the records that would have lingered from previous sessions of the app.

Sounds like you're not cleaning up properly in your code... at the end of the code that uses the temporary table, you should run the delete or drop the table there.