Deleting all records through code

[anonymous]

[Originally posted by Mohammad Farooq]

Dear friends,
I have number of tables in my database. I want to delete all records from all tables through code by clicking on a command button.
Regards
Farooq

[anonymous]

[Originally posted by Mike Lyons]

Some more detail would help -- what kind of database is this, and how are you accessing it (ADO, DAO, RDO, etc.)?

In most database environments, you can clear a table using:

* * DELETE * FROM [TableName]

The [TableName] would be the name of your table.

The syntax might vary a little depending on the type of database but essentially it's the same.* The main things to consider are how you will obtain a list of the tables -- you might want to hold a table of "clearable" tables, and a column to specify the sort order in case of conflicts with indexing and relationships where you have to clear one table before another.

Then loop through a recordset of just the clearable tables and for each one run a SQL statement like I showed above.

Mike

[anonymous]

[Originally posted by Mohammad Farooq]

Dear Mike,
Thanks for ur reply,
Actually i want to know that how can i obtain the list of tables from my database using ADO Reference (Not ADO Control).
And then Delete all records from all tables using ADO.
Regards
Farooq

[anonymous]

[Originally posted by Mike Lyons]

Well, as I suggested, you might want to maintain a table that contains the names of the tables in your database that you want to be clearable.* There may be some tables in a database, such as system tables, that you should NOT delete data from.

By keeping a list of tables safe for clearing you avoid having to worry about damaging the wrong table, especially if you add tables to your database that contain application settings, lookup list values and so on that you might not wish to clear.

Mike

[anonymous]

[Originally posted by m.posseth@work]

this is possible with ADOX this is an extension to ADO with some powerfull features like

creating tables , columns , indexes etc etc

it also has the ability to show all database objects through a collection ( so you can see all tables , including the system tables ) and to alter , remove them

ADOX also has a compact and repair functionality , so after deletion you might want to compact the database to free up some space


in the past i used ADOX a lot untill i discovered that a SQL statement is much faster* however with SQL statement you must know your objects* ( so you should go for Mikes aproach)

However with ADOX you can read the table names out and decide depending for instance on there names what to do with them

*