Acessing T-SQL Results via VBA in Access

Question

I've been searching all morning, and I cannot find an answer to this. Probably because I'm not searching the right question.

It could also be that I'm doing a whole load of things wrong, so I'll run through what I'm doing and how.

We're running SQL Server 2012 Express and connecting via VBA in MS Access 2010, both of which are 64bit editions.

Firstly, I'll run over what works, here is some of my vba code:

Public Function SQL_Server_Execute(TSQL_String As String)
    ' create a new connection
    Dim Server_Connection As New ADODB.Connection
    ' open the connection
    Server_Connection.Open (connection string)
            
    ' check to see if any errors occurred
    If Server_Connection.Errors.Count <> 0 Then
        Err.Raise Server_Connection.Errors(0).Number
    End If
    ' run the provided statement string and grab the resulting record set
    Dim Returned As ADODB.Recordset
    Set Returned = Server_Connection.Execute(TSQL_String)
    ' check to see if any errors occurred
    If Server_Connection.Errors.Count <> 0 Then
        Err.Raise Server_Connection.Errors(0).Number
    End If
    ' first see if the recordset is open
    If Returned.State = adStateOpen Then
        ' check for number of records
        If Returned.EOF = 0 Then
            MsgBox "No records returned"
        Else
            MsgBox "Records returned"
        End If
        
        ' close the recordset
        Returned.Close
    Else
        MsgBox "Returned a closed recordset"
    End If
    ' close the connection if it was opened
    If Server_Connection.State = adStateOpen Then
         Server_Connection.Close
    End If
    Set Server_Connection = Nothing
End Function

Note - I've removed the validation and some of the error trapping as I'm still trying to get it all working.

Ok, this works fine for executing stored procedures, I can see in SSMS that the procedures run and do their jobs so as far as I know nothing is wrong with the connection.

The problem is with the returned recordset, it always comes back with an EOF of 0, therefore no records.

I've gone through all the help pages I can find about this and everywhere states that the .execute method returns a recordset containing the result of what was executed, this recordset will be closed if no results were returned.

What's interesting is where the code checks to see if the recordset.state is equal to adStateOpen it is resulting in TRUE, which tells me the recordset is open, but the EOF is 0, so it contains nothing.

I've tested this by passing the string "SELECT GetDate() as Time_Now", which runs without errors but returns no results.

Here're the questions I'd like to ask:

1) Am I retreiving the results in the wrong way? As far as I can see I'm doing it right.

2) Is it something in the T-SQL string I need to do differently?

3) Is it possibly something in the Server settings?

I need this to work since I don't see how a database can work when the FE cannot retreive information from the Server.

Thanks in advance.

Answer 1

Just simplify it:

    Set Returned = Server_Connection.Execute(TSQL_String)
    MsgBox Returned.Fields(0).Value
    If Not Returned.BOF And Not Returned.EOF Then
      MsgBox "Has rows"
    Else
      MsgBox "No rows"
    End If

Called with SQL_Server_Execute "SELECT @@VERSION".

Answer 2

Would've helped if I'd known beforehand that BOF and EOF were boolean types... (face-palm)

Shows how misunderstanding a tiny little detail can cause huge problems.

Thanks for the help.