Do Update Loop

[Mark]

Hi I have a tricky one I could not find any reference to it in previous posts.

I have a linked table in SQL (Table - X) with 200,000+ entries and has 300+
fields. I update table X's 300+ fields with code based on data in another
table I have in an Access (Table - Z). Each field has a primarykey as
'Peoples Names'

I extracted 100 'peoples names' from X and updated their data in table Z
with different data and now I would like to re-update table X with the new
updated data. I now need to update the 300+ fields in X by running the code
for the 100 peoples names that are in table Z, only.
I originally made a:
do
'run updates'
rst.movenext
loop

which worked fine when i originally did the 200,000entries but now i only
want to update the 100 peoplenames in table Z, otherwise ithe performance of
updating the SQL is way too slow as it will run through every one of the
entries('peoples names')
Am i clear?
Any help would be appreciated.
Mark

 

[Mark]

After sending myself through a loop i think i finally worked it out
'***************
Do Until rst.eof
if dlookup("[peoplesnames]","[Z]","[peoplesnames]='"&rst!peoplesnames & "'")
= True Then
'Run the code'
Else
endif
rst.movenext
loop
'***************
not instant but does it quick enough!

 

[Robert Morley]

Yeah, DLookup is relatively slow (it would be faster to open a second
recordset and go through it that way or just use a JOIN query), but it can
certainly save on coding, and for so few names, it's not THAT big of an issue.



Rob

After sending myself through a loop i think i finally worked it out
'***************
Do Until rst.eof
if dlookup("[peoplesnames]","[Z]","[peoplesnames]='"&rst!peoplesnames & "'")
= True Then
'Run the code'
Else
endif
rst.movenext
loop
'***************
not instant but does it quick enough!

Hi I have a tricky one I could not find any reference to it in previous posts.

I have a linked table in SQL (Table - X) with 200,000+ entries and has 300+
fields. I update table X's 300+ fields with code based on data in another
table I have in an Access (Table - Z). Each field has a primarykey as
'Peoples Names'

I extracted 100 'peoples names' from X and updated their data in table Z
with different data and now I would like to re-update table X with the new
updated data. I now need to update the 300+ fields in X by running the code
for the 100 peoples names that are in table Z, only.
I originally made a:
do
'run updates'
rst.movenext
loop

which worked fine when i originally did the 200,000entries but now i only
want to update the 100 peoplenames in table Z, otherwise ithe performance of
updating the SQL is way too slow as it will run through every one of the
entries('peoples names')
Am i clear?
Any help would be appreciated.
Mark

 

[Steve Sanford]

Hi Mark,

I've been looking at the code snippet you posted and I can't figure out why
you are using DLookUp(). You have If DLookUp(....) = TRUE Then. But DLookUp()
doesn't return a TRUE or FALSE. If DLookUp() doesn't find a match a NULL is
returned which is neither True or False. A better way might be If
LEN(DLookUp(....) ) >0 Then...

About the DLookUp().... You are looking up a value from a field
(peoplesnames) in table "Z" where the field equals a field from a recordset.
But it looks like the recordset is based on the table Z also.

You said you: extract records from table X (Linked SQL server table??) to
table Z (Access table). Modify the records in table Z, then update table X
with modified data from table Z.

Can't you:
open a recordset based on table Z
loop thru the recordset
Create a string SQL Update query on the fly
sSQL = "UPDATE TableX Field1, Field2, ... , FieldN
sSQL = sSQL & " VALUES(.......)"
sSQL = sSQL & " WHERE [peoplesnames]='" & rst!peoplesnames & "'"
([peoplesnames] is text??)
CurrentDB.Execute sSQL, dbfailonerror


I don't know what code you have for 'Run the code', but the above idea
should work.

Is [peoplesnames] a text field? What does the data look like? Is it the
actual names of people?

Would you post the SQL for for the recordset? And maybe explain what 'Run
the code' does(and maybe the code if it is not too long).

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

After sending myself through a loop i think i finally worked it out
'***************
Do Until rst.eof
if dlookup("[peoplesnames]","[Z]","[peoplesnames]='"&rst!peoplesnames & "'")
= True Then
'Run the code'
Else
endif
rst.movenext
loop
'***************
not instant but does it quick enough!

Hi I have a tricky one I could not find any reference to it in previous posts.

I have a linked table in SQL (Table - X) with 200,000+ entries and has 300+
fields. I update table X's 300+ fields with code based on data in another
table I have in an Access (Table - Z). Each field has a primarykey as
'Peoples Names'

I extracted 100 'peoples names' from X and updated their data in table Z
with different data and now I would like to re-update table X with the new
updated data. I now need to update the 300+ fields in X by running the code
for the 100 peoples names that are in table Z, only.
I originally made a:
do
'run updates'
rst.movenext
loop

which worked fine when i originally did the 200,000entries but now i only
want to update the 100 peoplenames in table Z, otherwise ithe performance of
updating the SQL is way too slow as it will run through every one of the
entries('peoples names')
Am i clear?
Any help would be appreciated.
Mark

 

[MArk]

Thanks steve
Apologies for no response. was away. but problem is solved with my dlookup
for now.

Hi Mark,

I've been looking at the code snippet you posted and I can't figure out why
you are using DLookUp(). You have If DLookUp(....) = TRUE Then. But DLookUp()
doesn't return a TRUE or FALSE. If DLookUp() doesn't find a match a NULL is
returned which is neither True or False. A better way might be If
LEN(DLookUp(....) ) >0 Then...

About the DLookUp().... You are looking up a value from a field
(peoplesnames) in table "Z" where the field equals a field from a recordset.
But it looks like the recordset is based on the table Z also.

You said you: extract records from table X (Linked SQL server table??) to
table Z (Access table). Modify the records in table Z, then update table X
with modified data from table Z.

Can't you:
open a recordset based on table Z
loop thru the recordset
Create a string SQL Update query on the fly
sSQL = "UPDATE TableX Field1, Field2, ... , FieldN
sSQL = sSQL & " VALUES(.......)"
sSQL = sSQL & " WHERE [peoplesnames]='" & rst!peoplesnames & "'"
([peoplesnames] is text??)
CurrentDB.Execute sSQL, dbfailonerror


I don't know what code you have for 'Run the code', but the above idea
should work.

Is [peoplesnames] a text field? What does the data look like? Is it the
actual names of people?

Would you post the SQL for for the recordset? And maybe explain what 'Run
the code' does(and maybe the code if it is not too long).

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

After sending myself through a loop i think i finally worked it out
'***************
Do Until rst.eof
if dlookup("[peoplesnames]","[Z]","[peoplesnames]='"&rst!peoplesnames & "'")
= True Then
'Run the code'
Else
endif
rst.movenext
loop
'***************
not instant but does it quick enough!

Hi I have a tricky one I could not find any reference to it in previous posts.

I have a linked table in SQL (Table - X) with 200,000+ entries and has 300+
fields. I update table X's 300+ fields with code based on data in another
table I have in an Access (Table - Z). Each field has a primarykey as
'Peoples Names'

I extracted 100 'peoples names' from X and updated their data in table Z
with different data and now I would like to re-update table X with the new
updated data. I now need to update the 300+ fields in X by running the code
for the 100 peoples names that are in table Z, only.
I originally made a:
do
'run updates'
rst.movenext
loop

which worked fine when i originally did the 200,000entries but now i only
want to update the 100 peoplenames in table Z, otherwise ithe performance of
updating the SQL is way too slow as it will run through every one of the
entries('peoples names')
Am i clear?
Any help would be appreciated.
Mark