RE: Purge records in visual basic

[yinyin]

Does anyone know how to purge the data in the database from visual basic.

[Chris]

Assume you're using MS Access database and...

• Old Databsse = your current database name.
• New Database = your temp database name.
• TblMyTable= the Table inside your current database.

Screen.MousePointer = vbHourglass
Dim Db As DAO.Database
Set Db = DBEngine.Workspaces(0).OpenDatabase("<Old Database>", False, False)

'Purge the unused record.
Db.Execute "DELETE * FROM TblMytable;"
Db.Close
Set Db = Nothing
DBEngine.CompactDatabase "<Old Database>", "<New Database>", dbLangGeneral

'Delay 3sec for the computer to catch up.
Sleep 3000

'Delete the current database
If Dir("<Old database>") <> "" Then Kill "<Old Database>"
'Rename the temp databse to the original database name.
Name "<New Databse>" As "<Old Databse>"
Screen.MousePointer = vbDefault

[Edited by Chris on 06-05-2000 at 02:43 PM]

[yinyin]

Originally posted by Chris
Assume you're using MS Access database and...

• Old Databsse = your current database name.
• New Database = your temp database name.
• TblMyTable= the Table inside your current database.

Screen.MousePointer = vbHourglass
Dim Db As DAO.Database
Set Db = DBEngine.Workspaces(0).OpenDatabase("<Old Database>", False, False)

'Purge the unused record.
Db.Execute "DELETE * FROM TblMytable;"
Db.Close
Set Db = Nothing
DBEngine.CompactDatabase "<Old Database>", "<New Database>", dbLangGeneral

'Delay 3sec for the computer to catch up.
Sleep 3000

'Delete the current database
If Dir("<Old database>") <> "" Then Kill "<Old Database>"
'Rename the temp databse to the original database name.
Name "<New Databse>" As "<Old Databse>"
Screen.MousePointer = vbDefault

[Edited by Chris on 06-05-2000 at 02:43 PM]

Chris,

It doesnt seem to work. An error occur in the line "Kill "<Old Database>"". It say permission denied. So any idea about that.

[Chris]

Does you close all the DAO object or any others application is open the "<Ols Database>"?