Closing a recordset error : Operation is not allowed when...is clo

  • Thread starter Thread starter NateBuckley
  • Start date Start date
N

NateBuckley

Hello, I've been playing around with connecting to a database and inserting
information using the following code. (just pasting it in sorry if it looks
messy)

Sub ADOTest()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\temp\testdb\test.mdb;"
cn.CursorLocation = adUseClient
strSQL = "INSERT INTO tblPeople ([fldName], [fldAge]) VALUES('A Test',
'24');"

rs.Open strSQL, cn

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

I get the following error on the line that says "rs.Close"

"Operation is not allowed when the object is closed. "

It was to my understanding that I had to close everything, it inserts the
test data and continue to works if i comment out that line, but I'd like to
know why it isn't working.

Thanks for any help
 
You've got "rs.close" followed by "set rs = nothing", hence the message.
Delete the "set rs = nothing" and it should work.

Sam
 
This is me speculating, but is it because the INSERT SQL Command, inserts
something then closes the recordset, so you don't have to specify that you
wish to close it?
 
If my previous suggestion fails, try this:

Sub ADOTest()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\temp\testdb\test.mdb;"
cn.CursorLocation = adUseClient
strSQL = "INSERT INTO tblPeople ([fldName], [fldAge]) VALUES('A Test',
'24');"

set rs = cn.execute(strSQL,,1)

rs.Close

cn.Close

End Sub


NateBuckley said:
This is me speculating, but is it because the INSERT SQL Command, inserts
something then closes the recordset, so you don't have to specify that you
wish to close it?



NateBuckley said:
Hello, I've been playing around with connecting to a database and inserting
information using the following code. (just pasting it in sorry if it looks
messy)

Sub ADOTest()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\temp\testdb\test.mdb;"
cn.CursorLocation = adUseClient
strSQL = "INSERT INTO tblPeople ([fldName], [fldAge]) VALUES('A Test',
'24');"

rs.Open strSQL, cn

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

I get the following error on the line that says "rs.Close"

"Operation is not allowed when the object is closed. "

It was to my understanding that I had to close everything, it inserts the
test data and continue to works if i comment out that line, but I'd like to
know why it isn't working.

Thanks for any help
Click to expand...
Click to expand...
 
Back
Top