PHP and MS Access: Number of Records returned by SELECT query

Question

I am running following PHP code to interact with a MS Access database.

$odbc_con = new COM("ADODB.Connection");
$constr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . $db_path . ";";
$odbc_con -> open($constr);

$rs_select = $odbc_con -> execute ("SELECT * FROM Main");

Using ($rs_select -> RecordCount) gives -1 though the query is returning non-zero records.

(a) What can be the reason? (b) Is there any way out?

I have also tried using count($rs_select -> GetRows()). This satisfies the need but looks inefficient as it will involve copying of all the records into an array first.

Answer 1

Doesn't Access have its own COUNT operator? eg:

$rs_select = $odbc_con -> execute ("SELECT COUNT(*) FROM Main");

Answer 2

It is possible that ODBC doesn't know the recordcount yet. In that case it is possible to go to the last record and only then will recordcount reflect the true number of records. This will probably also not be very efficient since it will load all records from the query.

As Oli said, using SELECT COUNT(*) will give you the result. I think that using 2 queries would still be more efficient than using my first method.

Answer 3

Basically, Access is not going to show you the whole record set until it needs to (it's faster that way for much of the user experience anyway) - especially with larger recordsets.

To get an accurate count, you must traverse the entire record set. In VBA I normally do that with a duo of foo.MoveLast and foo.MoveFirst - I don't know what the php equivalents are. It's expensive, but since it sounds like you are going to be processing the whole record set anyway, I guess it is OK.

(a side note, this same traversal is also necessary if you are manipulating bookmarks in VBA, as you can get some wild results if you clone a recordset and don't traverse it before you copy the bookmark back to the form's recordset)

Answer 4

ADODB has its own rules for what recordcount is returned depending on the type of recordset you've defined. See:

MS Knowledge Base article 194973

W3C Schools article

In the example above, the PHP COM() object is used to instantiate ADODB, a COM interface for generic database access. According to the PHP documentation, the object reference produced is overloaded, so you can just use the same properties/methods that the native ADODB object would have. This means that you need to use the ADODB methods to set the recordset type to one that will give an accurate recordcount (if you must have it). The alternative, as others have mentioned, is to use a second query to get the COUNT() of the records returned by the SELECT statement. This is easier, but may be inappropriate in the particular environment.

I'm not an ADO guru, so can't provide you with the exact commands for setting your recordset type, but from the articles cited above, it is clear that you need a static or keyset cursor. It appears to me that the proper method of setting the CursorType is to use a parameter in the command that opens the recordset. This W3C Schools article on the CursorType property gives the appropriate arguments for that command.

Hopefully, this information will help the original poster accomplish his task, one way or the other.

--
David W. Fenton
David Fenton Associates

Answer 5

If you're using a dynamic cursor type of connection, then it could actually change. Someone may delete a record from that database while you're browsing through pages of records. To avoid, use a static sort of snapshot cursor. I have this bookmarked which will explain it well. This always got me and the bookmark always reminded me why.

http://support.microsoft.com/kb/194973

Answer 6

are bhai koi to do answer kisi ko nahi aata kya