http://community.csdn.net/Expert/topic/3400/3400520.xml?temp=2.529544E-02 这个说的比较好
容易看的懂
Option Explicit
Dim conn As ADODB.Connection
Dim lCurrentPage As Long
Private Sub cmdNext_Click()
lCurrentPage = lCurrentPage + 1
Call Loadcontrol(lCurrentPage)
End Sub
Private Sub cmdPrevious_Click()
If lCurrentPage > 1 Then
lCurrentPage = lCurrentPage - 1
Call Loadcontrol(lCurrentPage)
End If
End Sub
Private Sub Form_Load()
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test2000.mdb;"
lCurrentPage = 1
Call Loadcontrol(lCurrentPage)
End Sub
Private Sub Loadcontrol(lPage As Long)
Dim adoPrimaryRS As ADODB.Recordset
Dim lPageCount As Long
Dim nPageSize As Integer
Dim lCount As Long
'每页显示的纪录
nPageSize = 10
Set adoPrimaryRS = New ADODB.Recordset
adoPrimaryRS.Open "select * from numbers", conn, adOpenStatic, adLockOptimistic
adoPrimaryRS.PageSize = nPageSize
'页数
lPageCount = adoPrimaryRS.PageCount
If lCurrentPage > lPageCount Then
lCurrentPage = lPageCount
End If
adoPrimaryRS.AbsolutePage = lCurrentPage
'定义另一个记录集
Dim objrs As New ADODB.Recordset
'添加字段名称
For lCount = 0 To adoPrimaryRS.Fields.Count - 1
objrs.Fields.Append adoPrimaryRS.Fields(lCount).Name, adVarChar, adoPrimaryRS.Fields(lCount).DefinedSize
Next
'打开记录集
objrs.Open
'将指定记录数循环添加到objrs中
For lCount = 1 To nPageSize
objrs.AddNew
objrs!id = adoPrimaryRS!id
objrs!anumber = adoPrimaryRS!anumber
adoPrimaryRS.MoveNext
Next
'绑定
Set DataGrid1.DataSource = objrs
'显示页数
txtPage = lPage & "/" & adoPrimaryRS.PageCount
End Sub
Private Sub Form_Unload(Cancel As Integer)
If Not conn Is Nothing Then
conn.Close
End If
Set conn = Nothing
End Sub
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by " + @tblfldName+".["+@fldName +"] desc"
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by " + @tblfldName+".["+@fldName +"] asc"
end
set @strSQL = "select top " + str(@PageSize) + " "+@SelectfldName+" from "
+ @tblName +" where " + rtrim(@tblfldName)+".["+@fldName +"] " + @strTmp
+ " tblTmp.["+ @fldName + "] from (select top " + str((@PageIndex-1)*@PageSize) + " "
+ rtrim(@tblfldName)+".["+@fldName +"] from " + @tblName + " " + @strOrder + ") as tblTmp) "
+ @strOrder
if @strWhere != '' --如果有where条件子句
set @strSQL = "select top " + str(@PageSize) + " "+@SelectfldName+" from "
+ @tblName + " where " + @tblfldName+".["+@fldName +"] " + @strTmp
+ " tblTmp.["+ @fldName + "] from (select top " + str((@PageIndex-1)*@PageSize) + " "
+ @tblfldName+".["+@fldName +"] from " + @tblName + " where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
if @PageIndex = 1 --如果是第一页
begin
set @strTmp = ""
if @strWhere != ''
set @strTmp = " where " + @strWhere
set @strSQL = "select top " + str(@PageSize) +" "+@SelectfldName+ " from "
+ @tblName + @strTmp + " " + @strOrder
end
if @IsCount != 0 --查询记录总数
begin
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
if @strWhere!=''
set @strSQL="select count(*) as Total from [" + @tblName + "]"
+" where "+ @strWhere
end
exec (@strSQL)
GO