|
 |
access thread: is there a faster way
Message #1 by "chris angus" <chris@a...> on Wed, 31 Oct 2001 20:31:18
|
|
can anyone tell me if there is a faster way than this code below.
there are about 408796 records in data1.recordset and i can that it takes
a few seconds for the data1.recordset.findfirst
it takes about twenty minutes to import 600 lines from a csv delimited
file, and i've got a 4500 to do.
With Data2.Recordset
.FindFirst "subkey1 = '" & sPart & "' And subkey2 = '" _
& sSuplier & "'"
If (Data2.Recordset!subkey2 = sSuplier) Then
Data2.Recordset.Edit
Data2.Recordset!a5 = sCost
Else
.AddNew
Data2.Recordset!subkey1 = sPart
Data2.Recordset!a13 = sSuplier
Data2.Recordset!subkey2 = sSuplier
Data2.Recordset!a5 = sCost
Data2.Recordset!Prefix = "C"
Data1.Recordset.FindFirst "subkey1 = '" & sPart & "' AND Prefix = 'C'"
Data2.Recordset!a2 = Data1.Recordset!a2
.Update
End If
Message #2 by "John Ruff" <papparuff@c...> on Wed, 31 Oct 2001 12:39:54 -0800
|
|
How about looking at the Seek function.
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Wednesday, October 31, 2001 8:31 PM
To: Access
Subject: [access] is there a faster way
can anyone tell me if there is a faster way than this code below. there
are about 408796 records in data1.recordset and i can that it takes
a few seconds for the data1.recordset.findfirst
it takes about twenty minutes to import 600 lines from a csv delimited
file, and i've got a 4500 to do.
With Data2.Recordset
.FindFirst "subkey1 = '" & sPart & "' And subkey2 = '" _
& sSuplier & "'"
If (Data2.Recordset!subkey2 = sSuplier) Then
Data2.Recordset.Edit
Data2.Recordset!a5 = sCost
Else
.AddNew
Data2.Recordset!subkey1 = sPart
Data2.Recordset!a13 = sSuplier
Data2.Recordset!subkey2 = sSuplier
Data2.Recordset!a5 = sCost
Data2.Recordset!Prefix = "C"
Data1.Recordset.FindFirst "subkey1 = '" & sPart & "' AND Prefix
'C'"
Data2.Recordset!a2 = Data1.Recordset!a2
.Update
End If
Message #3 by John Fejsa <John.Fejsa@h...> on Thu, 01 Nov 2001 08:22:47 +1100
|
|
Hi Chris,
By using 'Seek' statement instead of 'FindFirst' your function s will run
about 5 times faster.
____________________________________________________
John Fejsa
Systems Analyst/Computer Programmer
Hunter Centre for Health Advancement
Locked Bag 10
WALLSEND NSW 2287
Phone: (02) 49246 336 Fax: (02) 49246 209
____________________________________________________
The doors we open and close each day decide the lives we live
____________________________________________________
>>> chris@a... 01/11/2001 7:31:18 >>>
can anyone tell me if there is a faster way than this code below.
there are about 408796 records in data1.recordset and i can that it
takes
a few seconds for the data1.recordset.findfirst
it takes about twenty minutes to import 600 lines from a csv delimited
file, and i've got a 4500 to do.
With Data2.Recordset
.FindFirst "subkey1 =3D '" & sPart & "' And subkey2 =3D '" _
& sSuplier & "'"
If (Data2.Recordset!subkey2 =3D sSuplier) Then
Data2.Recordset.Edit
Data2.Recordset!a5 =3D sCost
Else
.AddNew
Data2.Recordset!subkey1 =3D sPart
Data2.Recordset!a13 =3D sSuplier
Data2.Recordset!subkey2 =3D sSuplier
Data2.Recordset!a5 =3D sCost
Data2.Recordset!Prefix =3D "C"
Data1.Recordset.FindFirst "subkey1 =3D '" & sPart & "' AND Prefix =3D
'C'"
Data2.Recordset!a2 =3D Data1.Recordset!a2
.Update
End If
---
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Wed, 31 Oct 2001 13:14:47 -0800
|
|
SQL is almost always faster than walking a recordset--you might give that a
try. It looks like you need an INSERT INTO (aka Append) query for the
records where Source.Subkey2 <> sSuplier and an UPDATE query for the rest.
The INSERT INTO would probably look something like:
INSERT INTO Data2
(Subkey1 , a13 , subkey2 , a5 , Prefix , a2)
SELECT sPart , sSuplier , sSuplier , sCost , "C" , a2
FROM Data1
WHERE NOT (Data2.Subkey1 = Data1.SPart AND Data2.Subkey2
Data1.sSuplier) ;
Where Data1 and Data2 are the names of the tables (or queries) from which
your recordsets are drawn. The update query might be
UPDATE Data2 INNER JOIN
Data1
ON Data2.Subkey1 = Data1.SPart AND
Data2.Subkey2 = Data1.sSuplier
SET Data2.a5 = Data1.sCost ;
If you're interested, there's a good SQL tutorial page at:
http://w3.one.net/~jhoffman/sqltut.htm
HTH,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Wednesday, October 31, 2001 12:30 PM
To: Access
Subject: [access] is there a faster way
can anyone tell me if there is a faster way than this code below.
there are about 408796 records in data1.recordset and i can that it takes
a few seconds for the data1.recordset.findfirst
it takes about twenty minutes to import 600 lines from a csv delimited
file, and i've got a 4500 to do.
With Data2.Recordset
.FindFirst "subkey1 = '" & sPart & "' And subkey2 = '" _
& sSuplier & "'"
If (Data2.Recordset!subkey2 = sSuplier) Then
Data2.Recordset.Edit
Data2.Recordset!a5 = sCost
Else
.AddNew
Data2.Recordset!subkey1 = sPart
Data2.Recordset!a13 = sSuplier
Data2.Recordset!subkey2 = sSuplier
Data2.Recordset!a5 = sCost
Data2.Recordset!Prefix = "C"
Data1.Recordset.FindFirst "subkey1 = '" & sPart & "' AND Prefix = 'C'"
Data2.Recordset!a2 = Data1.Recordset!a2
.Update
End If
|
|
|
View Cart
