Results 1 to 2 of 2

[VBA] using variable in SQL connection string

  1. #1
    trevor is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    Orlando, FL, USA
    Posts
    10

    Question [VBA] using variable in SQL connection string

    Heya,

    In VBA does anyone know how to use a variable in a SQL connection string?

    We're aiming for active directory integrated login for SQL that works great, just hoping to prefill thei username field.

    Code:
    Sub connect()    Dim cdb As DAO.Database, qdf As DAO.QueryDef
        Set cdb = CurrentDb
        Set qdf = cdb.CreateQueryDef("")
        qdf.connect = "ODBC;" & _
                "Driver={ODBC Driver 17 for SQL Server};" & _
                "Server=*****.database.windows.net;" & _
                "Database=BDC;" & _
                "Trusted_Connection=no;" & _
                "Authentication=ActiveDirectoryInteractive;" & _
                "UId=& ""> #"" & TempVars!Email2 & ""#"";"
        qdf.ReturnsRecords = False
        qdf.Execute dbFailOnError
        Set qdf = Nothing
        Set cdb = Nothing
    End Sub
    Thanks everyone!

  2. #2
    Minty is offline Hacker
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    961
    Create the whole connection string into a string variable beforehand, using whatever variables you want in the concatenation.

    Then simply use the strVar as the connection string.

    Code:
    Dim strConn as String
    
    strConn = "ODBC;" & _            
                "Driver={ODBC Driver 17 for SQL Server};" & _
                "Server=*****.database.windows.net;" & _
                "Database=BDC;" & _
                "Trusted_Connection=no;" & _
                "Authentication=ActiveDirectoryInteractive;" & _
                "UId=& ""> #"" & TempVars!Email2 & ""#"";"
    
     qdf.connect = strConn
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Connection String to Postgre
    By martingaleh in forum Programming
    Replies: 3
    Last Post: 04-13-2016, 04:28 PM
  2. Remote Desktop Connection Broker connection string
    By Philosophaie in forum Access
    Replies: 1
    Last Post: 09-14-2015, 02:51 PM
  3. Connection string error
    By Philosophaie in forum Access
    Replies: 3
    Last Post: 09-08-2015, 09:26 PM
  4. Replies: 3
    Last Post: 05-28-2013, 11:53 AM
  5. Connection String
    By cradaa in forum Access
    Replies: 4
    Last Post: 05-24-2011, 06:28 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums