Recordset Error Message

[ROCK1999]

I'm continuing to get the error message:

"Method .open of object _recordset failed"

when I attempt to run the following function. all of the sql queries are corrrect in syntax and in logic....i am completely at my wits end, and any help would be appreciated:


Private Sub cmdAdd_Click()
Dim conn As adodb.Connection
Dim Rec_determineprereq As adodb.Recordset
Dim rec_determinespace As adodb.Recordset
Dim rec_register1 As adodb.Recordset
Dim rec_register2 As adodb.Recordset


Dim dbpath As String
Dim register1 As String
Dim register2 As String
'Dim sql1 As String


Dim determineprereq As String
Dim determinespace As String

'On Error GoTo ErrHandler

Set conn = New adodb.Connection
Set Rec_determineprereq = New adodb.Recordset
Set rec_determinespace = New adodb.Recordset
Set rec_register1 = New adodb.Recordset
Set rec_register2 = New adodb.Recordset

Dim schedcode As String

schedcode = txtSchedCode.Text


'assignments
dbpath = "C:\studentinformationsystem.mdb"
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbpath
'sql1 = "select schedulecode from section where schedulecode = ' & txtschedulecode.text & ' AND section.maxenroll=section.curenroll"

'DETERMINE IF THE STUDENT HAS THE PROPER PREREQUISITES
determineprereq = "SELECT [Query9].[can_take_cn], [Query9].[can_take_dc], [section].[schedulecode] From query9, [section] WHERE (([query9].[can_take_cn]=[section].[coursenumber]) And ([query9].[can_take_dc]=[section].[departmentcode]) And ([section].[schedulecode]= ' " & txtSchedCode.Text & " '))"
determinespace = "SELECT schedulecode From section WHERE section.schedulecode= '" & txtSchedCode.Text & "' And curenroll = maxenroll"


register1 = "insert into StudentClass (student_number,schedulecode,semestercode) values ( '" & id & "' , '" & txtSchedCode.Text & "', '" & currentsemester & "') "
register2 = "insert into StudentGrade '" & id & "' , (select coursenumber, departmentcode, semestercode from studentclass,section where schedulecode = '" & txtSchedCode.Text & "' AND semestercode = '" & currentsemester & "' AND student_number = '" & id & "' )"
' Determine if there is space



'rec_determinespace.Open determinespace, Conn, adOpenDynamic, adLockOptimistic = 1
rec_determinespace.Open determinespace, conn, adOpenDynamic


If rec_determinespace.Fields(0).Value > 1 Then

MsgBox " This Class Is Closed"
txtSchedCode.SetFocus
Call cmdAdd_Click

rec_determinespace.Close
End If
' If it gets here, then there is space

'Determine prerequisites


Rec_determineprereq.Open determineprereq, conn, adOpenForwardOnly

If Rec_determineprereq.RecordCount > 0 Then
MsgBox "You Do Not Have The Prerequisite For This Course"
txtSchedCode.SetFocus
Rec_determineprereq.Close
Call cmdAdd_Click
Else
'Register for the course
rec_register1.Open register1, conn, adOpenDynamic
rec_register2.Open register2, conn, adOpenDynamic
MsgBox "Class Added Successfully"
rec_register1.Close
Call cmdAdd_Click
End If
' End If



'SQL2 = "INSERT INTO StudentClass (StudentNumber,ScheduleCode,SemesterCode) VALUES ('" & id & "', '" & schedcode & "', '" & currentsemester & "')"
'txtSchedCode.SetFocus
'Rec.Open SQL2, Conn, adOpenDynamic
'MsgBox "[Class Added Successfully]"
'Rec.Close

End Sub

 

[PraveenMenon]

try this

set rec_register1=conn.execute (register1)

or

rec_register1.open register1, conn, adOpenDynamic,adcmdtext

okay?
All the Best
Praveen Menon
pcmin@rediffmail.com

 

[Doug4Now]

Hi,

I was having a similar problem and found a solution after seeing your post. I discovered that my SQL statement had a reserved word in it and the problem was solved by using [...] around the word.

Thought that I'd share the solution with you but noticed that had extensive use of [...] apart from one line that you may have overlooked which may be causing the error...

determinespace = "SELECT schedulecode From section WHERE section.schedulecode= '" & txtSchedCode.Text & "' And curenroll = maxenroll"

The word "section" may be a reserved word and you might need to have it as [section] to resolve the problem - hope this helps.

Doug.