Use VBA to query SQL Azure Database

Question

I'm a PC Excel developer. I use ADODB a lot. I have a workbook that allows users to enter search terms and uses those terms to query a database on SQL Azure. It works perfectly. 

I need to make a version for Mac. YIKES! A whole new world. I've read and researched and downloaded ODBC drivers and...still can't get one damned thing to download from my database. Don't get me wrong. I can use the interface to do it. I can open Microsoft Query and enter and query and run it and paste it to a sheet. All that. But I need a VBA solution.

Can someone just send sample code that will grab one record (Top 1) from my table in the Azure SQL database and return it to a sheet?

If I can do just that one thing, I'll figure out the rest. 

I have a free version of the Actual SQL Server ODBC driver. If there is away to do the above with buying anything I'd prefer it. 

Let me know if I can provide more info that will help.

tod

Answer 1

Hi TodLay

Once you have a query on a worksheet, you can programmatically have that query return any result set. Like anything else, it's easy (or not so easy) once you have an example that works.

If I understand your request, you want to be able to specify a query and then execute that query programmatically in Excel 2016 for Mac. 

As things stand, you can almost do this. There is a broken VBA command that is a blocking issue.

Here's a code example that works in Windows connecting to SQL Server. It should work generically for any ODBC connection assuming you change the Driver parameters appropriately.

'Query the database

    Sheets("FullRecordFromSS").Select

    ActiveSheet.Unprotect

    DoEvents

    Range("Table_ExternalData_1[ID]").Select

    Let stSQL1 = "SELECT * FROM libInfo.dbo.tblMain where LibUserID = " & "'" & SelectedLibUserID & "'" & ";"

    Range("A2").Select

    With ActiveWorkbook.Connections("GetRecordToUpdate").ODBCConnection

        .BackgroundQuery = True

        .CommandText = stSQL1

        .CommandType = xlCmdSql

        .Connection = _

        "ODBC;DRIVER=SQL Server;SERVER=servername,14360;UID=UserId;PWD=Password;APP=Microsoft Office 2016;WSID=credential"

        .RefreshOnFileOpen = False

        .SavePassword = True

        .SourceConnectionFile = ""

        .SourceDataFile = ""

        .ServerCredentialsMethod = xlCredentialsMethodIntegrated

        .AlwaysUseConnectionFile = False

    End With

'

'Run the query

    ActiveSheet.Unprotect

    Range("A2").Select

    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=True

End If

If you try to run this code on a Mac, it halts at the line:

    With ActiveWorkbook.Connections("GetRecordToUpdate").ODBCConnection

The code does successfully change the query in the querytable, but you can't refresh the query programmatically. This example does show the syntax for building your query. Notice how you have to use quotation marks to parse the query.

If you want to encourage Microsoft fix the broken VBA statement, please click here to vote for and discuss this topic:

https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/18814606-vba-odbc-connection

Answer 2

@ Jim - There is less value in posting a quick reply than in posting the answer to the question. If you don't know the answer, please refrain from answering, as it makes it look like you've actually answered the question, which you have not. MSDN is set up primarily to answer questions for Windows versions of Office. THIS forum is set up to answer questions about Office for Mac.

Answer 3

Hi Tod,

We suggest you post in the MSDNforum for professional help as the requirement is relevant to development/coding and SQL. Thanks for your understanding.

Best Regards,

Jim