Re: Using a multi-index in VBA

Tech-Archive recommends: Fix windows errors by optimizing your registry

This is a batch-processed database. No users except the one running the
queries will *ever* be in it (it's an audit database).

1) I'll check that setting this morning

2) Got that

3) yup

4) They do - it's "recno" (auto-numbered field).

5) I don't think that will be meaningful based on my actual key.

Thanks for these tips. If I move the tables to SQL (pending now), will I
have the same issues then? Do SQL databases have a hard size-limit (like
Access' 2 Gb)?

Again, my sincere thanks for your information!

Dennis

"Allen Browne" wrote:

> 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: Very slow query
    ... indexed for all the secondary tables, only the primary key is indexed). ... create a new blank database and immediately turn OFF the Name ... With 14 secondary tables or less, my query runs in 1 sec or less. ... in forms, base the form on the primary data table, and use combobox or ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: Query to merge
    ... Thanks for that insite - I am sure that the additional customers (that would ... simply build your query to give you all records from the one, ... Say it was a customer database. ... customers with the same primary key. ...
    (microsoft.public.access.queries)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)