VBA - working with data videos | Excel VBA Part 56.2 - Get Data from SQL Server with ADO Recordsets

Hi Andrew, 

Awesome video and thank you. 

I think there are some bugs with the CopyFromRecordset property when using MySQL drivers. 

To be more specific, when I use this property then some of the fetched data come in a truncated version, say instead of Consumer Staples it is fetched as Consumer.

Having searched the web I found a property of the Recordset object as per below:

rs.CursorLocation = adUseClient

Indeed this addition provides me with a solution, but I quite do not get the reason why even when I read info in the link below. May I kindly ask you to elaborate on what this property does and its current value "adUseClient"?

https://www.w3schools.com/asp/prop_rs_cursorlocation.asp

Thank you

Hello Andrew, for some reason my code didn't work so I had to record it step by step. Here how it looks

Sub CostDataSQL()

    Dim i As Long
   
    Application.CutCopyMode = False
    Test.Range("C2").CurrentRegion.Clear

    With ActiveWorkbook.Connections("DATABASE").OLEDBConnection

        .BackgroundQuery = False
        .CommandType = xlCmdSql
        .connection = _
        "OLEDB;Provider=SQLOLEDB.1;Password=XXX;Persist Security Info=True;User ID=XXX;Data Source=MYSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DATABASE"

        .CommandText = Array( _
        "SELECT * FROM REPORT.dbo.PROJECTS as w WHERE w.""Project Number"" " & _
        "like '" & Lists.Range("C4").Value & "%' or w.""Project Number"" like '" & Lists.Range("C3").Value & "%' or w.""Project Number"" not like '" & Lists.Range("C9").Value & "%'" _
        )

        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False

    End With

    With ActiveWorkbook.Connections("DATABASE")

        .Name = "DATABASE"
        .Description = ""

    End With

    Application.CutCopyMode = False

    With Test.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("DATABASE"), Destination:=Test.Range("$C$1")).TableObject

        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "Table_DATABASE_Query"
        .Refresh

    End With

End Sub

My questions would be:

1. Not sure what the difference is between OLEDB and ADODB, does it matter?

2. I have a VERY long SQL statement, so I tried to break it in lines and it didn't work (I have more than 25 lines). Is there a way for me to break it by variables? I tried it by looking at these websites, but it doesn't work for me

https://stackoverflow.com/questions/...-continuations
https://www.anysitesolutions.com/vba...continuations/
 

Thank you so much.

Hello Andrew, I have an issue with my code, I'm trying to follow your code suggestion I appreciate if you can please help me.  I'm getting Run-Time error '-2147217900 (80040e14)'

[Microsoft] [ODBC SQL Server Driver] [SQL Server] An expression of non-boolean type specified in a context where a condition is expected near "number".

Ideally, my SQL contains SELECT within SELECT statement, i.e. several databases joined with "LEFT Outer join" with input parameters "WHERE project number is like 'P-10500%' or 'P-10600%'

But I tried to construct a code so that at least it can read the database. In Connection string should I put Schema name or I need to list database names if it is more than one database?  How can I construct a VBA code for it?

Here's my start code:

Sub GetDataFromSQLServer()

            Dim ws As Worksheet
            Dim f As ADODB.Field

            Dim i As Integer
            Dim myservername As String
            Dim mydatabase As String
            Dim myuserid As String
            Dim mypasswd As String
            Dim cn As ADODB.connection
            Dim rs As ADODB.Recordset 

          Set cn = New ADODB.connection   

          cn.ConnectionString = " Provider=MSDASQL.1;Persist Security Info=True;DRIVER=SQL Server; DATABASE: REPORT;SERVER=MYSERVER;UID=ALBERTAN;PWD=PW;APP=Microsoft Office 2016;WSID=XXX"

        cn.Open 

        Set rs = New ADODB.Recordset

        rs.ActiveConnection = cn
        rs.Source = "SELECT * FROM (SELECT * FROM REPORT 2 as a Left Outer Join Report 3 as b on a.costcode = b.costcode w WHERE w.Job Number like 'p-10500%' OR ‘P-10600%’”

            rs.CursorType = adOpenForwardOnly
            rs.LockType = adLockReadOnly

            rs.Open 

            Set ws = Worksheets.Add
            ws.Select

           For Each f In ResultSet.Fields

                i = i + 1
                ws.Cells(1, i).Value = f.Name

            Next f  

            Range("A2").CopyFromRecordset ResultSet 
            Range("A1").CurrentRegion.WrapText = False
            Range("A1").CurrentRegion.EntireColumn.AutoFit  

            rs.Close
            cn.Close  

End Sub