what is best way to clear tables?

[Luke Storer]

I need to b able to clear records from 3 tables with one click

I tried opening each as a record set and looping through deleting each in turn with a loop, however this I know to be poor practice and it also errors as i cannot delete a record that is part of a relationship.

is there a more suitable way to do this?

 

[boblarson]

3 delete queries:

Dim strSQL As String

strSQL = DELETE * FROM YourTableName
CurrentDb.Execute strSQL
strSQL = DELETE * FROM YourTableName2
CurrentDb.Execute strSQL
strSQL = DELETE * FROM YourTableName3
CurrentDb.Execute strSQL

 

[Luke Storer]

hmm, couldn't get this to work...

kept getting error "expected end of statement"

so i wrote 3 Delete Queries, but every time they run i get an access warning about modifying tables, ordered them in a manner so that the link table is deleted first meaning the relationship error no longer occurs

 

[boblarson]

Oh, for my part I forgot to encapsulate the part after strSQL = between quotes:
Dim strSQL As String

strSQL = "DELETE * FROM YourTableName"
CurrentDb.Execute strSQL
strSQL = "DELETE * FROM YourTableName2"
CurrentDb.Execute strSQL
strSQL = "DELETE * FROM YourTableName3"
CurrentDb.Execute strSQL

 

[Luke Storer]

ah brilliant, that worked a treat - thankyou

 

[neileg]

so i wrote 3 Delete Queries, but every time they run i get an access warning about modifying tables, ordered them in a manner so that the link table is deleted first meaning the relationship error no longer occurs

You can use SetWarnings() to turn the warnings off before you run the queries and then on again afterwards.

 

[KennyDies]

I use a custom function called Zap...

Public Function Zap(strTable As String) As Long
'Purpose: Deletes all data in table
'Returns: -1 on error else no of records affected

Dim strSQL As String

strSQL = "DELETE * FROM " & strTable
Zap = ExecuteSQL(strSQL)

End Function

 

[RoyVidar]

Perhaps you should give us your ExecuteSQL function, too?

 

[KennyDies]

Appologies...

Public cnn As New ADODB.Connection

Public Function ExecuteSQL(strSQL As String) As Long

Dim cnn As New ADODB.Connection

On Error GoTo err_ExecuteSQL

DoCmd.SetWarnings False

ConnectLocal.Execute strSQL

DoCmd.SetWarnings True

exit_ExecuteSQL:
Exit Function

err_ExecuteSQL:
MsgBox Err.Description, vbInformation, "cls_Data::ExecuteSQL()"
Resume exit_ExecuteSQL
End Function

Public Function ConnectLocal() As ADODB.Connection

Dim cnn As New ADODB.Connection

Set cnn = CurrentProject.Connection

Set ConnectLocal = cnn

End Function

 

[boblarson]

On Error GoTo err_ExecuteSQL

DoCmd.SetWarnings False

ConnectLocal.Execute strSQL

DoCmd.SetWarnings True

exit_ExecuteSQL:
Exit Function

err_ExecuteSQL:
MsgBox Err.Description, vbInformation, "cls_Data::ExecuteSQL()"
Resume exit_ExecuteSQL
End Function

KennyDies - Did you realize that you are at risk for turning off your warnings completely and not knowing it?

You should have DoCmd.SetWarnings True as the first line in your error handler as well. If an error occurs before you hit your DoCmd.SetWarnings True, you will not get warnings at all from that point on.

Just thought you'd like to know.