The backend database is Access. After using it for a year, the customer said that opening the interface was very slow. After checking the database, I found that there were more than 50,000 records in the data table. I tried copying the records to 10 Thousands of records, opening the interface is very slow, and turning pages has the same problem. The method I adopted is to load 20 records per page, write them in a loop and display them in the table, and then set four page turning keys to view the data, but the problem is Although there are only twenty records loaded into the page each time, all records must be loaded at once every time the record set is opened, so it is slow.
Solution one:
1.Set an auto-increment field. And the field is INDEX.
2. Since it is ACCESS, it can only be used for front-end paging. The purpose of self-increasing fields is to realize the paging function.
1> Record the last self-incremented value of the user's previous page, such as M.
2> Next page, take the starting value of the page.M+1, the ending value: M+1+1.5*PAGESIZE (Note: Since the database will have addition and deletion operations, there should be a coefficient for the page size. You can Customize a large coefficient of 1 according to the situation.
3> The front-end loop takes the first PAGESIZE bar of RS, writes it into a new RS, and returns.
Note: The new RS is a connectionless RS.
Solution two:
One hundred thousand records is not the limit of Access database. What's more, your method is not paging in the true sense (the PageSize and AbsolutePage properties should be used).
VBScript code
Copy the code code as follows:
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.PageSize = 20
rs.Open Select * From guest, iConc, adOpenKeyset, adLockOptimisticlng
Pages = rs.PageCount
lngCurrentPage = 1
The record set opened at this time has only 20 records.
When turning pages:
Copy the code code as follows:
If lngCurrentPage < lngPages Then
lngCurrentPage = lngCurrentPage + 1
rs.AbsolutePage = lngCurrentPage
End If