Operation is not allowed when the object is closed

James123456 · Jan 25th 2017

Hi All,

Am trying to connect to SQL and get data into Excel. My connection string is as follow but it keep falling over on line with error Operation is not allowed when the object is closed:

Code

ThisWorkbook.Worksheets("Test").Range("myRange1").CopyFromRecordset rsPubs

The code is as follow:

Code

Public Sub copDataOve(Query As String)
 On Error GoTo 0
 
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
     
    Dim strConn As String
    Dim strServer As String
    Dim strDB As String


         
    strConn = "PROVIDER=SQLOLEDB;"
    strServer = Trim(ThisWorkbook.Worksheets("Settings").Range("D6").Text) 
    strDB = Trim(ThisWorkbook.Worksheets("Settings").Range("D11").Text)
     
    strConn = strConn & "DATA SOURCE=" & strServer & ";INITIAL CATALOG=" & strDB & ";"
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
     
    cnPubs.Open strConn
   
    rsPubs.Open Query, cnPubs, adOpenStatic
    ThisWorkbook.Worksheets("Test").Range("myRange1").CopyFromRecordset rsPubs


    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing
    
End Sub

Display More

Any help would be so much apprecaited. I have already tried SET NOCOUNT ON in my SQLscript but to no success.

**Kenneth Hobson** · Jan 25th 2017

Re: Operation is not allowed when the object is closed

First, confirm that you have the connection string setup right. I guess you mean SQL Server but I am not sure. https://www.connectionstrings.com/sql-server/

Once you know what it should look like, do this before Open.

Code

Debug.Print StrConn

After a Run, check the value in VBE's Immediate window. Click View and then Immediate Window if not visible.

After that, you may need to play with the Open options like adOpenStatic by adding more or not using adOpenStatic.

Here is an old example that might give you an idea or two.

Code

Sub ADO()
'   the Microsoft ActiveX Data Objects 2.x Library
    Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer, Row As Integer, s As String
    
    'On Error GoTo EndNow
'   Set window and calc off to speed updates
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035


'   Database information
    'DBFullName = "C:\myfiles\vbabook\Names.mdb"
    DBFullName = "u:\Material\ADO\NWind.mdb"
    
'   Open the connection
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Cnct
    
'   Create RecordSet
    Set Recordset = New ADODB.Recordset
'   Next two lines critical to work in QPro properly.  Excel does not need them.
    Recordset.CursorType = adOpenKeyset
    Recordset.LockType = adLockOptimistic
      
    With Recordset
'       Filter
        Src = "SELECT * FROM Orders "
        'Src = Src & "and CategoryID = 30"
        Recordset.Open Source:=Src, ActiveConnection:=Connection


'   Cells.Clear 'Used in Excel to clear a sheet
'       Write the field names
        'For Col = 0 To .Fields.Count - 1
           'Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name   'Excel method
        'Next


If .RecordCount < 1 Then GoTo EndNow 'Query found no matching records
'       Write the recordset by Excel method
        'Range("A1").Offset(1, 0).CopyFromRecordset Recordset
'Add a new record (not pushed to the database until Update)
    MsgBox CStr(.RecordCount), vbInformation, "#Records"
    .AddNew
    Recordset("ShipName") = [Name!A2]
    Recordset("ShipAddress") = [Address!B6]
    Recordset("ShipCity") = Worksheets("City").Range("C3")
    .Update
     MsgBox CStr(.RecordCount), vbInformation, "#Records"
  
  End With
  
EndNow:
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing
'   Reset window and calculation
End Sub

Display More

James123456 · Jan 26th 2017

Re: Operation is not allowed when the object is closed

Hi Kenneth,

Thanks for your reply. It is SQL Server. However i have tried all that. The connection is open but it thinks the recordset is closed which i clearly open with

Code

rsPubs.Open Query, cnPubs, adOpenStatic

.

**Kenneth Hobson** · Jan 26th 2017

Re: Operation is not allowed when the object is closed

See if this helps. The connection string has some issues.

Code

'http://stackoverflow.com/questions/18339253/excel-2003-vba-write-to-sql-server-2000-ado
Sub Main()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ssql As String
Dim scn$


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


'scn  = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=""DB on Server"";" & _
    "Data Source=""Server Name"" & _
    "User ID=Form;Password=nope;"


ssql = "sql statment that writes a record to a table using info in the excel form," & _
" writen in vb that has worked through an odbc"


With cn
    .CursorLocation = adUseClient
    .Open scn
    .CommandTimeout = 0
    Set rs = .Execute(ssql)
End With
    On Error GoTo badsql:


rs.Open ssql, cn, adOpenStatic, adLockOptimistic
On Error GoTo badsql:


rs.Close
cn.Close


Set rs = Nothing
Set cn = Nothing




MsgBox ("This record was updated in the database and documented for" & _
" processing. Please close this workbook.")




badsql:
    MsgBox ("This record was not processed please resubmit.")
    Exit Sub
End Sub

Display More

James123456 · Jan 27th 2017

Re: Operation is not allowed when the object is closed

Thanks for the suggestion again. Am getting the same error.

I went through another approach of creating stored procedure from the sql script, passing the parameters from VBA to it, creating tables in SQL and then populating the recordset from it.

Thanks again.

Operation is not allowed when the object is closed

Participate now!

Share