Closing a recordset error : Operation is not allowed when...is clo
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 |
Closing a recordset error : Operation is not allowed when...is clo
You've got "rs.close" followed by "set rs = nothing", hence the message.
Delete the "set rs = nothing" and it should work. Sam "NateBuckley" wrote: 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 |
Closing a recordset error : Operation is not allowed when...is clo
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" wrote: 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 |
Closing a recordset error : Operation is not allowed when...is
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" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 03:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com