empty a database!!

[Niamh02]

I connect to my access 2000 database via an ADO control in my VB 6 project. I want to provide a function to check if the database/recordset (there's only 1 table)is empty and delete all records in it if it isnt.
Any help would be great!
Thanx

 

[irlbinky]

I have done something similar before but don't have the code with me. This should work (if you have a primary key or other required field that cannot be empty it would be better)

dim cnDatabase as ADODB.Connection
set cnDatabase = New ADODB.connection
cnDatabase.connectionString = "provider = Microsoft.jet.OLEDB.3.51;Data Source = path to database"

dim temp as integer
cnDatabase.Open
sqlAction = "SELECT count(*) as myCount FROM tablename"
temp = cnDatabase.execute sqlAction
if temp > 0
sqlAction = &quot;DELETE FROM tablename where required_field <> ''&quot;
end if
cnDatabase.close
set cnDatabase = Nothing

I hope it works out OK
Cathal

 

[CCLINT]

uh, just use .Execute &quot;DELETE FROM tablename&quot; *******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[lovell811]

Yeah CLLINT I'm pretty sure that's how you do it. Unless you want to use the 15 lines of code used above! ----------------
Joe

 

[chogben]

Or just copy a blank version over the top of it. It's a fairly quick and easy method.

 

[Schroeder]

Clearing the table by copying a blank version of the database over top of the original has the added benefit of eliminating the need to compact.