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

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.