Prev: DB guide
Next: when to use many-many relationships or different data bases
From: Jeff Boyce on 15 Dec 2009 08:43
In your experience, does your VBA code run faster (I'm assuming a
record-by-record iteration through a recordset) than the set-level
operations that Access queries are designed to provide?

(I'm always looking for counter-examples and/or confirmation...)

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"rolaaus" <rolaaus(a)discussions.microsoft.com> wrote in message
news:30A6CF5A-5858-40B1-9384-3849DB0DE396(a)microsoft.com...
> I'm not sure how accurate this would be, or how applicable it would be to
> your version of Access, but how about converting your query to VBA code?
>
> "pccdavef" wrote:
>
>> Hello all -
>>
>> I have a situation where I'm importing substantial amounts of data
>> (30,000 to
>> 120,000 rows at a pop) from three external sources through a text file or
>> an
>> Excel spreadsheet into three different data tables. I've established
>> lookup
>> tables for those fields that can reasonably be normalized between and
>> amongst
>> the data tables.
>>
>> The process I'm going through is:
>>
>> 1. Import the raw data into an "import" table that matches the structure
>> of
>> the source data. Also included in the import table are columns for
>> foreign
>> keys of 'normalizable' fields, which are set to 0 when the source data is
>> imported.
>>
>> 2. Append any new lookup data that may be present in the source file to
>> the
>> lookup tables.
>>
>> 3. Run a series of update queries on the import table to update the
>> foreign
>> key fields with the keys of the lookup data. Depending on the source data
>> file, there are between 3 and 7 of these update queries.
>>
>> 4. Append new records into the data table using only the foreign key
>> values
>> where applicable.
>>
>> I'm discovering that the update queries in step 3 are taking a LONG time
>> to
>> run (several minutes each), which is going to annoy my users to no end.
>>
>> My questions are:
>> - are there other, better processes or data structures to use?
>> - is there a way of optimizing update queries?
>>
>> I appreciate any help or suggestions -
>> Dave
>>
>> .
>>


First  |  Prev  | 
Pages: 1 2
Prev: DB guide
Next: when to use many-many relationships or different data bases