Delete all data from a database.

[PogoWolf]

Hey all,
I wonder if there is an easy way (in VB.NET) to delete ALL data in an access database, but leave the data structure itself.. with out the need to know the tables, and write seperate specific delete querys.


The PogoWolf

http://pogowolf.GamersCommune.com

 

[stevexff]

Think you'd probably need to get a list of tables, then iterate over them doing DELETE FROM table; on each. In SQL server, you'd use the system tables or one of the system stored procedures to list out the tables. Is there an equivalent query you can run in Access? Also, you might have problems with foreign keys and the order of deletion, if Access supports this.

 

[PogoWolf]

Well, I found the code to do this in SQL:

Declare @sql NVarChar(4000)
Select @sql= isnull(@sql,'') +'Delete From ' + Name + ';' From sysObjects Where xType='U'
Exec sp_executesql @sql

But I don't know of a way to translate that into Access.

The problem is that the database is still very new, and most likly to change in the future. I'm trying to have a 'all in one' function that'll delete the data with out
the need to update the function everytime I make an update to the database itself..


Perhaps if there's a way to read in the table names from ADO.NET the delete queries could be created from that..


The PogoWolf

http://pogowolf.GamersCommune.com

 

[Kris11]

May be this link might help you a bit.

-Kris

 

[PogoWolf]

Bingo! that did the trick! Thanks Kris11


The PogoWolf

http://pogowolf.GamersCommune.com

 

[chiph]

I'm not sure Access supports it, but the TRUNCATE SQL command works much faster than deleting each row individually.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[Turpis]

TRUNCATE doesn't work in Access. So the only way to delete all records from a table other than one by one is the SQL command "DELETE FROM [TableName] WHERE 1=1". Thanks to ThatRickGuy for pointing me in the right direction on this one a month or so back.

Charles
Quality Assurance/Developer

 

[PogoWolf]

Hey all, thanks for the replies and everything. I would like to finish up this thred by posting the VB.NET code that I've used to Kill all the data, and the then compact the DB.

Note: You will need to reference the COM Object "Microsoft ADO 2.7 for DDL and Security" (Installed with the .NET framework)

Public sub KillAllData()
'//////////////////////////////////////////////
'// Deletes all data from the database.
'//////////////////////////////////////////////
Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = ConnectionString
DatabaseConnection.Open()

SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing,Nothing, Nothing, "TABLE"})

Dim RowCount As Int32

'// Delete all data from all tables, no matter
'// the schema.
For RowCount = 0 To SchemaTable.Rows.Count - 1
  Database.SQL_DataDump("Delete FROM " & SchemaTable.Rows.Item(RowCount)!TABLE_NAME.ToString)
Next RowCount

'// Close and Clean.
DatabaseConnection.Close()
DatabaseConnection = NOTHING
SchemaTable= NOTHING

'// Compact and copy the DB
Dim jro As JRO.JetEngine
jro = New JRO.JetEngine()
jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\DataBase\DB.mdb", _
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\DataBase\DB2.mdb;Jet OLEDB:Engine Type=5")

'// Clean up
jro=NOTHING

'// Delete the orginal DB
Kill (".\DataBase\DB.mdb")
'// Rename copy.
Rename(".\DataBase\DB2.mdb", ".\DataBase\DB.mdb")

The PogoWolf

http://pogowolf.GamersCommune.com