Re: merge multiple databases

From: Jim Carlock (anonymous_at_127.0.0.1)
Date: 05/14/04 

Date: Fri, 14 May 2004 13:46:42 -0400

If you put the SQL into queries inside of Access, I'm thinking that
it should get even faster... IE make the query in the new Access file,
and then call that query from the VB application, with an .Execute
if it's an action query, or with the .RecordSet object if it's a Select
query.

If the query inside of Access was named "qSelectAllRecords", then
you can open it with :

Set rs = gDAO_DB.OpenRecordset("qSelectAllRecords") 

-- 
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.
"DIOS" wrote:
My results were astonishing. As YYZ commented the SQL version was way
faster.
Here are my results:
Test applied to merging 8 databases with three tables each. 4 medium sized
databases
and 4 small databases. Four runs were applied and the times were avearged.
Record Looping method: 410 seconds
SQL UPDATE method: 57 seconds
The record looping method had more status updates via a label but the
difference wa still
heavily favoring the SQL method.
Thanx for all the help guys.
AGP
-- 
**********************************************************************
Unpak my email address before sending me personal email
**********************************************************************
"YYZ" <notapplicable> wrote in message
news:eqq9q37NEHA.3452@TK2MSFTNGP10.phx.gbl...
> "DIOS" <sindizzy.pak@softhome.net> wrote in message
> news:UH7oc.72168$Ik.5216674@attbi_s53...
> > That is a very interesting algoithm. It might work so ill give it a
shot.
> > I will create the tempID fiields and then run my queries and updates
against
> > those
> > and just inputa  temp number for tha actual Ids until i fill them in. I
have
> > the iteration
> > algorithm already written but this eems like it may be more efficient
and
> > faster.
>
> In my experience, it should be IMMENSELY faster.  We were doing somewhat
the
> same thing a while ago, copying down a "library" of materials and other
things.
> Initially, we were selecting everything into a recordset, then inserting
into
> the local database.  Once we changed the logic to select from one DB
directly
> into the other, it sped the process up over 100x if I remember correctly.
>
> > I will post my comparisons.
>
> Please do!  I'd be interested to see how it fares.
>
> Matt
>
>

Relevant Pages

  • 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: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)