updatable query

  • Thread starter Chris R Simmons
  • Start date
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)
 
R

Randy Harris

Chris, this is just a guess, but I don't see any place where you define the
connection string, conJWE.

Chris R Simmons said:
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)
Click to expand...
 
S

Scott McDaniel

Can you add new records from the Query Builder in Access? If not, they your
query isn't built correctly and Access (actually Jet) isn't allowing you to
add records. Make sure are required fields are either (a) being populated
with appropriate data or (b) you have Defaults set at the table level. Make
sure to include all necessary Foreign key fields in your query.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Chris R Simmons said:
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)
Click to expand...
 
C

Chris Simmons

Randy,
Thank you for your response. I forgot to include that
in my posting, but yes I did define the connection string
in a "General" Module since I am using it on several forms.

'Public declares
Public conJWE As ADODB.Connection
Public cmdJWE As ADODB.Command
Public rsJWE As ADODB.Recordset

Public Sub main()

'Create the connection & recordset
Set conJWE = New ADODB.Connection
Set rsJWE = New ADODB.Recordset

'open the connection
conJWE.ConnectionString
= "Provider=Microsoft.jet.oledb.4.0;" & _
"Persist Security InFo=False;data source=" &
App.Path & _
"\DataBase\JWExam.mdb; Mode = read"
conJWE.Open

frmMain.Show
End Sub

It appears I set the mode for "Read". Thus I was not able
to update to the database. Thank you for your time, help
& suggestion. After setting the mode to read/write the
update works.
Chris
-----Original Message-----
Chris, this is just a guess, but I don't see any place where you define the
connection string, conJWE.
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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)
Click to expand...
Click to expand...
 
C

Chris Simmons

Scott,
Thank you for your reply. From Access Query Builder, I
was able to add records. But in my VB program, the query
I built was not including my primary key called "ID" when
I tried to update the database with a new record. Not
only that, I had another problem with my connection mode
being set to "Read" instead of "Read/Write".
Again, thank you for your time, suggestions and help
in solving my problems for me.
Chris
-----Original Message-----
Can you add new records from the Query Builder in Access? If not, they your
query isn't built correctly and Access (actually Jet) isn't allowing you to
add records. Make sure are required fields are either (a) being populated
with appropriate data or (b) you have Defaults set at the table level. Make
sure to include all necessary Foreign key fields in your query.
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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)
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

updateable query 0
updateable query 0

Top