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
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:
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
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"
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
- Also: Which method is more efficient:
- 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
- If Found -> Goto Update Check
- If Not Found -> .Addnew
- Clear Entire Table, and just add each record new
- 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.
- Searching for the record
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