Deleting Records from another Table

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

I want to delete all of the records from a table in the same database in
Access 2007. I want to preserve the structure of the table though.

How do I do that?
 
And perhaps if you don't want to create a query object:

Dim db As DAO.Database
Dim strSQL as String

strSQL = "DELETE tablename.* FROM tablename;"
db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing

John
 
JohnC said:
And perhaps if you don't want to create a query object:

Dim db As DAO.Database
Dim strSQL as String

strSQL = "DELETE tablename.* FROM tablename;"
db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing
Click to expand...


You forgot to set db to a reference to the current database:

Set db = CurrentDb

But you don't want to try to close the current database (even if you try,
your attempt will be ignored), so remove this line:
db.Close
Click to expand...

For that matter, the following line of code can replace all of the above:

CurrentDb.Execute "DELETE* FROM tablename", dbFailOnError
 
Dirk Goldgar said:
For that matter, the following line of code can replace all of the above:

CurrentDb.Execute "DELETE* FROM tablename", dbFailOnError
Click to expand...

Or, to save a few keystrokes <g>

CurrentDb.Execute "DELETE FROM tablename", dbFailOnError
 
Back
Top