Database count Incorrect :(

[BackupUser]

Restored from previous forum. Originally posted by Fangbeast.

I use the following bit of code to get the count of the total number of records in an Access Database using SQL. I have checked numerous sites for the correct syntax and they all agree.

However, this code returns a count of 4010 which is 373 records less than the actual number. I have check the database to be sure I am not going mad ???

Anyone have an idea what the heck is going on?

Code: Select all

  qry.s = "Select Count(*) from mydata;"
  
  If DatabaseQuery(qry.s)
    While NextDatabaseRow()
      totalrecords = GetDatabaseLong(0)
    Wend
  Else
    stat(0, "SQL Error: " + qry.s)
    Return
  EndIf
  
debug totalrecords

We are Dyslexic of Borg, prepare to have your ass laminated!

[BackupUser]

Restored from previous forum. Originally posted by Paul.

I was always taught to assign the data I needed in a count to a table...

Code: Select all

If DatabaseQuery("Select Count(*) as totalrecs from mydata;")
  totalrecords=0
  While NextDatabaseRow()
    totalrecords=GetDatabaseLong(0)
  Wend
EndIf

Anyways, it's always worked fine and been accurate for me.
Of course another question arises... how do you know you are off by 373 records?



----------
Visit the PB Resources Site at http://www.reelmediaproductions.com/pb

[BackupUser]

Restored from previous forum. Originally posted by Fangbeast.

Thanks Paul, it appears from what we spoke about in private that my code is okay but the database itself is misnumbered somehow.

I am going to have to think of a different way to find out the last rceord number assigned to the database before I add a new one and have a visible record number. More fun


We are Dyslexic of Borg, prepare to have your ass laminated!

[BackupUser]

Restored from previous forum. Originally posted by woki.

Deleted record numbers are not reused.

Use "select max(fieldname of record number) as last_rec from table" to get the latest record number.

[BackupUser]

Restored from previous forum. Originally posted by Fangbeast.

woki thanks muchly for that. I have been going over SQL docs till my brain hurt and couldn't find it. Just finished fixing and testing the change and it works so well!!! No need for me to compress the database just yet or tear out my hair :)

We are Dyslexic of Borg, prepare to have your ass laminated!