ADODB Recordset Closed, why?

[RobertMidd]

This is driving me nuts!!! and no amount of Google searching can find me a resolution.

In my Access form I can open a SQL database ok and pass a query and it gets executed ok (I know because it creates a new table and puts data into the table).

After I want to check for a record in the SQL database by selecting the first record in the table but the ADO recordset remain closed after I have opened it and I cannot check any result. The same select works ok in SSMS so I know a record is being returned.

So here is my code.

Private Sub DoSQLConnection(SQLText As String)
        Dim ObjConn As Object
        Dim StrConn As String
        Dim ObjRS As Object
        
        On Error GoTo NoConnect
        
        Set ObjConn = New ADODB.Connection
        Set ObjRS = New ADODB.Recordset
        With ObjConn
            StrConn = "Provider=SQLOLEDB;Data Source=" & MySQLServer & ";"
            If MyAuthCon = 0 Then
               StrConn = StrConn & "Trusted_Connection=Yes"
            Else
               StrConn = StrConn & "User id=" & MyUsercode & ";Password=" & MyPassword & ";"
            End If
            
            .ConnectionString = StrConn
            .Open
            Set ObjRS = .Execute(SQLText)
            ObjRS.Open
            MsgBox (ObjRS.State)
        End With
        
        Exit Sub
NoConnect:
        
        MyConnError = 2
        Screen.MousePointer = 1
        Exit Sub
End Sub

The MSGBox command with ObjRS returns 0 meaning it is closed and if I try to reference a property such as RecordCount then I get an error and the watch indicates <Operation is not allowed when the object is closed.>

Anyone any idea(s)?

Thanks, Robert.

 

[Gasman]

Just guessing here, but do you get a connection?
What does MyUserCode and MyPassword hold? Are they global?

What hapens if you walk through the code with F8?

 

[RobertMidd]

The usercode and password are global for now but I am not using those anyway as it has a trusted connection.

The connection is fine and the query is executed ok (as I indicated above). The issue is the recordset is closed as seen in the debugger watch window.

I just want to check ObjRS.RecordCount to know the SQL commands ran and created the new table and placed at least one record in the table. I can see from SSMS this happens but cannot get this back into VBA.

So through the same code, I first send a page load of SQL commands that drops the table if it exists, creates the table, perform queries to get data from other tables in the database and based on the results will insert multiple records in the new table and all this works fine through the same VBA code as I do not need to check the recordset.

When I then pass another query through the same code to get me the first record from the new table then this is when I need to check ObjRS.RecordCount to check that at least one record is returned (So I know the first stage of drop, create and insert completed ok) and this is where it fails with the error that the object is closed.

 

[RobertMidd]

Fixed it, changed the driver to ODBC instead of OLEDB and it now works

 

[Gasman]

Fixed it, changed the driver to ODBC instead of OLEDB and it now works

So you never had a connection really?

 

[RobertMidd]

So you never had a connection really?

Yes I did, as I have said in my previous posts.

 

[Deleted member 147267]

This isn't my area of expertise, but FWIW, I think what Execute returns is a closed recordset if the sql is not a select query (i.e. closed if Execute runs an action query). I wouldn't suggest this if I could see what's in SQLText and it was a Select query, so sorry if I'm way off base.

 

[RobertMidd]

This isn't my area of expertise, but FWIW, I think what Execute returns is a closed recordset if the sql is not a select query (i.e. closed if Execute runs an action query). I wouldn't suggest this if I could see what's in SQLText and it was a Select query, so sorry if I'm way off base.

Thanks, yes I had read that elsewhere but this was a simple SELECT statement.

It would appear that the SQLOLEDB driver gets the connection, performs the query but never returns an open recordset whereas the ODBC driver does the same but returns the open recordset. Not sure why as I thought OLEDB would be better as it is more performant.

 

[Galaxiom]

It would appear that the SQLOLEDB driver gets the connection, performs the query but never returns an open recordset whereas the ODBC driver does the same but returns the open recordset.

I'm guessing the Open Method of the recordset is expecting to pull a new recordset and wants either a Command as the Source or a connection as the ActiveConnection.

I would have expected an error though.

What happens if you remove the Open line?

I have used ADODB connections to return recordsets many times but always with the fully configured Open Method of the Recordset or the Execute method of a Command rather than the Connection.

BTW The Execute method of a Connection returns a ReadOnly ForwardOnly recordset.

Not sure why as I thought OLEDB would be better as it is more performant.

The advantage is that the recordset can be disconnected (and reconnected after editing) so doesn't tie up the server. I doubt that it makes much difference when the connection is kept open.