MartyConnelly
martyconnelly at shaw.ca
Thu May 27 13:45:25 CDT 2004
I am just wondering where the problem lies, with Access 2000 or was it
XP , you can set the Forms recordset to an ado recordset albeit read only.
There are workarounds to handle disconnected and editable recordsets. I
must be looking at something the wrong way.
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Private Sub Command0_Click()
cnn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=pubs;" & _
"User ID=sa;" & _
"Password=;"
cnn.Open
rst.Source = "Select * from authors"
rst.CursorLocation = adUseClient
rst.Open , cnn
Set Me.Recordset = rst
End Sub
paul.hartland at fsmail.net wrote:
>Martin,
>Stumped on this one the only way I can think of doing something similar is create a temporary table, write the result set into the temporary table and have an additional counter field 1 to ??? then with an access query have something like
>SELECT * FROM tblTemp WHERE tblTempCounterField>=1 And tblTempCounterField<=100
>
>then change the query to
>SELECT * FROM tblTemp WHERE tblTempCounterField>=101 And tblTempCounterField<=200
>
>etc etc as the user presses the next or previous page button.
>Paul Hartland
>PS if you come across a better solution I would like to know how to do it for future reference.
>
>
>
>
>
>Message date : May 26 2004, 07:33 PM
>>From : "Martin Reid"
>To : "Access Developers discussion and problem solving"
>Copy to :
>Subject : Re: [AccessD] Paging Recordsets
>I know. I want to to the same thing with data from SQL Server in Access.
>
>Martin
>
>
>----- Original Message -----
>From: "MartyConnelly"
>To: "Access Developers discussion and problem solving"
>
>Sent: Wednesday, May 26, 2004 6:40 PM
>Subject: Re: [AccessD] Paging Recordsets
>
>
>
>
>>Paging is quite commonly done on ASP websites
>>
>>You just set or use three properties of the ADO recordset
>>' Create recordset and set the page size (no of records per page)
>>Set objPagingRS = New ADODB.Recordset
>>objPagingRS.PageSize = iPageSize
>>' Get the count of the pages using the given page size
>>iPageCount = objPagingRS.PageCount
>>' Move to the selected page number
>>objPagingRS.AbsolutePage = iPageCurrent
>>
>>Here is some quickly cobbled sample code stolen from
>>
>>
>http://www.asp101.com
>
>
>>Search on "paging" as there several methods available including placing
>>in an array
>>There are probably articles floating around as to best method for
>>performance
>>
>>
>>Sub AdoPaging()
>>'stolen from
>>http://www.asp101.com/samples/viewasp.asp?file=db%5Fpaging%2Easp
>>' BEGIN USER CONSTANTS
>>Dim CONN_STRING As String
>>Dim CONN_USER As String
>>Dim CONN_PASS As String
>>
>>CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>>"Data Source=C:\Program Files\Microsoft
>>Office\Office\Samples\Northwind.mdb;" & _
>>"User Id=admin;" & "Password="
>>
>>' Our SQL code - overriding values we just set
>>' Comment out to use Access
>>'CONN_STRING = "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
>>' & "Initial Catalog=samples;Connect Timeout=15;" _
>>' & "Network Library=dbmssocn;"
>>'CONN_USER = "samples"
>>'CONN_PASS = "password"
>>' END USER CONSTANTS
>>
>>' Declare our vars
>>Dim iPageSize As Long 'How big our pages are
>>Dim iPageCount As Long 'The number of pages we get back
>>Dim iPageCurrent As Long 'The page we want to show
>>Dim strOrderBy As String 'A fake parameter used to illustrate
>>passing them
>>Dim strSQL As String 'SQL command to execute
>>Dim iRecordsShown As Long 'Loop controller for displaying just
>>iPageSize records
>>Dim I As Long 'Standard looping var
>>Dim objPagingConn As ADODB.Connection
>>Dim objPagingRS As ADODB.Recordset
>>
>>' Get parameters
>>' set number of records per page
>>
>>iPageSize = 10 ' You could easily allow users to change this
>>
>>' Set page to show or default to 1
>>
>>iPageCurrent = 2
>>
>>
>>' If you're doing this script with a search or something
>>' you'll need to pass the sql from page to page. I'm just
>>' paging through the entire table so I just hard coded it.
>>' What you show is irrelevant to the point of the sample.
>>'strSQL = "SELECT * FROM sample ORDER BY id;"
>>
>>strOrderBy = "'Customer ID'"
>>'strOrderBy = "last_name,first_name,sales"
>>
>>' Build our SQL String using the parameters we just got.
>>strSQL = "SELECT * FROM customers ORDER BY " & strOrderBy & ";"
>>
>>
>>Debug.Print strSQL
>>
>>' Now we finally get to the DB work...
>>' Create and open our connection
>>Set objPagingConn = New ADODB.Connection
>>'objPagingConn.Open CONN_STRING, CONN_USER, CONN_PASS
>>objPagingConn.Open CONN_STRING
>>' Create recordset and set the page size
>>Set objPagingRS = New ADODB.Recordset
>>objPagingRS.PageSize = iPageSize
>>
>>' You can change other settings as with any RS
>>'objPagingRS.CursorLocation = adUseClient
>>objPagingRS.CacheSize = iPageSize
>>
>>' Open RS
>>objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly,
>>adCmdText
>>
>>' Get the count of the pages using the given page size
>>iPageCount = objPagingRS.PageCount
>>
>>' If the request page falls outside the acceptable range,
>>' give them the closest match (1 or max)
>>If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
>>If iPageCurrent < 1 Then iPageCurrent = 1
>>
>>' Check page count to prevent bombing when zero results are returned!
>>If iPageCount = 0 Then
>>Debug.Print "No records found!"
>>Else
>>' Move to the selected page
>>objPagingRS.AbsolutePage = iPageCurrent
>>
>>' Start output with a page x of n line
>>
>>' Show field names in the top row
>>Dim strtitles As String
>>strtitles = ""
>>For I = 0 To objPagingRS.Fields.Count - 1
>>strtitles = strtitles & objPagingRS.Fields(I).Name & " "
>>Next 'I
>>Debug.Print strtitles & vbCrLf
>>
>>' Loop through our records and ouput 1 row per record
>>iRecordsShown = 0
>>Dim strFields As String
>>strFields = ""
>>Do While iRecordsShown < iPageSize And Not objPagingRS.EOF
>>
>>For I = 0 To objPagingRS.Fields.Count - 1
>>
>>strFields = strFields & objPagingRS.Fields(I) & ","
>>
>>Next 'I
>>Debug.Print strFields & vbCrLf
>>'clear strfields shown
>>strFields = ""
>>
>>' Increment the number of records we've shown
>>iRecordsShown = iRecordsShown + 1
>>' Can't forget to move to the next record!
>>objPagingRS.MoveNext
>>Loop
>>
>>' All done - close table
>>
>>End If
>>
>>' Close DB objects and free variables
>>objPagingRS.Close
>>Set objPagingRS = Nothing
>>objPagingConn.Close
>>Set objPagingConn = Nothing
>>
>>End Sub
>>
>>
>>Martin Reid wrote:
>>
>>
>>
>>>Anyone every done any work on paging large recordsets in Access. I want
>>>
>>>
>to
>
>
>>>look at the possibility of breaking up massive recordsets returned from
>>>
>>>
>SQL
>
>
>>>Server in page sets of say 100 records. Rather than pull them all at the
>>>start I want the user to be able to select Page 1, Page etc etc then move
>>>freely between pages.
>>>
>>>Only 100 records (or less) would be taken of the server at one time.
>>>
>>>I cannot use filters at the client end. All records MUST be available?
>>>
>>>MArtin
>>>
>>>
>>>
>>>
>>>
>>--
>>Marty Connelly
>>Victoria, B.C.
>>Canada
>>
>>
>>
>>--
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada