Results 1 to 2 of 2
  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 VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    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, 05:28 PM
  2. Remote Desktop Connection Broker connection string
    By Philosophaie in forum Access
    Replies: 1
    Last Post: 09-14-2015, 03:51 PM
  3. Connection string error
    By Philosophaie in forum Access
    Replies: 3
    Last Post: 09-08-2015, 10:26 PM
  4. Replies: 3
    Last Post: 05-28-2013, 12:53 PM
  5. Connection String
    By cradaa in forum Access
    Replies: 4
    Last Post: 05-24-2011, 07: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
  •  
Other Forums: Microsoft Office Forums