runtime error '-214 7217900(80040e14)

[chiks34]

hi
this is the error i get when i run my program
i have put 3 asterisks at the beginning of the line which attempt to open the recordset(cRS.open "Movies,adopenStatic,adlockoptimistic)

pls help
Option Explicit
Dim cRS As ADODB.Recordset

Private Sub Form_Load()

' declare a module-level variable of type Recordset
' This type exists in the external library ADODB

Dim strConn As String
Dim Moviesrset As New ADODB.Recordset

' create an object of type ADODB.Recordset and
' make the variable cRS point to the new object
Set cRS = New ADODB.Recordset

' set the connection string
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\acp\acp\globalDVD.mdb;Persist Security Info=False;Jet OLEDBatabase Password=globalcomm"

' force the recordset object to connect directly to a table of the database
****cRS.Open "Movies", strConn, adOpenDynamic, adLockOptimistic ' update form fields with current record values
UpdateForm cRS
End Sub

Private Sub UpdateForm(pRS As ADODB.Recordset)
txtMovieID = pRS.Fields("MovieID")
txtTitle = pRS.Fields("Title")
txtStudio = pRS.Fields("Studio")
txtDirector = pRS.Fields("Director")
txtRDate = pRS.Fields("RDate")

End Sub

Private Sub ResetForm(pRS As ADODB.Recordset)
txtMovieID = ""
txtTitle = ""
txtStudio = ""
txtDirector = ""
txtRDate = ""

End Sub

Private Sub UpdateRS(pRS As ADODB.Recordset)
pRS.Fields("Title") = txtTitle
pRS.Fields("Studio") = txtStudio
pRS.Fields("Director") = txtDirector
pRS.Fields("RDate") = txtRDate
End Sub

Private Sub cmdDel_Click()
Dim intResult As Integer

If (Not cRS.BOF) And (Not cRS.EOF) Then
intResult = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If intResult = vbYes Then
cRS.Delete
cmdFirst_Click
End If
End If
End Sub

Private Sub cmdExit_Click()
cRS.Close
Set cRS = Nothing
Unload Me
End
End Sub

Private Sub cmdFirst_Click()
cRS.MoveFirst
UpdateForm cRS
End Sub

Private Sub cmdLast_Click()
cRS.MoveLast
UpdateForm cRS
End Sub

Private Sub cmdNew_Click()
If Not cRS.EOF Then
cRS.AddNew
ResetForm cRS
txtTitle.SetFocus
End If
End Sub

Private Sub cmdNext_Click()
If Not cRS.EOF Then
cRS.MoveNext
If cRS.EOF Then
cRS.MoveFirst
End If
UpdateForm cRS
End If
End Sub

Private Sub cmdPrev_Click()
If Not cRS.BOF Then
cRS.MovePrevious
If cRS.BOF Then
cRS.MoveLast
End If
UpdateForm cRS
End If
End Sub

Private Sub cmdSave_Click()
If (Not cRS.BOF) And (Not cRS.EOF) Then
UpdateRS cRS
cRS.Update
End If
End Sub

[brucevde]

Specifying the Error Description would be more useful than the Error Number. As it is, your code works fine for me.

[Hack]

Specifying the Error Description would be more useful than the Error Number. As it is, your code works fine for me.

Agree on description rather than error number. Especially since Google could not find that error number at all.

[Al42]

i have put 3 asterisks at the beginning of the line which attempt to open the recordset(cRS.open "Movies,adopenStatic,adlockoptimistic)

But is this the line that triggers the error?

[rodrix]

I got the same error.
It's a "Syntax error in UPDATE statement."

However I can't figure it out. I checked it multiple times and I am almost sure syntax is right.

This is the query:

Conn.Execute "UPDATE DB_Tours_Data SET CODE_Tours_Data='" & txtCodeName & _
"',CODE_ServiceProviders_Data='" & cmbSP & "',City='" & cmbCity & _
"',TourType='" & cmbTourType & _
"',SysDescription='" & txtSystemDescription & "',Name_ForSP='" & _
txtNameForSP & "',Language='" & txtNameForPax & "',Description_ForPax='" & _
txtDescForPax & "',MandatoryComment='" & _
txtMandatoryComment & "' WHERE Code_Tours_Data='" & _
Mid(frmData_Tours.Tag, 2) & "'"

The problem is with the field Language. If change the word in bold for NameForPax, the query works fine. So it can't be a syntax error. However when I run the same query (with Language in it) in Access instead from VB the query works fine. Could you geniuses help me solve this nightmare? I am really stuck... Any ideas are deeply apreacciated.

Thanks in advance
Cheers!
Rod

[rodrix]

SOLVED

YES!!!! Solved!

Answer: The word "Language is a reserved SQL word"
Therefore using a field with name 'Language' produces the stated "syntax" error.

For a list of SQL Reserved Keywords:
http://developer.mimer.com/validator...rved-words.tml

I hope it helps others!!!!

Rod

[rodrix]

...Wait...I almost forgot... chiks34's error is still unsolved.

Chicks did you check that you aren't using reserved words like I did?
At least we know that your error is an "Update Syntax Error"

Cheers!
Rod

[Al42]

As was pointed out in another thread,

cRS.Open "Movies", strConn, adOpenDynamic, adLockOptimistic ' update form fields with current record values

is invalid syntax.

[Deliriumxx]

another thread? What one?

I have this issue on one of my client's machines,,, but not on any other client's machines ??? Anybody knows more about this??