Sage 100

 View Only

  • 1.  2019 Premium Version - Opening Access Views and Tables in Excel

    Posted 06-23-2020 02:35
    Client in on 2019 Premium Version.  I created an ODBC System DSN to use Excel to extract data, which works fine.  I used the MAS_Reports SQL login when creating the System DSN.  My client has created Access tables and views using the same ODBC System DSN and this works fine.  When opening Excel, going to Data, Get Data, From Database, choosing the Access database, the below ODBC error occurs.  So, a prompt does not come up to enter the ODBC System DSN credentials. @Steve Iwanowski, @Alnoor Cassim any insight?


     ​​

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------


  • 2.  RE: 2019 Premium Version - Opening Access Views and Tables in Excel

    Posted 06-23-2020 03:20
    Edited by Dan Burleson 06-23-2020 03:21
    I've never gotten SOTAMAS90 to work with the newer Access format. Did you try this with a ".mdb" type Access database?

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



  • 3.  RE: 2019 Premium Version - Opening Access Views and Tables in Excel

    Posted 06-23-2020 09:29
    No, that has not been tried yet.

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------

    Original Message


  • 4.  RE: 2019 Premium Version - Opening Access Views and Tables in Excel

    Posted 06-23-2020 08:22
    That error mentions DataFormat, so I wonder if there's a type mismatch of some kind?  Try reducing the columns or changing them all to Text (Edit Query, Change the DataType to Text) to see if the error goes away.  Or instead of using Access as the source for Excel, can you go straight to SQL / Sage?

    ------------------------------
    Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
    ------------------------------



  • 5.  RE: 2019 Premium Version - Opening Access Views and Tables in Excel

    Posted 06-23-2020 09:31
    The person set up views and tables in Access.  Neither one of us know how to create the views is SQL to use.  I would have to enlist you on that :)

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------

    Original Message


  • 6.  RE: 2019 Premium Version - Opening Access Views and Tables in Excel

    Posted 06-23-2020 10:22
    Try Get Data - From Other Sources - Microsoft Query (with a DSN to your Access database).  I often have issues going straight to databases, and find MS Query + DSN to be more reliable.

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



Related Content