delete all rows in a table

  • Thread starter Thread starter Rivers
  • Start date Start date
R

Rivers

can someone please advise me on the way i can delete all rows in my table
called "00 1-1 booked" i tried the delete method in VB but to no avail is it
possible to call a delete method from vb. i still want the structure to
survive but the recdords to be deleted.

thanks
 
can someone please advise me on the way i can delete all rows in my table
called "00 1-1 booked" i tried the delete method in VB but to no avail is it
possible to call a delete method from vb. i still want the structure to
survive but the recdords to be deleted.

thanks
Click to expand...

Is it a one time deal?
Just open the table. Select all the records by (clicking in the little
square in the left hand corner where the gray field names meets the
record selectors).
Press the Delete key.

You can delete all records using a delete query,
or
you can use VBA code:

CurrentDb.Execute 'Delete * from TableName;", dbFailOnError

All the date will be deleted, but the fields and properties will be
untouched.
 
Rivers,
referential integrity can prevent records from being deleted.
If this is your situation, delete records from any child tables before you
try to delete records from any parent table.

Jeanette Cunningham
 
I tried the command already but this did not work is there another way?

Jeanette Cunningham said:
Rivers,
referential integrity can prevent records from being deleted.
If this is your situation, delete records from any child tables before you
try to delete records from any parent table.

Jeanette Cunningham
Click to expand...
 
TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from TableName;", dbFailOnError

this command doesnt work what am i doing wrong please help

thanks

Rivers said:
I tried the command already but this did not work is there another way?
Click to expand...
 
Rivers said:
TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from TableName;", dbFailOnError

this command doesnt work what am i doing wrong please help
Click to expand...

The variable cannot be inside the quotes or else the query will actually
looking for a table named "TableName".

TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
 
Rick said:
The variable cannot be inside the quotes or else the query will
actually looking for a table named "TableName".

TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
Click to expand...

Sorry, I completely missed the ramifications of this (really horrible) table
name on the process. It will of course need to have square brackets around
it because of the spaces and the fact that the name begins with a digit.

TableName = "[00 1-1 booked]"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
 
TableName = "[00 1-1 booked]"
CurrentDb.Execute "Delete * From" & TableName, dbFailOnError

tried this but it asks for a parameter as it is missing
Click to expand...
if that's pasted from your module, you are missing a space between
from and "

Rick Brandt said:
Rick said:
Rivers wrote:
TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from TableName;", dbFailOnError

this command doesnt work what am i doing wrong please help

The variable cannot be inside the quotes or else the query will
actually looking for a table named "TableName".

TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
Click to expand...

Sorry, I completely missed the ramifications of this (really
horrible) table name on the process. It will of course need to
have square brackets around it because of the spaces and the fact
that the name begins with a digit.

TableName = "[00 1-1 booked]"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
Click to expand...
Click to expand...
 
TableName = "[00 1-1 booked]"
CurrentDb.Execute "Delete * From" & TableName, dbFailOnError

tried this but it asks for a parameter as it is missing

Rick Brandt said:
Rick said:
The variable cannot be inside the quotes or else the query will
actually looking for a table named "TableName".

TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
Click to expand...

Sorry, I completely missed the ramifications of this (really horrible) table
name on the process. It will of course need to have square brackets around
it because of the spaces and the fact that the name begins with a digit.

TableName = "[00 1-1 booked]"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
Click to expand...
 
TableName = "[00 1-1 booked]"
CurrentDb.Execute "Delete * From" & TableName, dbFailOnError

tried this but it asks for a parameter as it is missing

Rick Brandt said:
Rick said:
Rivers wrote:
TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from TableName;", dbFailOnError

this command doesnt work what am i doing wrong please help

The variable cannot be inside the quotes or else the query will
actually looking for a table named "TableName".

TableName = "00 1-1 booked"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
Click to expand...

Sorry, I completely missed the ramifications of this (really horrible) table
name on the process. It will of course need to have square brackets around
it because of the spaces and the fact that the name begins with a digit.

TableName = "[00 1-1 booked]"
CurrentDb.Execute " Delete * from " & TableName, dbFailOnError
Click to expand...
Click to expand...

When replying to a question many times we use generic names, such as
YourTable, MyTable, FormName, ControlName, etc.
You're supposed to replace those generic names with your actual object
names

Why are you placing the table name in a variable?
Substituting your table name for the generic YourTable, this works for
me:

CurrentDb.Execute "Delete * from [00 1-1 booked];", dbFailOnError
 
Back
Top