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

[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

 

[Sam Wilson]

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

Sam

 

[NateBuckley]

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?

 

[Sam Wilson]

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

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?

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