Updating a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table where I want to change a date for all the records that have an
asterisk in a particular column. Here's my stab at the code:

Dim rst As New ADODB.Recordset
rst.Open "tblStaff", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

Do While Not rst.EOF
'If "![txtQual] = 'x'" Or "![txtQual] = 'X'" Then
'![datStartDate] = DateAdd("d", 1, Date)
'End If
Dim strSQL As String
strSQL = "UPDATE tblStaff " & _
"SET [datStartDate] = DateAdd(""d"", 1, Date) " & _
"WHERE [Qual] = 'x'or [txtQual] = 'X'" 'Date served
goes in table
CurrentDb.Execute strSQL, dbFailOnError


rst.MoveNext
Loop
I treid the "If" stmt first and then commented it out and tried the SQL
stmt. I'm sure this is probably not a hard problem. Thanks for any help

Nick
 
I have a table where I want to change a date for all the records that
have an asterisk in a particular column. Here's my stab at the code:
Click to expand...

If you are going to do this on a regular basis, then it's probably
simplest to make it into a Querydef, and then call it using

Querydefs("ResetStartDates").Execute dbFailOnError

There is no harm in making the command on the fly if you prefer.

I don't know what all recordset stuff is for: just pass the SQL to the
CurrentDB().Execute method. If you need to use ADO, then it's probably on
the the Connection.Execute method -- bear in mind, though, that you don't
have a dbFailOnError option in ADO so you'll have to inspect the Errors
collection or something.

Can't see much wrong with the SQL command you have sugggested, except
that the punctuation is messed up around the OR operator. You are not
being consistent about string delimiters, which is important in ADO. I am
also guessing that functions like DATE() and UCASE() are available in
ADO/Jet; you may have to use proper alternatives like SYSTEMDATE and
CONVERT instead.

This is how I would do it, but then I am a DAO man...

UPDATE tblStaff
SET datStartDate = DATEADD('d', 1, DATE())
WHERE UCASE(Qual) = 'X'

Hope that helps


Tim F
 
Thanks. I have now tried this.... It locks up my machine. Any thoughts?

rst.Open "tblStaff", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

Do While Not rst.EOF
Dim strSQL As String
strSQL = "Update tblStaff " & _
"Set datStartDate = DateAdd('d', 1, datStartDate)" & _
"WHERE UCASE(txtQual) = 'X'
CurrentDb.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
 
Do While Not rst.EOF
Dim strSQL As String
strSQL = "Update tblStaff " & _
"Set datStartDate = DateAdd('d', 1, datStartDate)" & _
"WHERE UCASE(txtQual) = 'X'
CurrentDb.Execute strSQL, dbFailOnError
rst.MoveNext
Loop
Click to expand...

What is all this recordset stuff for?? As this stands, you are running
the update as many times as you have records in the table. I'm not
surprised it's taking a long time, particularly as there are all kinds of
locks to be negotiated on the way.


Just remove every line that has a "rst" in it, and simply pass the string
to the execute method.


B Wishes


Tim F
 
Thanks! That worked fine, but I remembered that I can't update the table
until I call a couple of functions that will manipulate the date. How could
I loop through my table checking each record for the "x" so that I can call
these date manipulating functions? I don't seem to be able to get the
MoveNext command to work. Thanks for any suggestions.
 
Set rst = CurrentDb.OpenRecordset([sqlStatement or other recordsource],
dbOpenForwardOnly)
While NOT rst.EOF
rst.edit
rst.fields("datStartDate") = DateAdd('d', 1, datStartDate)
rst.update
rst.movenext
Wend
rst.close
Set rst = Nothing

The code above loops through each record in the recordset (rst) and
updates the datStartDate. To make the update conditional add an if then
as in...

Set rst = CurrentDb.OpenRecordset([sqlStatement or other recordsource],
dbOpenForwardOnly)
While NOT rst.EOF
If UCASE(rst.fields("txtQual")) = 'X' then
rst.edit
rst.fields("datStartDate") = DateAdd('d', 1, datStartDate)
rst.update
end if
rst.movenext
Wend
rst.close
Set rst = Nothing

If you need to do any data manipulation, put the code in the if...then,
capture the results from the data manipulation into a variable and then
put then put the variable where the DateAdd() function exists. Assuming
that its the datStartDate value that you need to update. The example
assumes that you're using a generic recordset such as tblRecords or SQL
statement such as SELECT * FROM tblRecords. It would be possible to use
a statement such as "SELECT * FROM tblRecords WHERE txtQual = 'X';"
which would only return the records to be updated. In which case, you
wouldn't need the If...then since the rst Recordset would only contain
the records to be updated to begin with. I personally would go with the
later method out of habit.

David H
 
Thanks! That worked fine, but I remembered that I can't update the
table until I call a couple of functions that will manipulate the
date. How could I loop through my table checking each record for the
"x" so that I can call these date manipulating functions? I don't
seem to be able to get the MoveNext command to work.
Click to expand...

Going backwards... you don't need the MoveNext. At all. Ever. Under any
circumstances. Even if it's raining outside. The whole point about using
SQL rather than iterating a whole recordset is that it does the whole
table all in one go. No looping. No checking. You just define what you
want to happen and what set of records you want it to happen to. The db
engine will then choose the fastest and most efficient method of updating
the records, releasing locks for other users, and blocking up the network
traffic as little as possible. Everyone wins.

You can call all kinds of date manipulation functions. Using SQL Server,
you get fewer commands that are more flexible; using Jet (i.e. mdb files)
you can use most of the functions available in VBA. Can you be a bit more
specific about what you are actually doing- we may be able to provide
some more specific help?

Glad it helped.

B wishes


Tim F
 
Back
Top