Delete problem

blinx
10-11-2004, 09:01 PM
Hi,
I'm using this code to delete records from database (dbase IV)

Public Sub Deletemint()
Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command
Dim objRS As ADODB.Recordset
Dim rsmint As ADODB.Recordset

Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command


objCon.connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=dBASE IV;" & _
"Data Source=C:\prog"
objCon.Open objCon.connectionString
Set objRS = New ADODB.Recordset

objRS.Open "delete From mintran", objCon, adOpenStatic, adLockReadOnly, adCmdText

End Sub


The problem is the size of the deleted database remains the same...
for example :
before execute this table has 50 records with 200kb size...
after execution the table has no records but still has 200kb size
how this can happen??

how to solve this?

Thx

webbone
10-11-2004, 09:25 PM
Access is notorious for things like this. What you need to do is compact the database to reduce the size.

Note that there will be a minimum size for your database even with no data due to the structures Access requires to describe the database.

EDIT: It is possible to do the compacting through code. I can't remember off the top of my head how to do this with ADO but I'm sure one of the other Experts will remember or I can look in some code later if you need to do that.

NEOLLE
10-11-2004, 11:06 PM
I havnt tried to compact a database through code. MS Access provides a tool for this.

In the MS Access IDE goto Tools-->Database Utilities -->Compact and Repair Database... :)

tom2knox
10-11-2004, 11:46 PM
i think its just the same as foxpro 2.6, deleting a record is not physically deleting it, it is just marked as deleted. you need to know what exact command to use when cleaning your db, in foxpro2.6 i think if im not mistaken it's pack or pow...
hope it helps you... ;)

blinx
10-12-2004, 08:01 PM
i think its just the same as foxpro 2.6, deleting a record is not physically deleting it, it is just marked as deleted. you need to know what exact command to use when cleaning your db, in foxpro2.6 i think if im not mistaken it's pack or pow...
hope it helps you... ;)


so there is no possibility of deleting physical file in DBASE using VB?

blinx
10-13-2004, 02:55 AM
How???????????????????????????

Norenca
10-13-2004, 03:05 AM
Why don't you just drop the table and create it again?



Public Sub Deletemint()
Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command
Dim objRS As ADODB.Recordset
Dim rsmint As ADODB.Recordset

Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command

objCon.connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=dBASE IV;" & _
"Data Source=C:\prog"
objCon.Open objCon.connectionString
Set objRS = New ADODB.Recordset

objRS.Open "drop table mintran", objCon, adOpenStatic, adLockReadOnly, adCmdText
objRS.Close

objRS.Open "Create table mintran(field int, field2, varchar)", objCon, adOpenStatic, adLockReadOnly, adCmdText
objRS.Close

End Sub



I think it takes up even less time to process that command and you'll have a completely fresh table.

Btw: the parameters for opening the recordset ("adOpenStatic, adLockReadOnly, adCmdText"
) are wrong for my solution, you'll have to change them, but I haven't used adodb in a while, so I don't know what they have to be.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum