1,105,592 Community Members

Truncate table using VB6

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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 . . .

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

"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!

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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)

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
1
 

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!

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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 . . .

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

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? ;-)

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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.

Question Answered as of 1 Year Ago by BitBlt and AndreRet
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article