Have simple question re: Deletion and RecordCount Thanks! 
Author Message
 Have simple question re: Deletion and RecordCount Thanks!

Hi!

I have a simple question relating to database file management.

In my application, sometimes I need to delete one or all records from
a database file, and get an updated RecordCount value.  I probably
didn't declare it right or something, because I can delete every
record and my RecordCount doesn't change!  I wanted to declare the
structure as a table, but maybe it isn't?

Here is the declaration:

  Set ws = DBEngine.Workspaces(0)
  Set db = ws.OpenDatabase("c:\vb\trial.mdb")
  Set tblKeyTemp = db.OpenRecordset("temp_key", dbOpenTable)

I have tried to delete all the records in tblKeyTemp with the
following code:

   'empty tblkeytemp to remove prior trial's results
   If Not tblKeyTemp.EOF Then tblKeyTemp.MoveFirst
   Do While Not tblKeyTemp.EOF
       tblKeyTemp.Delete
       tblKeyTemp.MoveFirst
   Loop

But tblKeyTemp.RecordCount never changes!  In fact, the data is never
deleted.  If tblKeyTemp had 12 records before, it still has 12 records
in it now.

What am I doing wrong?

Much thanks, Nancy



Mon, 22 Feb 1999 03:00:00 GMT  
 Have simple question re: Deletion and RecordCount Thanks!

RecordCount returns the number of records which have been accessed. For an
accurate count, you need to
Recordset.movefirst
Recordset.movelast

Recodset.recordcount will now return an accurate value.



Quote:
> Hi!

> I have a simple question relating to database file management.

> In my application, sometimes I need to delete one or all records from
> a database file, and get an updated RecordCount value.  I probably
> didn't declare it right or something, because I can delete every
> record and my RecordCount doesn't change!  I wanted to declare the
> structure as a table, but maybe it isn't?



Mon, 22 Feb 1999 03:00:00 GMT  
 Have simple question re: Deletion and RecordCount Thanks!

Re: Have simple question re: Deletion and RecordCount  Thanks!

Hi,

I use VB 3.0 but I use also the Jet Engine of VB 4.x.

The recordcount doesn't run fine : you only can use it to know if
ther's record or not, but you can not know the number of records.

Good luck ...



Mon, 22 Feb 1999 03:00:00 GMT  
 Have simple question re: Deletion and RecordCount Thanks!



Hi,

Your DO loop is testing for EOF yet after each delete, you movefirst,
which (I think) would give a BOF.
Try changing the movefirst statement within the loop to movenext.
----
It could also be you are getting an error that you not trapping. Try
putting an on error statement before the loop and setting a breakpoint
in the error section.

h.

Quote:
>Hi!

>I have a simple question relating to database file management.

>In my application, sometimes I need to delete one or all records from
>a database file, and get an updated RecordCount value.  I probably
>didn't declare it right or something, because I can delete every
>record and my RecordCount doesn't change!  I wanted to declare the
>structure as a table, but maybe it isn't?

>Here is the declaration:

>  Set ws = DBEngine.Workspaces(0)
>  Set db = ws.OpenDatabase("c:\vb\trial.mdb")
>  Set tblKeyTemp = db.OpenRecordset("temp_key", dbOpenTable)

>I have tried to delete all the records in tblKeyTemp with the
>following code:

>   'empty tblkeytemp to remove prior trial's results
>   If Not tblKeyTemp.EOF Then tblKeyTemp.MoveFirst
>   Do While Not tblKeyTemp.EOF
>       tblKeyTemp.Delete
>       tblKeyTemp.MoveFirst
>   Loop

>But tblKeyTemp.RecordCount never changes!  In fact, the data is never
>deleted.  If tblKeyTemp had 12 records before, it still has 12 records
>in it now.

>What am I doing wrong?

>Much thanks, Nancy



Mon, 22 Feb 1999 03:00:00 GMT  
 Have simple question re: Deletion and RecordCount Thanks!

To get the recordcount of a recordset  you must perform a MOVELAST on the
recordset.  If you want to do a mass delete of all records in a table,
perform a db.execute with a SQL statement instead of looping through a
recordset.  

        db.execute "delete from tablename",dbfailonerror

The above code will delete all data from a table.  Replace "tablename" with
the actual table name.  If you want to delete all records from a table, why
are you worried about how may records are in the table?  If you use a
recordset the following code will delete the data:

Example

        if not rs.eof  and not rs.bof then rs.movefirst

        while not rs.eof
                rs.delete
                rs.movenext
        wend

When you delete a record using JET on an Access table the record count does
not actually change until the recordset is refreshed with a REQUERY.  If
you have Access on your system, open Access and open the table you are
deleting the records from.  Next, start your VB app , leaving Access open,
and delete the records.  Go back to Access and look at the table.  You
should see "#Deleted" in the table columns of the records you deleted using
VB.  If you close and reopen the table the deleted records will be gone.

I hope this information helps you...



Quote:
> Hi!

> I have a simple question relating to database file management.

> In my application, sometimes I need to delete one or all records from
> a database file, and get an updated RecordCount value.  I probably
> didn't declare it right or something, because I can delete every
> record and my RecordCount doesn't change!  I wanted to declare the
> structure as a table, but maybe it isn't?

> Here is the declaration:

>   Set ws = DBEngine.Workspaces(0)
>   Set db = ws.OpenDatabase("c:\vb\trial.mdb")
>   Set tblKeyTemp = db.OpenRecordset("temp_key", dbOpenTable)

> I have tried to delete all the records in tblKeyTemp with the
> following code:

>    'empty tblkeytemp to remove prior trial's results
>    If Not tblKeyTemp.EOF Then tblKeyTemp.MoveFirst
>    Do While Not tblKeyTemp.EOF
>        tblKeyTemp.Delete
>        tblKeyTemp.MoveFirst
>    Loop

> But tblKeyTemp.RecordCount never changes!  In fact, the data is never
> deleted.  If tblKeyTemp had 12 records before, it still has 12 records
> in it now.

> What am I doing wrong?

> Much thanks, Nancy



Wed, 24 Feb 1999 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Passing a variable - simple question - thank you.

2. Anyone having problems with .recordcount?

3. Simple Question: Assigning Icon to an App : Simple Question

4. Having trouble with simple COLLECTION

5. Having problems with SIMPLE query in VB 6 and Access XP, Please Help

6. STILL Having trouble with simple COLLECTION

7. One more File Deletion question

8. Deletion from LEFT JOINed tables - very basic question

9. Deletion from LEFT JOINed tables - very basic question

10. Deletion from LEFT JOINed tables - very basic question

11. Newbie Question. File Deletion Script

12. Deletion of a control array item question

 

 
Powered by phpBB® Forum Software