Access Update / Batch Update ???? HELP

[KenBZim]

I am using VB6 ADO and Access Database. There are 5PCs connected to an Access database which resides on a designated server.

Problem is the code below updates most of the time but in some cases does not update??
All other update processes within the app work fine. Nobody seems to have a problem with anything other than this particular function.
My thought was that I should use ‘Updatebatch’ rather than ‘Update’, but if I do I get an error. Should I include the update within the Loop?
Anyone got any advice??

Dim RSLog as New ADODB.Recordset

RSLog.Open "Select * From WorkTickets_Tracking", Datacon, adOpenStatic, adLockOptimistic

For I = 0 To 100
'Find this workticket Number
RSLog.Find "Workticket=" + Str$(wt(I)), 0, adSearchForward, 1
If RSLog.EOF Then GoTo NextWorkTicket 'Work ticket number not found
'The item is found so update the table

RSLog("Status") = True ‘Set the status true

NextWorkTicket:
Next I
RSLog.Update
RSLog.Close

[amrita]

What is the exact error ??
Set the recordset's CursorLocation as adUseClient, and see

[si_the_geek]

You definitely should be including the .Update within the loop - as .Update only affects the current record, and you are changing which record is current by using the .Find


Note that there are two other things you should ideally be changing, the first is your use of GoTo (which is frowned upon) when there is no reason to do so - an End If (which takes less code, and makes your code easier to read) would do instead.

Next up you should never use "Dim ... As New ..", instead you should Dim and Set separately (as well as setting it to Nothing when you are finished), as then the memory is managed correctly. The way it is now, you only regain the memory that was used (which could potentially be several megabytes) when the variable goes out of scope, and the garbage collector (which isn't very good in Classic VB) realises it needs to clear up.

Here's how I would change your code:

Dim RSLog as ADODB.Recordset
Set RSLog = New ADODB.Recordset

RSLog.Open "Select * From WorkTickets_Tracking", Datacon, adOpenStatic, adLockOptimistic

For I = 0 To 100
  'Find this workticket Number
  RSLog.Find "Workticket=" + Str$(wt(I)), 0, adSearchForward, 1
  If Not RSLog.EOF Then  'Work ticket number found
    'The item is found so update the table
    RSLog("Status") = True 'Set the status true
    RSLog.Update
  End If
Next I

RSLog.Close
Set RSLog = Nothing

Note also that all of this can be replaced by a single line of code - an Update statement (with an appropriate Where clause) executed on the connection object. It would not only be much less code, but should also run noticeably quicker.

[KenBZim]

Thanks
I am not actually getting an error with the code as shown.
What is strange is that it appears for the most part that the code works ok and the update takes place. Then once in a while a user will report that they have had to re run the function because the update did not occur???

[si_the_geek]

I don't use .Find myself, but unless it does an implicit Update you will only be updating the record that has Workticket= wt(100)

I had missed the wt() before, so it couldn't be just a one line replacement to use Update.. it would take 4 or 5 lines instead (using a loop to build an IN clause), but would still be shorter and faster.

[KenBZim]

Thanks
That is very valuable help
I haven't had much experience using Update / Insert Statements all I have ever used in the past is delete i.e
Sql="Delete From table.... Where....."
connection.Execute sql
Are these the same as the above?
How would one perform an insert based on the code I already have?

[si_the_geek]

That's very similar.. the major between a Delete and an Update is that for an Update you need to specify the fields to alter, and the values to give them, eg:

UPDATE table
SET field1 = value1, field2 = value2
WHERE condition(s)

For a better explanation of Update and other SQL statements, see the SQL tutorial link in the Tutorials/FAQs thread at the top of this forum.


My initial thought (before I saw wt) was this:

Datacon.Execute "UPDATE WorkTickets_Tracking " _
              & "SET Status = True " _
              & "WHERE Workticket Between 0 And 100"

..however, this isn't right for you, so using the wt array too we can come up with this:

Dim strSQL as String, intLoop as Integer
  For intLoop = 0 to 100
    strSQL = strSQL & "," & wt(intLoop)
  Next intLoop
  strSQL = Mid$(strSQL,2)  '(remove comma at the start)

  strSQL = "UPDATE WorkTickets_Tracking " _
         & "SET Status = True " _
         & "WHERE Workticket IN(" & strSQL & ")"
  Datacon.Execute strSQL

If the wt array only contains elements 0 to 100, the loop can be replaced by the Join function, would give this:

Dim strSQL as String
  strSQL = Join(wt, ",")
  strSQL = "UPDATE WorkTickets_Tracking " _
         & "SET Status = True " _
         & "WHERE Workticket IN(" & strSQL & ")"
  Datacon.Execute strSQL

[KenBZim]

Many Thanks