My current project is using DAO to communicate with the access database. I'm trying to find a solution to paginate using DAO but all I see is pagination for ADO. Is it possible to paginate using DAO? How would I implement that?
Hi morplul
The data access methods DAO and ADO are very similar, so if you have an example in ADO, then changing it to DAO is not much effort.
Pagination - On the screen ? Using A Grid ? (MSFlexGrid is a good one to use) To a Printer ? To Excel ?
Pagination is a technique for dividing a large result set into smaller parts, or pages, and allowing the user to view one page at a time. It is often used in web applications to improve performance and make it easier for users to find and view specific information.
DAO, or Data Access Objects, is a technology used in Microsoft Access to provide an object-oriented interface for working with relational data. It is typically used for interacting with Access databases, but it can also be used with other data sources, such as SQL Server.
In general, pagination can be implemented in any application that retrieves data from a database or other data source. However, the specific implementation will depend on the technology being used and the requirements of the application.
To paginate using DAO, you will need to use a combination of DAO methods and properties to retrieve the data you need in small, manageable chunks. This will typically involve using the DAO.Recordset object to retrieve a set of records from the database, and then using the Move and PageSize properties to control which records are returned and how many are returned at a time.
For example, to retrieve the first page of data containing 10 records, you might use the following code:
Code:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
rs.MoveFirst
rs.PageSize = 10
This code would open a recordset containing all of the records from the MyTable table, move the cursor to the first record, and then set the page size to 10 records. You can then use the Move method to move the cursor to different pages of data, and use the GetRows method to retrieve the data for each page.
For example, to retrieve the second page of data containing 10 records, you could use the following code:
Code:
rs.Move 10
Dim arr() As Variant
arr = rs.GetRows(10)
This code would move the cursor to the eleventh record in the recordset, and then retrieve the next 10 records into an array. You can then use the array to display the data on your application's user interface.
Overall, implementing pagination using DAO requires a good understanding of the DAO object model and how to work with recordsets in Access. If you are not familiar with these concepts, I would recommend reading the documentation and tutorials available on Microsoft's website, as well as consulting with a knowledgeable developer or database administrator.
Pagination - On the screen ? Using A Grid ? (MSFlexGrid is a good one to use) To a Printer ? To Excel ?
For the pagination I used ListView. I set the column headers first before adding the items and subitems.
Originally Posted by George1111
See if you can change ADO to DAO first
I should've tried it first before posting here honestly. I guess I was just being lazy. Anyway, here is what I coded up:
Code:
Public Function Get_Enrollee(Optional page As Integer = 1) As Collection
Dim qdf As QueryDef
Dim query As String
Dim enrollees As New Collection
Dim En As Enrollee
Dim total As Integer
Dim result As New Collection
query = "SELECT * FROM enrollee"
Set qdf = db.CreateQueryDef("", query)
Set rs = qdf.OpenRecordset
first_index = (page - 1) * 23 ' the row rs starts
last_index = first_index ' the row rs ends
total = 0
If rs.RecordCount <> 0 Then
rs.MoveLast
total = rs.RecordCount
rs.MoveFirst
End If
If page > 1 Then
rs.Move first_index
End If
i = 1
While Not rs.EOF And i <= 23 ' 23 is the number of items to be displayed to ListView
Set En = New Enrollee
With En
.id = rs!enrollee_id
.Grade = rs!grade_level
.Lname = rs!last_name
.Fname = rs!first_name
.Mname = rs!middle_name
.Sex = rs!Sex
.Age = rs!Age
.Birthdate = rs!Birthdate
.Birthplace = rs!Birthplace
.Mt = rs!mother_tongue
.Address = rs!Address
.Fathername = rs!father_name
.Fnum = rs!father_no
.MotherName = rs!mother_name
.Mnum = rs!mother_no
.GuardianName = rs!guardian_name
.Gnum = rs!guardian_no
.Submission = rs!date_enrolled
End With
enrollees.Add En
i = i + 1
first_index = first_index + 1
rs.MoveNext
Wend
With result
.Add enrollees, "enrollees"
.Add total, "record_count"
.Add first_index + 1, "first_index"
.Add last_index, "last_index"
End With
Set Get_Enrollee = result
End Function
I feel like this can be improved a lot but it works for now so I'm happy.