Results 1 to 6 of 6
  1. #1
    jbcom41 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3

    OBDC Save connection String

    Hi Folks



    Total newbie here so be kind, I have just finished migrating our data from our access backend to MSSQL express.


    I want to connect the front end but want to save the connection string within the ODBC, so I can distribute the frontend without having to recreate the ODBC on every workstation – is this possible?


    Or is there a better way to do this?


    Any information would be greatly appreciated.


    Technical details
    Access O365
    MSSQL 2019 Express


    Many thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    something like:
    usage: ConnectTbls "AcmeSvr","Employees","","",true

    Code:
    sub ConnectTbls(ByVal pvSvr, ByVal pvDb, ByVal pvUser, ByVal pvPass, ByVal pbIsTrusted As Boolean)
    vDriver = "Driver={SQL Server}"
    if pbIsTrusted then
        sConn = vDriver & ";Server=" & pvSvr & ";Database=" & pvDb & ";Trusted_connection=Yes;ConnectionTimeOut = 5"
    else
       sConn = vDriver & ";Server=" & pvSvr & ";Database=" & pvDb & "; Uid=" & pvUser & ";Pwd=" & pvPass & ";ConnectionTimeOut = 5"
    end if
      'cycle thru and connect all tbls needed
    Dim db As Database
    Dim tdf As TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        tdf.connect = sConn
        Debug.Print tdf.Name; "="; tdf.Connect
    Next
    end sub

  3. #3
    jbcom41 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3
    Hi ranman, thanks for infor - where would you save this info to? Do you save it within the OBDC? Totally new to this stuff so please excuse my ignorance??

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The ODBC connections are stored with the table definitions, provided nothing changed between your version and the distributed version, you shouldn't need to do anything.
    If you hover over a table in the navigation window you will see a portion of the saved string.

    However, each user will need the same ODBC driver you used to create the connection.
    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 ↓↓

  5. #5
    jbcom41 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3
    Hi

    Is there anyway to store the connection string within the OBDC file to stop this box appearing everytime you open the access front end up?

    Click image for larger version. 

Name:	Connection.JPG 
Views:	9 
Size:	22.2 KB 
ID:	43898

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If your users are on the domain and the have access rights to the server you can tick the use trusted connection box.
    It will then connect with their domain credentials.

    The alternative is to save the password when you create/recreate the table link.
    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. I'm struggling with a connection string.
    By Coxroach1 in forum Access
    Replies: 3
    Last Post: 11-20-2018, 06:18 AM
  2. Connection String to Postgre
    By martingaleh in forum Programming
    Replies: 3
    Last Post: 04-13-2016, 05:28 PM
  3. Remote Desktop Connection Broker connection string
    By Philosophaie in forum Access
    Replies: 1
    Last Post: 09-14-2015, 03:51 PM
  4. Connection string error
    By Philosophaie in forum Access
    Replies: 3
    Last Post: 09-08-2015, 10:26 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