Re: Using a multi-index in VBA

Not sure there is a simple solution for your question, especially if other
users are potentially inserting/deleting/editing between your 50k query
runs, and especially with the way Access handles TOP.

Crucial factors might be:
1. Make sure you allow Windows as much swapfile space as it needs.

2. Make sure you maintain lots of free hard disk space on the drive that
hosts your temp folder and swapfile.

3. Compact the database periodically.

4. Make sure all tables in the query have a primary key.

5. If necessary, create temp table to hold just the primary key value of the
records you want to select, and then inner join this on the main query so as
to give Access a simple and efficient selection group.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EB3F7FAA-D2C6-4660-A476-98A1F7585F72@xxxxxxxxxxxxxxxx
> Oh, I forgot - back to my original issue:
>
> I have a LARGE table. Big. Huge. ;^)
>
> My goal is to limit the number of records read and processed at-a-time (to
> say, 50,000 or so), while maintaining the integrity of the sort. I have
> been
> getting "Out of Memory" errors. So is there some way that I can use
> something
> like OFFSET, LIMIT that will allow the sort to be maintained properly
> across
> multiple passes?
>
> Thanks!
>
> "Allen Browne" wrote:
>
>> Dennis, you don't need to do anything.
>>
>> The Rushmore technology in JET (the query engine in Access) will
>> automatically use the index if the ORDER BY clause matches the same
>> fields
>> in the same order.
>>
>> Rushmore is actually very good at using the indexes automatically.
>>
>>
>> "Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:623944F1-128A-43B6-924E-6010DFC80C79@xxxxxxxxxxxxxxxx
>> >I created a multi-field index (named "Pass2Index") for a dataset. The
>> >fields
>> > are all the ones in the ORDER BY below:
>> >
>> > recSet.Open "SELECT * from [Quarterly Data - Passed] WHERE [Year] = "
>> > &
>> > yearCounter & " ORDER BY [Procedure Code], [Major Program], " & _
>> > "[Recipient ID Num], [Service Date From], [Health Plan Pmt
>> > Date], [Mod1], " & _
>> > "[Health Plan Claim Identifier], [Table Insert Date] DESC"
>> >
>> > Now, how do I actually USE that index in a SELECT statement as above?
>> > What
>> > do I need to change, and to what?
>> >
>> > Thanks!!


.

Relevant Pages

  • Re: Make Table Query - Sorting Errors
    ... Dim OutputTable As DAO.Recordset ... The query to concatenate the CAMPNO values will need to be LOOKING at the ... If you post the query you are using to do this, I beleive we can get it to ... Is there a command that I can use in the module code to first sort the ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... You sort in a query, ... You can do a compound sort in a query. ... "Tom Ellison" wrote: ... resulting table only has one record for each value of "CAMPNO". ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... You have to use a query to sort the data in to your required order and then ... Access MVP 2002-2005, 2007 ... University of Maryland Baltimore County ... How do I impose a sort on the datasheet view? ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... MS Access MVP ... resulting table only has one record for each value of "CAMPNO". ... Is there a command that I can use in the module code to first sort the ... I have a make table query that is supposed to sort in ascending order ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... sorted, it will sort them as they arrive. ... you query them, why is this of any concern to you? ... correctly on the field CAMPNO. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)