Sage 100

 View Only

  • 1.  Attn Access Gurus - how to change databases when linking to SQL ( Premium )

    Posted 04-27-2020 15:30
    With Sage 100 Standard and SOTAMAS90, it's simple to change companies because I get the prompt to login to the ODBC.

    End-user has an MS Access database reading from linked tables

    Now we've upgraded to Premium and going through SQL driver which authenticates via AD.

    How do I get a similar prompt to change between the MAS_XXX databases? 

    I've seen that if I set one as the default within the SQL ODBC then I'm not prompted and the default data pulls in.

    I'd like to have a way to be prompted at the outset for the database? 

    Hopefully, I don't have to create multiple DSN each tied to separate MAS_xxx....

    What's the secret?

    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------


  • 2.  RE: Attn Access Gurus - how to change databases when linking to SQL ( Premium )

    Posted 04-27-2020 18:04
    It's ProvideX that's the oddball on DSNs - everything else has to be tied to a specific database in DSN creation.  What happens if you DON'T set a default in the SQL ODBC?
    And what are they doing in Access?  If it is just to speed up reports then take the queries from access and set them up as views in SQL.

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    Asheville NC
    678.273.4010 ext 5
    ------------------------------



  • 3.  RE: Attn Access Gurus - how to change databases when linking to SQL ( Premium )

    Posted 04-28-2020 09:09
    I wasn't involved in the MS Access creation ( it was all homebrew ) and this came up at the 11:59 point of an upgrade - aka "if we can't update this Access database we can't go live".

    To which I asked - what other reports do your 20 users have hidden locally on their desktops ....

    And, yeah, this also happened after they all signed off on testing.

    I'll be in including this in my new book "I've got a secret - the hidden cost of acquiring Sage orphans and why you never believe their stories about past horrible consultants"

    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------

    Original Message


  • 4.  RE: Attn Access Gurus - how to change databases when linking to SQL ( Premium )

    Posted 04-27-2020 18:20
    If it is for running Custom reports for different company data, from outside Sage 100, you might want to look at DLV. 
    https://milletsoftware.com/datalinkBenefit.htm

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 5.  RE: Attn Access Gurus - how to change databases when linking to SQL ( Premium )

    Posted 04-28-2020 09:11
    It's a custom Access that the user self-created. I'm not looking to add on any functionality for which they will then expect me to support for free. I was hoping I overlooked something that would keep me from having to create numerous DSNs each tied to a separate company.

    I'm not sure how people do this with 100 or so separate Sage companies. I guess at that point they have a developer working on coding in some type of dashboard that swaps the DSN behind-the-scenes.

    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------

    Original Message


  • 6.  RE: Attn Access Gurus - how to change databases when linking to SQL ( Premium )

    Posted 04-28-2020 10:51
    I don't know how to do this in Access, but you don't need a DSN, just the right connection string.  Here is something I have from a VBScript... which dynamically goes to the right company code's database for ODBC queries.  (sSageCompanyCode is set higher in the script).


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 7.  RE: Attn Access Gurus - how to change databases when linking to SQL ( Premium )

    Posted 04-28-2020 14:02
    Edited by Dan Burleson 04-28-2020 14:02
    In Access you can modify the ODBC connection string of Pass Through Queries to change companies (databases) and tables. You will need a bit of VBA code if you don't want to do this manually in the query properties. Here's an example of a SQL Pass Through Query connection string:

    ODBC;DSN=SQL_Premium;Description=SQL on ThinkStation;UID=MAS_Reports;PWD=XXXXXXXX;APP=Microsoft Office;DATABASE=MAS_ABC;ApplicationIntent=READONLY;;TABLE=dbo.AR_Customer​


    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Original Message


Related Content