Results 1 to 8 of 8
  1. #1
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74

    Prompt user for DSN

    Hi all, I have multiple clients who use quickbooks and their quickbooks files are stored locally on my computer. In my Access database, I want the user to be able to select the correct quickbooks DSN and store the full connection string to be able to run a variety of standardized queries on different quickbooks files. I thought I could force the DSN selector prompt using a dummy passthrough query and save the DSN that they choose, but it doesn't actually save the DSN and therefore I cannot access the info using querydef. It just comes up as "ODBC;" without the full connection string. Any suggestions would be appreciated.

    Code:
    Dim qd As QueryDef
            Set qd = CurrentDb.QueryDefs("GetDSNQ")
    'open the dummy query
            DoCmd.OpenQuery "GetDSNQ"
    'opening this query prompts the system dialog to select the DSN before running the query
            DoCmd.Close acQuery, "GetDSNQ", acSaveYes
    'I was hoping this would grab the selected connection string, but it does not save in the query
            Me.MyClientDSN = qd.Connect
    End Sub


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So when user opens a passthrough query and Access can't find connection, users are prompted to select from a dialog? So you need some way to prompt this dialog independent of a passthrough query?

    Would QuickBooks allow DSN-less connection, review http://accessmvp.com/DJSteele/DSNLessLinks.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Yes, I need some way to prompt that window in order to capture the connection string. I don’t mind using a funny pass through query because it prompts the dialog just fine and it works except that I don’t know how to extract the connection string for the DSN that they choose. I looked at the link and it seems like there needs to be input from the user as to the database and such, so I think I’d still need some way to give them a list of options from the computer’s data sources. I’ll look further into it in case I’m misreading it, though.
    Last edited by mcucino; 12-03-2018 at 08:20 AM. Reason: Spelling-autocorrect

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am sure the db name could be hard coded or somehow derived from db info. How is app customized for each user? What naming convention is used for QuickBooks files?

    The server name would be your computer?
    Last edited by June7; 12-04-2018 at 02:55 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Excellent point! Deriving the info is a possibility if we ensure consistent naming conventions. I'd still like the pop up though if possible, because it also allows them to set up a new data source. An ideal scenario would be to use the file selector to find the quickbooks file and have access VBA create the data source to ensure consistency. And it would be the most user friendly option. Do you know if that's possible? I'll start researching

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Clients can have multiple QuickBooks files? They have discretion on naming and folder location?

    Yes, system FileDialog will allow users to find/navigate to file/folder and code can grab the full path from that dialog. Common topic, many examples.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    I'm perfectly fluent with using the file selector, it's just a matter of translating that into a usable, named data source for pass through queries. We're a financial consulting firm for businesses, so we have hundreds of different clients (each with their own QB file). As an example:
    A. here's the QB file location: "E:\Client Data\ClientName\Quickbooks File\Client Name, LLC.QBW"
    B. and using QODBC and the computer's data source prompt, it turns it into a named data source called "Client Name QB Data"
    C. Said data source can now be named in the ODBC connection like so:
    Code:
    ODBC;DSN=Client Name QB data;SERVER=QODBC;OptimizerDBFolder=%AppData%\QODBC Driver for QuickBooks\Optimizer;OptimizerAllowDirtyReads=N;SyncFromOtherTables=N;ForceSDKVersion=<default SDK>;LicenseYear=2017
    But I need to get from point A to point C programatically.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, over my head.

    Maybe be a hidden system table somewhere or a registry setting.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-12-2018, 09:06 AM
  2. Replies: 6
    Last Post: 07-15-2017, 11:57 PM
  3. Prompt user during first login
    By nishant.dhruve in forum Access
    Replies: 1
    Last Post: 12-27-2016, 04:34 PM
  4. Pop Up window to prompt user!
    By Kevo in forum Forms
    Replies: 6
    Last Post: 06-14-2012, 02:25 PM
  5. User Prompt for multiple Files
    By ratherbgolfing in forum Programming
    Replies: 1
    Last Post: 01-31-2012, 03:38 PM

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