How to do MS Access database paging + search?
I'm querying Access from C# as well (with paging and searching), and I'm using the following code to build all my queries:
var sb = new StringBuilder();
sb.Append("select {0} from {1}");
sb.Append(" where {3} in (");
sb.Append("select top {4} sub.{3}");
sb.Append(" from (");
sb.Append(" select top {5} tab.{3}");
sb.Append(" from {1} tab");
sb.Append(" where {2}");
sb.Append(" order by tab.{3}");
sb.Append(" ) sub");
sb.Append(" order by sub.{3} desc");
sb.Append(")");
sb.Append("order by {3}");
sql = string.Format(sb.ToString(), this.ColumnsToSelect, this.TableName,
this.WhereClause, this.OrderBy, this.PageSize, this.PageNum * this.PageSize);
Note that in order for this to work, all parameters must be supplied
(if you don't actually want to filter anything, just put 1=1
into the WHERE
clause)
How do I implement pagination in SQL for MS Access?
If you wish to apply paging in MS Acces use this
SELECT *
FROM (
SELECT Top 5 sub.ClientCode
FROM (
SELECT TOP 15 tblClient.ClientCode
FROM tblClient
ORDER BY tblClient.ClientCode
) sub
ORDER BY sub.ClientCode DESC
) subOrdered
ORDER BY subOrdered.ClientCode
Where 15 is the StartPos + PageSize, and 5 is the PageSize.
EDIT to comment:
The error you are receiving, is because you are trying to reference a column name assign in the same level of the query, namely rownumber. If you were to change your query to:
SELECT *
FROM (
SELECT ClientCode,
(SELECT COUNT(c2.ClientCode)
FROM tblClient AS c2
WHERE c2.ClientCode <= c1.ClientCode) AS rownumber
FROM tblClient AS c1
)
WHERE rownumber BETWEEN 0 AND 15
It should not give you an error, but i dont think that this is the paging result you want.
Microsoft Access and paging large datasets
If you run a ranking query, you will get a column containing ascending numbers in your output. You can then run a query against this column using a BETWEEN...AND
clause to perform your paging.
So, for example, if your pages each contain 10 records and you want the third page, you would do:
SELECT * FROM MyRankingQuery WHERE MyAscendingField BETWEEN 30 and 39
How to Rank Records Within a Query
Microsoft support KB208946
paging and ordering a MS Access query
try also this approach:
SELECT * FROM
(
SELECT TOP 20 *
FROM
(
SELECT TOP 40
s.name as SHolderCategory,
c1.id,
c1.fmember,
c1.link,
m.name as category,
c1.name,
c1.address1,
c1.address2,
c1.city,
c1.state,
c1.zip
FROM
orgs AS c1
inner join membershipcls m on m.Id = c1.mClassID
inner join SHolderscategories s on s.Id = c1.SHolderCategoryID
WHERE c1.active = 1
ORDER BY c1.name
) o
ORDER BY o.name DESC
) f ORDER BY f.name
How to implement pagewise loading in MSAccess
It's possible in Access SQL, but not as straightforward as in other database products.
(for example MySQL, where it would be just LIMIT 10,10
)
Check out my answer here:
How to do MS Access database paging + search?
(the code to build the SQL Statement is in C#, but of course you can do it in any other language as well. If you don't know C# and need help understanding my answer, just leave a comment here)
Related Topics
How to Convert an Int to a Zero Padded String in T-Sql
Using Table Variable with Sp_Executesql
How to Perform a Select Query in a Do Block
Rails Scope to Check If Association Does Not Exist
SQL Server as Statement Aliased Column Within Where Statement
Is There a Performance Difference Between Between and in with MySQL or in SQL in General
Why Do I Need to Explicitly Specify All Columns in a SQL "Group By" Clause - Why Not "Group by *"
Group by Values That Are in Sequence
Recursive Stored Functions in MySQL
Changing SQL Server Database Sorting
How to Get Max(Date) from Given Set of Data Grouped by Some Fields Using Pyspark
Possible to Restore a Backup of SQL Server 2014 on SQL Server 2012
Postgresql With-Delete "Relation Does Not Exists"