C
Chris R Simmons
microsoft.public.access.modulesdaovba.ado
I am building a program in Visual Basic 6 that uses ADO to access a
table in an MS Access file. A command button calls the procedure
"AddUser()". An error occurs at the line: "rsJWE.Update". I trap the error
message in the On Error GoTo Handle Errors. It reads:
"Error occurred, Because:
Operation must use an updateable query. (-2147467259)"
I am trying to follow the example in the MSDN, with the same parameter
constants adOpenKeyset, adLockOptimistic, adCmdText but getting this error
message that says I need to use an "updateable query". Below is the code
that causes this error message. I don't know why I cannot add a new record
without getting this error message. Any help is very much appreciated.
Thank you,
Chris
*******************************
Private Sub AddUser()
Dim boolFormFilled As Boolean
Dim strSelect As String
On Error GoTo HandleErrors:
Call ClearTextBoxes
Call SelectAll
rsJWE.Close
rsJWE.Open gstrSQL, conJWE, adOpenKeyset, adLockOptimistic, adCmdText
Call FillTheTextBoxes
boolFormFilled = CheckFormFilled
If boolFormFilled = True Then
'proceed with add new user
rsJWE.AddNew
rsJWE!ID = rsJWE.RecordCount
rsJWE!Name = txtName.Text
rsJWE!Email = txtEmail.Text
rsJWE!Card = txtCard.Text
rsJWE!Request = txtRequest.Text
rsJWE!Expiration = txtExpires.Text
rsJWE.Update
Else
MsgBox "Missing or blank fields to be filled in?", vbOKOnly, "Empty
Text Box"
End If
AddUser_Exit:
Exit Sub
HandleErrors:
Dim strMessage As String
Dim errDBErr As ADODB.Error
Dim lngResponse As Long
strMessage = "Error occured, Because:" & vbCr & vbCr
If conJWE.Errors.Count = 0 Then
strMessage = strMessage & Err.Description & "(" & Err.Number & ")" &
vbCr
Else
For Each errDBErr In conJWE.Errors
strMessage = strMessage & errDBErr.Description & _
"(" & errDBErr.Number & ")" _
& vbCr
Next
End If
strMessage = strMessage & vbCr & "Do you want to Retry (Yes)," & _
"Continue without Retrying (No), or Cancel this Operation?"
lngResponse = MsgBox(strMessage, vbYesNoCancel, "Database Error...")
If lngResponse = vbYes Then
Resume
ElseIf lngResponse = vbNo Then
Resume Next
Else
Resume AddUser_Exit
'''''''''' rsJWE.Close
End If
End Sub
Private Sub ClearTextBoxes()
'empty all the text box fields
txtName.Text = ""
txtEmail.Text = ""
txtCard.Text = ""
txtRequest.Text = ""
txtExpires.Text = ""
End Sub
Public Sub SelectAll()
'Set up SQL for all records
gstrSQL = "Select [ID], [Name], (e-mail address removed)
I am building a program in Visual Basic 6 that uses ADO to access a
table in an MS Access file. A command button calls the procedure
"AddUser()". An error occurs at the line: "rsJWE.Update". I trap the error
message in the On Error GoTo Handle Errors. It reads:
"Error occurred, Because:
Operation must use an updateable query. (-2147467259)"
I am trying to follow the example in the MSDN, with the same parameter
constants adOpenKeyset, adLockOptimistic, adCmdText but getting this error
message that says I need to use an "updateable query". Below is the code
that causes this error message. I don't know why I cannot add a new record
without getting this error message. Any help is very much appreciated.
Thank you,
Chris
*******************************
Private Sub AddUser()
Dim boolFormFilled As Boolean
Dim strSelect As String
On Error GoTo HandleErrors:
Call ClearTextBoxes
Call SelectAll
rsJWE.Close
rsJWE.Open gstrSQL, conJWE, adOpenKeyset, adLockOptimistic, adCmdText
Call FillTheTextBoxes
boolFormFilled = CheckFormFilled
If boolFormFilled = True Then
'proceed with add new user
rsJWE.AddNew
rsJWE!ID = rsJWE.RecordCount
rsJWE!Name = txtName.Text
rsJWE!Email = txtEmail.Text
rsJWE!Card = txtCard.Text
rsJWE!Request = txtRequest.Text
rsJWE!Expiration = txtExpires.Text
rsJWE.Update
Else
MsgBox "Missing or blank fields to be filled in?", vbOKOnly, "Empty
Text Box"
End If
AddUser_Exit:
Exit Sub
HandleErrors:
Dim strMessage As String
Dim errDBErr As ADODB.Error
Dim lngResponse As Long
strMessage = "Error occured, Because:" & vbCr & vbCr
If conJWE.Errors.Count = 0 Then
strMessage = strMessage & Err.Description & "(" & Err.Number & ")" &
vbCr
Else
For Each errDBErr In conJWE.Errors
strMessage = strMessage & errDBErr.Description & _
"(" & errDBErr.Number & ")" _
& vbCr
Next
End If
strMessage = strMessage & vbCr & "Do you want to Retry (Yes)," & _
"Continue without Retrying (No), or Cancel this Operation?"
lngResponse = MsgBox(strMessage, vbYesNoCancel, "Database Error...")
If lngResponse = vbYes Then
Resume
ElseIf lngResponse = vbNo Then
Resume Next
Else
Resume AddUser_Exit
'''''''''' rsJWE.Close
End If
End Sub
Private Sub ClearTextBoxes()
'empty all the text box fields
txtName.Text = ""
txtEmail.Text = ""
txtCard.Text = ""
txtRequest.Text = ""
txtExpires.Text = ""
End Sub
Public Sub SelectAll()
'Set up SQL for all records
gstrSQL = "Select [ID], [Name], (e-mail address removed)