DAO connection string for Access (multiple versions) and Azure SQL (Active Directory Authentication)


  • Hello,
    I've done several days of research, tried and failed with tons of things I've found in various forums, before bothering to post here. I'm sure the answer is out there, but I'm not finding it.

    I am using Access VBA to create "DSN less" passthrough queries (in code; not an existing query). I got one of the connection strings to (finally) work on my computer--but _only_ on my computer. It won't work on other computers with Access 2016, or for one colleague with Access 2010 (we were in the same IP range, which is allowed on the Azure SQL firewall, I checked).

    So, if it works on mine, why not on others? I also checked the References, and we have the same five enabled.

    Currently I have the recommended connection string for ODBC from our Azure SQL portal, and it's not working _anywhere_. :-/

    Below is my code. What am I doing wrong? Thanks for any help.

        Dim qd As DAO.QueryDef
        Dim rs As DAO.Recordset
        Dim strConnect As String
        Dim strUser As String
        Dim strPassword As String
        Set qd = CurrentDb.CreateQueryDef("")
        strUser = ""
        strPassword = "password"
        strConnect = "Driver={ODBC Driver 13 for SQL Server};" & _
            ",1433;" & _
            "Database=mydatabase;" & _
            "Uid=" & strUser & ";" & _
            "Pwd=" & strPassword & ";" & _
            "Encrypt=yes;" & _
            "TrustServerCertificate=no;" & _
            "Connection Timeout=30;" & _
        qd.Connect = strConnect
        qd.sql = sql
        qd.ReturnsRecords = True
        Set rs = qd.OpenRecordset

    marți, 10 iulie 2018 16:02

Toate mesajele