Invalid SQL Run-time error '-2147217900 (80040e14)'

[WDSnews]

When I open my form with the code below, I get this error...

----
Run-time error '-2147217900 (80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.
---

The error occurs after the second OPEN statement. The first Open statement
opens a table named "School". The second Open statement should open a table
named "School Year". I don't understand why the second one fails. Thanks
for your help and commentary...

---
Private Sub Form_Open(Cancel As Integer)
Dim cnnSchool As ADODB.Connection
Dim rstSchool As ADODB.Recordset, rstYear As ADODB.Recordset

Set cnnSchool = Application.CurrentProject.Connection
Set rstSchool = New ADODB.Recordset
Set rstYear = New ADODB.Recordset

' set School Year
rstSchool.Open Source:="School", ActiveConnection:=cnnSchool, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic
rstSchool.MoveFirst
txtSchoolYear = rstSchool.Fields("Current School Year").Value

' set the Calendar Year
'ERROR HAPPENS ON THE NEXT LINE...
rstYear.Open Source:="School Year", ActiveConnection:=cnnSchool, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic
rstYear.MoveFirst
rstYear.Find "ID = " & rstSchool.Fields("Current School Year").Value
txtCalYear = rstYear.Fields("Calendar Year").Value

'close the recordset and disassociate object variable from object
rstSchool.Close
Set rstSchool = Nothing
rstYear.Close
Set rstYear = Nothing

End Sub

 

[WDSnews]

YES! that fixed it. Thank you.

Hi -

Just a guess (I don't use ADO code), but I suspect the problem may be that
you have a blank in the table name. Try putting the table name in square
vbrackets []:

rstYear.Open Source:="[School Year]", ActiveConnection:=cnnSchool, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic

HTH

John

When I open my form with the code below, I get this error...

----
Run-time error '-2147217900 (80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.
---

The error occurs after the second OPEN statement. The first Open
statement
opens a table named "School". The second Open statement should open a
table
named "School Year". I don't understand why the second one fails. Thanks
for your help and commentary...

---
Private Sub Form_Open(Cancel As Integer)
Dim cnnSchool As ADODB.Connection
Dim rstSchool As ADODB.Recordset, rstYear As ADODB.Recordset

Set cnnSchool = Application.CurrentProject.Connection
Set rstSchool = New ADODB.Recordset
Set rstYear = New ADODB.Recordset

' set School Year
rstSchool.Open Source:="School", ActiveConnection:=cnnSchool, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic
rstSchool.MoveFirst
txtSchoolYear = rstSchool.Fields("Current School Year").Value

' set the Calendar Year
'ERROR HAPPENS ON THE NEXT LINE...
rstYear.Open Source:="School Year", ActiveConnection:=cnnSchool, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic
rstYear.MoveFirst
rstYear.Find "ID = " & rstSchool.Fields("Current School Year").Value
txtCalYear = rstYear.Fields("Calendar Year").Value

'close the recordset and disassociate object variable from object
rstSchool.Close
Set rstSchool = Nothing
rstYear.Close
Set rstYear = Nothing

End Sub

--
John Goddard
E-Mail: jrgoddard AT cyberus DOT ca

Message posted via AccessMonster.com

 

[shakyplenty]

<script type="text/javascript"><!--
google_ad_client = "pub-1192207855870154";
/* 728x90, created 7/29/09 */
google_ad_slot = "7268091733";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>