I would love post the original code except that the "code" that initiated
the first post is an fact retreival via ODBC through a set of services
configured from an Oracle DB. I'm working with Oracle to try and figure what
the issue is - it is definately an error from teh ODBC driver though.
My current hypothesis is that when I took a copy of the DB it was in the
process of being accessed and as such the copy includes some locked records.
And the error from the first post relates to the locks. I'm still working on
this idea...
I managed to get the "Run-time error Could not update; currently locked by
another session on this machine" when updating records one at time. The code
for the one at a time processing is below, I basically have a form that
dosplays the data with a button, behind the button is the code at the end of
this post.
Some investigation actions:
1) I click the button
2) Some updates occur, but my debug highlights an error. eg:
Error encountered processing 1621.Attempting to process:
UPDATE Part1 SET Part1.[Description] = 'X' WHERE
(((Part1.Reference)='22.3.1') AND ((Part1.ReferenceSeq)=1621));
3) I check the data and no update has occured
4) I cut and paste the command into a query window and execute and the
update succeeds (!)
5) I re-click the button, but debug highlights an error eg:
Error encountered processing 1633.Attempting to process:
UPDATE Part1 SET Part1.[Description] = 'X' WHERE
(((Part1.Reference)='22.3.1') AND ((Part1.ReferenceSeq)=1633));
6) So I run the the following command in a query window (to update all
rows):
UPDATE Part1 SET Part1.[Description] = 'X'
This successfully updates the table!
I'm not sure if this relates to my original issue or whether this is a
red-herring caused by soem dodgy VB implementation.
Questions:
*Any idea why/how the locks occur? Clearly I am not an access programmer.
Ideally I would write a single query to do the work I am attempting to do
procedurally. I want a query to update the description column which may
contain arbitrary text, say "This is some arbitrary text" and convert this
to "XXXXXXXXXXXXXXXXXXXXXXXXX". Does Access have any standard functions to
do this?
Hopefully one of us learns something new :o)
Thanks again in advance.
Mat.
Private Sub Command0_Click()
Dim sql_string As String 'declares string variable
Dim last_rec As Integer 'declares last record variable
Dim current_desc As String
Dim currReference As String
Dim currReferenceSeq As String
Dim length_of_string As Integer
Dim n As Integer
Dim m As Integer
On Error GoTo ShowErrorTrap
DoCmd.SetWarnings (False) 'turns off automatic access table change alerts
DoCmd.GoToRecord , , acLast
last_rec = Me.ReferenceSeq 'find the id of the last record
DoCmd.GoToRecord , , acFirst ' goes to first record
For m = 1 To Me.Recordset.RecordCount
length_of_string = Len(Me.Description)
'If length_of_string > 950 Then length_of_string = 950
Debug.Print length_of_string
currReference = Me.Reference
currReferenceSeq = Me.ReferenceSeq
current_desc = "X"
For n = 0 To length_of_string - 2
' current_desc = current_desc & "X"
Next n
'generates sql_query
sql_string = "UPDATE Part1 SET Part1.[Description] = '" & current_desc &
"' WHERE (((Part1.Reference)='" & currReference & "') AND
((Part1.ReferenceSeq)=" & currReferenceSeq & "));"
Debug.Print sql_string
DoCmd.RunSQL (sql_string) 'execute query
DoCmd.GoToRecord , , acNext 'goto next record
Next m
Me.Requery
DoCmd.SetWarnings (True) 'turns back on off automatic access table change
alerts
ShowErrorTrap:
Debug.Print "Error encountered processing " & currReferenceSeq &
".Attempting to process: "
Debug.Print sql_string
End Sub
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
99.44% likelihood is that your code is screwed. Why not post it all so
we can figure it out ... or in the 0.56 % case we can learn something
new.
