MS Access 2000 pagination

Kondie

Hie,

I am using an MS Access 2000 database with PHP, via ODBC. I would like to create the navigation links "last, prev, next, last" from a retrieved data recordset.

I have managed to do this with MySQL using the "LIMIT" clause in the SQL statement, but Access does not seem to support that. It seems to me that one can oly retrieve the top n or bottom n records, but not something in the middle.

Does anybody have any ideas on how I can implement that?

Thanks,

Kondie

Danicek

I do not know about such function in Access.
It is frustrating because you will need to use count, identity collumn, inserted id or something like that.
What is more frustrating is that non of these will work in all conditions (for example gaps in identity collumns can make your iteration not really accurate).

Kondie

Thanks Danicek.

At least I have an idea now. I think I will give it a try.

jantheman

Hi,

This is my first post and have found this old thread with the exact problem I am having - does anybody out there have any more updated clues as to how pagination using MS Access data with an ODBC connection can work? My data has (and will always have) gaps in the data ID sequence which rules out the 'between $min and $max' method of retrieval.

Please - any ideas???? Thanks.

thorpe

This whole process is actually quite easy if you use COM() and the native ADODB.recordset object available in windows. I used to program asp and there are many methods available in the ADODB.recordset object to help with record paging.

For some examples, you might be best to search an asp site. Asp developeres tend to use access for alot of stuff.

Here is a link to my old stomping ground. If you search the boards there you will find some examples, that, coupled with the php manuals COM reference should help you out.

If you get stuck let us know.

Roger_Ramjet

Access does not support the LIMIT clause in it's SQL syntax, you have to use TOP instead.
example 'SELECT TOP 1000 * FROM table1' would return the first 1000 rows.
So you can't have an offset either. Basically it is only to limit the size of a resultset and speed processing up. Not a lot of use for pagination of results. And this holds true for ADO recordsets as well.

The secret is to track the identity column and ammend your query on the fly.

$_SESSION['base'] = "SELECT TOP 20 * FROM table ";
$_SESSION['where'] = " WHERE col1='" . $_POST['search'];
$_SESSION['last_id'] = "";
$_SESSION['order'] = " ORDER BY id";

$query = $_SESSION['base_query'] . $_SESSION['where'] . $_SESSION['last_id'] . $_SESSION['order'];
// run query

while ($row // etc {

// display etc
// then track the id
$_SESSION['last_id'] = " AND id>" . $row['id'];
}

This will page through the results 20 at a time: adjust for your particular case.

thorpe

The ADODB.recordset object has methods such as pagesize(), cachesize(), cursorlocation(), absolutepage(), and pagecount() that are specifically designed for record paging.

Roger_Ramjet

Yes, but the underlying query must return all the records to begin with.
Now that is fine on a client/server app where the adodb recordset is maintained between calls and you can use a server-side cursor. No use whatsoever in php where the recordset will be destroyed when the script ends - you'll have to run the full query for every page.

If you are going to track the page, then you can just as easily track the id and query for a reduced recordset based on that.

This is not to say that ADODB is not a good way to go with your db access - far better than ODBC anyway - just that it's methods are not that usefull in the php world.