+ Reply to Thread
Results 1 to 3 of 3

Changing the connection string with VBA

  1. #1
    Registered User
    Join Date
    12-04-2018
    Location
    Santa Rosa, CA
    MS-Off Ver
    2013
    Posts
    1

    Changing the connection string with VBA

    I have a excel workbook that creates a nice graphical reports from data it pulls in via a query from an MS ACCESS DB.

    I would like to create the ability for the end user to change the data source where they would type the connection string into a cell and have a VBA button click injected it into the Connections string.

    something like this:

    Please Login or Register  to view this content.
    Sheets(1).Range("A1") would have this:
    "DSN=MS Access Database;DBQ=\\Domain\Folder\SubFolder\Live DB\AccessDB.accdb;DefaultDir=\\Domain\Folder\SubFolder\Live DB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

    I have exhausted my online search for a solution and I'm hopeful that someone here can point me in the right direction.

    Thanks for your help

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Changing the connection string with VBA

    Try something like this. It splits the connection into its parts, then substitutes the pieces and then joins them back together.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Changing the connection string with VBA

    Had a glitch. Try this version.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ADODB connection - Changing connection path via fileDialog
    By cajand in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2015, 08:06 AM
  2. Excel External Connection Dropping Password from Connection String?
    By mar0isa in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-06-2015, 10:35 AM
  3. Changing connection string path to a dynamic path
    By Shevi in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-11-2014, 05:16 AM
  4. MS SQL Express connection string
    By Wongadob in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-10-2013, 12:42 PM
  5. Changing the URL of a web data connection with VB
    By xensure in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-23-2011, 10:27 PM
  6. Changing OLE DB connection
    By Jam22171 in forum Excel General
    Replies: 4
    Last Post: 02-17-2009, 05:45 AM
  7. [SOLVED] Changing Oracle connection from DSN to DSN-Less
    By Red in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1