VBA SQL returning null values

Question

I'm having a problem running a SQL statement on vba excel, the last 3 Columns are for storing numbers separated by commas, but when executed on excel vba it doesn't display these values, while on other Database programs it does

the code is the following

Sub obtainColMachs()    
    Dim cnn1 As New ADODB.Connection
    Dim mrs As New ADODB.Recordset, sqry As String

    Set cnn1 = New ADODB.Connection
        cnn1.ConnectionString = "driver=SQL Server Native Client 11.0;" & _
        "server=server;uid=user;pwd=password;database=DB;"
        cnn1.ConnectionTimeout = 3
        cnn1.Open

    sqry = "select top 1 m.* from recipe r left join RecipeGroup rg on r.RecipeGroupID = rg.RecipeGroupID " & _
    "left join Matricula m on tonalidad_ID = ParentGroupID *100 + rg.RecipeGroupID where Substring(ColorNo,3,3) = 'ZG5'"

    mrs.Open sqry, cnn1
    Range("A26").CopyFromRecordset mrs

    mrs.Close
    cnn1.Close
End Sub

It should return:

Data returned on Database program

But it only returns:

Data returned in VBA Excel

Lee Mac · Accepted Answer · 2018-11-22 23:04:07Z

1

You only seem to be returning fields from your Matricula table; perhaps the SQL should be:

select top 1 m.*, r.*, rg.* from

Or better yet, a list of the fields that are actually required and with reference to the table from which they originate, e.g.:

select top 1 m.Tonalidad, m.Field2, r.Field3, rg.Field4 etc... from

answered Nov 22, 2018 at 23:04

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA SQL returning null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related