Truncate table using VB6

[rishif2]

Hello experts
i am using following code in order to truncate a ms access table

Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
Dim query As String
query = "truncate table tbl"
rec.Open query, con, 2, 3
con.close

but its producing an error

So any solution to do so . . .

[BitBlt]

"TRUNCATE" is not a valid SQL statement for MS Access. It will work fine with MS SQL Server, though. For MSAccess you have to delete the rows with a DELETE statement.

As a separate issue, you may also want to consider not using a recordset object for deleting all the rows in your table. If your query or SQL statement will not return rows (such as for "delete" or "update" statements) you should use the con.Execute method. It'll save you having to instantiate an unneeded object.

Last, as a VB "best practice", after you close your connection object, set it to the reserved object "Nothing" to recover memory, handles and allow system garbage collection.

So, putting that all together, you might wind up with something like this:

Dim con As New ADODB.Connection
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path + "\db.mdb"
Dim query As String
query = "delete from tbl;"
con.Execute query
con.Close
Set con = Nothing

Hope these hints are useful! Happy Coding!

[rishif2]

provided tips are really nice but still a question exist and that is
i have created table in access and i used auto number in the first field and i inserted 10 records and then i deleted entire records of the table

now once again i inserted a record and the id(autonumber) field starts with 11

and i expected that it would be 1 rather than 11 .so what should be done to do so(start with 1 after deleting all records)

[BitBlt]

Ah, that's a wholly different question. The short answer is, you can't.

There's a MS Knowledgebase article describing the hoops you have to jump through to accomplish what you want here, but you may want to ask yourself if resetting is really necessary in the context of your application.

See, the theory behind AutoNumber is that it is a non-significant number that is only used for identification...therefore the value in it is immaterial. In practice, many developers wind up showing it to their end user, so gaps in sequence or high numbers might be alarming. However, unless you are using a more robust DBMS, resetting the value to start at 1 requires dropping and recreating the table, deleting and rebuilding the AutoNumber column, or some other hokey method.

Sorry I don't have better news for you. Good luck anyway!

[rishif2]

resetting the value to start at 1 requires dropping and recreating the table, deleting and rebuilding the AutoNumber column

i think after knowing how to perform alter operation(through VB) on a Access table , i can drop and recreate the field whenever it is required ,

so how to write alter query to do so . . .

[BitBlt]

Actually, as long as there's no data in the table in question, compacting the database will reset all the AutoNumber seed values to 1. So, if you delete all the data, you can use MSAccess to compact it and you're sorted.

If you want to do it programmatically, you have to use the DAO object DBEngine, and execute the DBEngine.CompactDatabase method. The drawback with doing it that way is that you have to specify a new database name as the destination to compact to, then use FileSystemObjects to delete the old database and rename the new compacted database. Big pain, but it can be done.

Are you REALLY sure it's that important? ;-)

[rishif2]

After watching a lot articles on this topic and getting your suggestions also , finally i've decided to use id field as non autonumber and when a record is going to be inserted then also insert incremented(to the last inserted) value to the id field, so it will look like an autonumber field

finally i would like to thank you for spending your important time to the discussion and gave important hints regarding database operations in Access through VB

[AndreRet]

I agree with your assesment. It is faster and less error prone to just drop the auto increment and create your own by counting records from your table.

Please mark as solved, thank you.