OpenDatabase, dbRunAsync..

JaedenRuiner

Registered User.
Local time
Today, 15:52
Joined
Jun 22, 2005
Messages
154
Okay,

Setup: 2 databases, FrontEnd controlling BackEnd

FrontEnd:
ProgressForm
ClassModule: Initialize

BackEnd:
Result Table - Table that will be populated by Calculation Query
Linked Query
Calculation Query - Based on Linked Query with formatting for certain fields
Example Calculations
Code:
  Original: Remarks: France/Germany *S .W 2P
  Calculated: 
      Flags:*S .W 2P
      Country1: France
      Country2: Germany
  
  Original: Title: "Self Sealing Stem-Bolt ('56)"
                      or
              Title: "Self Sealing Stem-Bolt>>T-Shaped Ste"
              Remarks: "m Bolt From Star-Trek"
  Calculated:
       Title: "Self Sealing Stem-Bolt"
       Paren: "'56"
                      or
       Title: "Self Sealing Stem-Bolt"
       AltTitle: "T-Shaped Stem Bold From Star-Trek"
with calculations like these, you can imaging the pain i'm in with importing this foxpro dbf table.

What I Want:
Ignoring the pesky details of the VB code in my Class module, which is basically designed to process each record and update the progress meter bar via events, while allowing the user to cancel the operation. I start by setting up a DAO.Database object pointing to the BackEnd database, and Open two Record sets, one to the query and one to the table. The BackEnd and FrontEnd databases are both pure Access databases ( so that means Jet i believe), except for the foxpro dbf table that is linked in as the static Query in the BackEnd - but since the query itself is linked into MS Access it shouldn't matter.
I want to process about 30 - 40 thousand records, transfering them from the BackEnd Query to the BackEnd table. This is so that I can access the formatted information from the BackEnd table without having to perform the pesky string manipulations every second. (We're dealing with about 74 fields per record - Yes, this is a massive table, but I'm working with what i've got, so I can't do much about it. *shrug*) As you can guess populating a query of this magnitude is slow by no description, but to add on top of it, like a dozen plus string calculations via functions is on the far side of cosmicly slow.
So i have three issues to work with:
  • How to Open the back end Database/Reordsets so that I can execute a RecordSet.MoveLast (to count total records) in a Asynchronous manner, so that my frontend execution is not delayed.
  • How to efficiently transfer the fields from one Recordset (Query that performs the string calculations) to the other recordset (Table that recieves straightup values). (i'm wondering if I sould do this via a BeginTrans/CommitTrans Batch update or something).
    • Also: Which method is more efficient:
      Code:
      for i=0 to rst.fields.count-1
         rst.fields(i) = rst2.fields(i)
      next i
    • Or should i just name the fields individually?
      Code:
         rst!field1=rst2!field1
         rst!field2=rst2!field2
          ...
         rst!field74=rst2!field74
  • Finally, how to compare each record, to see if I need to UPDATE, or ADD, or SKIP. I can base my initial comparison for Adding off of two Fields that together will be unique, thus using FindFirst or something I could determine if i need to add the record. However which is faster:
    • Searching for the record
      1. If Found -> Goto Update Check
      2. If Not Found -> .Addnew
    • Clear Entire Table, and just add each record new
    Also a speed question on the Update Checking, which would affect the previous question:
    • Is it faster to compare all 74 fields for differences and then updating differing fields
    • Just set all the fields' values automatically, whether they are different or not.

This is a hefty request I know, but i'm trying to find the smoothest solution to processing these records for importation into an Access database, and I would really prefer to do it with a back end database instead of in my primary front end database.

Thanks a bunch,
Jaeden "Sifo Dyas" al'Raec Ruiner
 
Out of interest

if you have a query that returns all the items you want to insert into another table, and you have the table,

why not insert them with an append query. Its far easier, and probably a darn sight quicker than iterating record sets.

you can count the records quickly with a dcount("*",queryname)

If the length of time you see the hourglass is an issue, and your input data is not changing, you could presumably filter your query by alphabetic or date means to do it piecemeal, rather than doing all 74000 in one go.
 
Okay,

True, i have been doing everything so far as SQL Append queries, but quick question.

If I use DoCmd.RunSQL, then I must:
"Insert into table (fields) IN 'DB' SELECT qry_Source.fields from qry_Source IN 'DB'"

However, If I were to go with a database obect:
set db = Opendatabase("BackEnd.mdb")
and with that database object either create an Action QueryDef (with a parameter that I would use for the piecemeal execution) or
directly call db.Execute("insert into..")
would it be necessary to include the "IN 'DB'" clause in the Db objects sql statement, or is that superfluous since the db object is already pointing to the database i'm working on?

Thanks

Jaeden "Sifo Dyas" al'Raec Ruiner

PS - Also,

Using an action query of a sorts, is there a smooth way to use an UPDATE query on these tables. The Example for UPDATE goes as such:
Code:
UPDATE Orders
SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03
WHERE ShipCountry = 'UK';
But that doesn't seem to allow for setting Order.OrderAmount = Input.OrderAmount. Will the INSERT INTO query always APPEND, or will it update as well given a UNIQUE index or something?
 
Are you using Access front end - I meant designing visual queries to handle all this sort of stuff, and letting Access take care of the SQL.

However if your front end points to the table, either as in intrinsic table, or as an attached table I am sure you can do whatever you want by just referring to the front end table name. I am not sure why you need to open the backend database directly.
 
gemma-the-husky said:
you can count the records quickly with a dcount("*",queryname)

DCount("*", queryname) -> Produces a Runtime error (6): Overflow

*shrug*
Jaeden "Sifo Dyas" al'Raec Ruiner
 
gemma-the-husky said:
I am not sure why you need to open the backend database directly.
Because I want to. There are always 10000 ways to peel an egg. However, as it is, I still don't know the answer to my question, regardless of whether or not I decide in the end to use the front end access method, or a backend programmattic method. How i do something is unimportant, that my question gets answered so i have all the information myself to make an educated decision is.
so, if I use db.execute() is the IN clause necessary?
thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
 
I can't answer that, as I rarely write direct SQL statements, if I can design a visual query to achieve the same thing. Sorry
 

Users who are viewing this thread

Back
Top Bottom