[AccessD] Editing Records

Oh, I hear ya.  I would take a computer that catches fire over a 'powers
that be' problem ANY DAY! <VBG>
 
What I would recommend then..and again, I'm still a little fuzzy on the
specifics, but I would say take the smaller of the two (which I am assuming
would be the imported file), and dump it into a Collection of custom
Classes.  Base the ID for each class you put into the collection on the
'key' that you are going to be looking for in the SQL records.  Then open
the SQL records as a recordset, and as you run through them, take the ID,
and do a check for a matching class in your collection.  If there is a
match, do the update, if there isn't move on.
 
That way you are running in memory with one of the lists, and you aren't
opening and closing a recordset to find a match.
 
Just a thought.
 
Drew

-----Original Message-----
From: Swisher, Timothy B. [mailto:tswisher at gfnet.com]
Sent: Wednesday, March 05, 2003 10:12 AM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Editing Records


The powers that be do not want links.  I need to use code.
 
Tim

-----Original Message-----
From: Drew Wutka [mailto:DWUTKA at marlow.com] 
Sent: Wednesday, March 05, 2003 10:51 AM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Editing Records


What is preventing you from making a link to the table(s) in SQL?  If you
have a linked table, you can make a query that pulls both recordsets up,
which would allow for the dynamic criteria and the dynamic update data.
 
Drew

-----Original Message-----
From: Swisher, Timothy B. [mailto:tswisher at gfnet.com]
Sent: Wednesday, March 05, 2003 9:32 AM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Editing Records


I think I need to use a recordset because I do not have the validation data
available to the app in any other form.
 
More background...
We get a file from one of our apps, I import that data into an Access db,
that data needs to be validated against our SQL Server.  The data from the
SQL Server is unbound, no table links.  I basically send the server a
project number, if it returns a record, its valid, if not its an error.  We
are checking for errors on project numbers, whether its active etc.  We also
use the project number and other data to find information about the project
and add that to the record, who it belongs, client, etc.  There is different
criteria for each validation.  For each record, I could have anywhere from
no changes to 13 changes.   
 
Thanks again.
 
Tim
 
-----Original Message-----
From: Susan Harkins [mailto:harkins at iglou.com] 
Sent: Wednesday, March 05, 2003 10:03 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Editing Records



I didn't thoroughly check, but is there a reason you need to use a recordset
to do this -- it looks to me like an Update query might be faster. 
 
Susan H. 
 
 

----- Original Message ----- 
From: Swisher,  <mailto:tswisher at gfnet.com> Timothy B. 
To: accessd at databaseadvisors.com <mailto:accessd at databaseadvisors.com>  
Sent: Wednesday, March 05, 2003 8:06 AM
Subject: [AccessD] Editing Records


Hello group, I have a procedure (A2k) that opens a recordset (DAO) and
cycles through the data validating/updating several fields for each record.
My question is, what is the best way to do this, 

Like this 

.edit 
If validation1 = false then 
        change data 
End if 

If validation2 = false then 
        change data 
End if 
.update 

Or  

If validation1 = false then 
        .edit 
        change data 
        .update 
End if 

If validation2 = false then 
        .edit 
        change data 
        .update 
End if 

I have about 13 validations that need to be done on about 90,000 records.
Each validation goes to a SQL Server BE and check the validity of the data.
There is a huge difference in speed opening and closing the recordset so
many times, but if that is the better way to go, then so be it.  All help is
appreciated.  TIA.

Tim 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030305/edb7ac7e/attachment-0001.html>