Re: ADODB.Recordset: Operation is not allowed when the object is closed

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 11/30/04 

Date: Tue, 30 Nov 2004 10:08:22 -0500

William Robertson wrote:
> Hi there
>
> I get the above message when trying to write to a SQL 2000 database.
> Can anyone perhpas shed some light on this for me?
>
> In theory I am tryng to create a generic subroutine which I can
> simply pass my INSERT/UPDATE/DELETE statements to, and then have it
> executed. The following subroutine gets called for each server that I
> am running through. The error above is returned on the line
> "objRecordSet2.Close"
>
> My code excerpt is as follows:
> Sub AccessDB()
> ' Set the database connection information
> sServer = "test"
> sLogin = "logon"
> sPwd = "pwd"
>
> ' Create the ADO Connection and Recordset objects.
> Set objConnection2 = CreateObject( "ADODB.Connection" )
> Set objRecordSet2 = CreateObject( "ADODB.Recordset" )
>
> ' Set the connection string, open the connection and execute the
> statement objConnection2.ConnectionString = "PROVIDER=SQLOLEDB" & _
> ";SERVER=" & sServer & _
> ";UID=" & sLogin & _
> ";PWD=" & sPwd & _
> ";DATABASE=ServerSupport"
> WScript.Echo & SQLString
> objConnection2.open
> objRecordSet2.Open SQLString, objConnection2
>
> objRecordSet2.Close
> objConnection2.Close
> End Sub

If your sql string is an insert/update/delete statement, then it will not
return a recordset. DON'T USE A RECORDSET TO EXECUTE A SQL STATEMENT THAT
DOES NOT RETURN RECORDS.

objConnection2.open
objConnection2.Execute SQLString,,129
objConnection2.Close: Set objConnection2 = Nothing

((the 129 tells ADO that you are executing text string containing a sql
statement that does not return records, which prevents ADO from
instantiating a recordset object implicitly. For further explanation, look
up CommandOptionEnum and ExecuteOptionEnum at msdn.microsoft.com/library)

Bob Barrows 

-- 
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Relevant Pages

  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • Re: UPDATE query in Access 2003 raising error
    ... The only reason I questioned the recordset is that you could conceivably ... the SQL looks fine to me. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: very high cpu/ram usage: many Excel/SQL Server ADO return-trips
    ... what the endpoint of all your loops is. ... There may be a pure SQL ... > module and an ADO connection. ... though within the loop that creates a recordset I also give ...
    (microsoft.public.excel.programming)
  • SQL Server 2000 (Application) Role accessed through ADO.NET
    ... users from connecting to SQL Server through other MSOffice ... The database role claims to answer my ... I establish the connection to the database ... I execute sp_setapprole stored procedure (providing the ...
    (microsoft.public.sqlserver.security)
  • Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT I
    ... This statement causes ADO to create a recordset to receive the results ... of the query being executed and assign that recordset to your qryAddProj ... You should use the ExecuteOptions argument of the Execute ... string containing a sql statement to be executed. ...
    (microsoft.public.data.ado)
Loading