Recordset does not open in VBA Excel

Question

I have a problem with Excel VBA recordset, it runs all other queries but this one particular query which runs in SQL Server does not open with the Recordset. Here is the code chunk:

SQL2 = "DECLARE @val VARCHAR (2000) " & vbCrLf & "DECLARE @cert VARCHAR(2000) " & vbCrLf & _
    "DECLARE @inv VARCHAR(2000) " & vbCrLf & "DECLARE @loc INT " & vbCrLf & _
    "DECLARE @res TABLE(val VARCHAR(100)) " & vbCrLf & _
    "DECLARE @Delimiter VARCHAR(2) " & vbCrLf & "SET @val = '' " & vbCrLf & _
    "SET @Delimiter = ',' " & vbCrLf & "SELECT @cert = " & vbCrLf & "CASE WHEN @cert = '' " & vbCrLf & _
    "THEN CertInvsRec " & vbCrLf & "ELSE @cert + coalesce(',' + CertInvsRec, '') " & vbCrLf & _
    "End " & vbCrLf & "FROM  ProjValuations WHERE (Proj = 'TPL-15-020') " & vbCrLf & _
    "SELECT @inv = " & vbCrLf & "CASE WHEN @inv = '' " & vbCrLf & "THEN InvsAmountRec " & vbCrLf & _
    "ELSE @inv + coalesce(',' + InvsAmountRec, '') " & vbCrLf & "End " & vbCrLf & _
    "FROM  ProjValuations WHERE (Proj = 'TPL-15-020') " & vbCrLf & "SET @val = CONCAT(@inv, ',', @cert) " & vbCrLf & _
    "IF NOT  @val = ',' " & vbCrLf & "DECLARE @Index SMALLINT, @Start SMALLINT, @DelSize SMALLINT " & vbCrLf & _
    "SET @DelSize = LEN(@Delimiter) " & vbCrLf & "WHILE LEN(@val) > 0 " & vbCrLf & "BEGIN " & vbCrLf & _
    "SET @Index = CHARINDEX(@Delimiter, @val) " & vbCrLf & "IF @Index = 0 " & vbCrLf & "BEGIN " & vbCrLf & _
    "INSERT INTO @res(val) VALUES(LTRIM(RTRIM(@val))) " & vbCrLf & "BREAK " & vbCrLf & _
    "End " & vbCrLf & "Else " & vbCrLf & "BEGIN " & vbCrLf & _
    "INSERT INTO @res (val) VALUES (LTRIM(RTRIM(SUBSTRING(@val, 1,@Index - 1)))) " & vbCrLf & _
    "SET @Start = @Index + @DelSize " & vbCrLf & _
    "SET @val = SUBSTRING(@val, @Start , LEN(@val) - @Start + 1) " & vbCrLf & "End " & vbCrLf & _
    "End " & vbCrLf & vbCrLf & "SELECT " & vbCrLf & "( " & vbCrLf & _
    "SELECT sum(InvTotExcl) FROM invnum WHERE autoindex in ( " & vbCrLf & _
    "SELECT iInvoiceID FROM _bvSalesOrdersFull WHERE (ProjectCode LIKE '%020%') AND " & vbCrLf & _
    "(StatusDescription = 'CERTIFICATE') and iInvoiceID not in (SELECT * FROM @res)) " & vbCrLf & _
    ") " & vbCrLf & " AS certAmnt, " & vbCrLf & "( " & vbCrLf & _
    "SELECT SUM(InvTotExcl) FROM _bvSalesOrdersFull WHERE (ProjectCode = 'TPL-15-020') AND " & vbCrLf & _
    "(StatusDescription = 'STANDARD') and iInvoiceID not in (SELECT * FROM @res) " & vbCrLf & ") AS invAmnt" 
If rsAmnts.State = adStateOpen Then rsAmnts.Close 
rsAmnts.Open SQL2, CN, adOpenStatic, adLockReadOnly

Any help would be highly appreciated, thanks in advance.

If it helps, If I use direct SELECT queries like e.g. SELECT * FROM things it runs, could be because of the variable declarations, so maybe should I move the query to a procedure?

Answer 1

Make sure you check regional settings, this will affect how the data is entered into the DB

Answer 2

So a solution I found that worked is by creating a procedure out of the SQL Query

/****** Object:  StoredProcedure [dbo].[valuationReports]    Script Date: 1/9/2017 17:24:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[valuationReports] 
    @proj AS VARCHAR(200)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @val VARCHAR (2000)
    DECLARE @cert VARCHAR(2000)
    DECLARE @inv VARCHAR(2000)
    DECLARE @loc INT
    DECLARE @res TABLE(val VARCHAR(100))
    DECLARE @Delimiter VARCHAR(2)
    SET @val = ''
    SET @Delimiter = ','
    SELECT @cert = coalesce(',' + CertInvsRec, '')
        FROM  ProjValuations WHERE (Proj = @proj)
    SELECT @inv = coalesce(',' + InvsAmountRec, '')
        FROM  ProjValuations WHERE (Proj = @proj)
    SET @val = CONCAT(@inv, ',', @cert)
    --select @val as 'tst'
    IF NOT  @val = ','
    BEGIN
        DECLARE @Index SMALLINT, @Start SMALLINT, @DelSize SMALLINT
        SET @DelSize = LEN(@Delimiter)
        WHILE LEN(@val) > 0
        BEGIN
            SET @Index = CHARINDEX(@Delimiter, @val)
            IF @Index = 0
                BEGIN
                    INSERT INTO @res(val) VALUES(LTRIM(RTRIM(@val)))
                    BREAK
                END
            ELSE
                BEGIN
                    INSERT INTO @res (val) VALUES (LTRIM(RTRIM(SUBSTRING(@val, 1,@Index - 1))))
                    SET @Start = @Index + @DelSize
                    SET @val = SUBSTRING(@val, @Start , LEN(@val) - @Start + 1)
                END
        END
    END
    SET @cert = ''
    SET @inv = ''
    SELECT @cert = 
        CASE WHEN @cert = ''
        THEN cast(iInvoiceID as varchar)
        ELSE @cert + coalesce(',' + cast(iInvoiceID as varchar), '')
        END
        FROM _bvSalesOrdersFull WHERE (ProjectCode = @proj) AND 
        (StatusDescription = 'CERTIFICATE') AND iInvoiceID NOT IN (SELECT * FROM @res)
    SELECT @inv = 
        CASE WHEN @inv = ''
        THEN cast(iInvoiceID as varchar)
        ELSE @inv + coalesce(',' + cast(iInvoiceID as varchar), '')
        END
        FROM  _bvSalesOrdersFull WHERE (ProjectCode = @proj) AND 
        (StatusDescription = 'STANDARD') AND iInvoiceID NOT IN (SELECT * FROM @res)

    SELECT 
    (
        SELECT ISNULL(SUM(InvTotExcl), 0) FROM invnum WHERE autoindex in (
        SELECT iInvoiceID FROM _bvSalesOrdersFull WHERE (ProjectCode = @proj) AND 
        (StatusDescription = 'CERTIFICATE') AND iInvoiceID NOT IN (SELECT * FROM @res)
        )
    ) AS 'cert',
    (
        SELECT ISNULL(SUM(InvTotExcl), 0) FROM _bvSalesOrdersFull WHERE (ProjectCode = @proj) AND 
        (StatusDescription = 'STANDARD') AND iInvoiceID NOT IN (SELECT * FROM @res)
    ) AS 'inv',
    (
        SELECT ISNULL(@cert, '')
    ) AS 'certIn',
    (
        SELECT ISNULL(@inv, '')
    ) AS 'invIn'

END

GO

And the VBA code which I used is

'Declare variables
Dim CN As Connection
Dim cmd As Command, rsAmnts As Recordset

'Initialise the variables
Set CN = New Connection
Set cmd = New Command

'Open connection
CN.Open ("connection string")

'Call procedure assigning the variable
cmd.ActiveConnection = CN
cmd.CommandText = "valuationReports"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

'The variable assigned a value
cmd(1) = "TP-15-0020"

'Set Recordset by executing the command i.e. procedure
Set rsAmnts = cmd.Execute

Hope this helps anyone out there

Answer 3

This may not be the direct answer but it won't fit in comments and may solve the issue.

You are using Connection.Open which according to this may not get you a recordset right away: -

Opens a connection to a data source.

And

When used on a client-side Connection object, the Open method doesn't actually establish a connection to the server until a Recordset is opened on the Connection object.

It could be a case of assigning it to a Recordset object exposes it as a recordset.

Whereas with Connection.Execute, according to this will return a recordset: -

Returns a Recordset Object (ADO) object reference.

And

Executes the specified query, SQL statement, stored procedure, or provider-specific text.

What I read from this is that Connection.Open will open a connection, this may not be the case of executing specific commands, but simply opening access to data. Connection.Execute explicitly mentions running queries and commands, so may accept your declarations as well as standard queries.

Quick example of execute: -

Public Sub Sample(ByVal SQL2 As String)
Dim DBConn  As ADODB.Connection
Dim RS      As ADODB.Recordset

Set DBConn = New ADODB.Connection
    DBConn.Open "Provider=SQLOLEDB;Server=[SERVER];Database=[DATABASE];User Id=[USER];Password=[PASSWORD;"
    Set RS = DBConn.Execute(SQL2)
        'RS is a recordset at this point
        RS.Close
    Set RS = Nothing
    DBConn.Close
Set DBConn = Nothing

End Sub

Answer 4

Step 1: Open the immediate window, paste your code like this and press enter on every line:

Step 2: Write this and see what you get, when you press ENTER:

Step 3: Check the result carefully. Try to take it into the SQL Server and to see the error there.