x Want to participate? Join the group to interact
Options
  • Replies 14 replies
  • Subscribers 106 subscribers
  • Views 2960 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
44
topics
90
replies
Payroll, Time Tracking, and Job Cost
776
topics
1k
replies
Reporting and Analytics
960
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
jland47

ODBC System DNS Not Working

Posted By

We have the MAS 90 4.0 ODBC Driver installed and I've setup a system DSN connection that we link to an Access DB.  The DB is saved on the Sage server, on our network I have an excel spreadsheet that is pulling data from a query in that DB, it refreshes when opened.  We have around 15-20 desktops that I've configured the ODBC connection for and the excel file refreshes as needed.  However, 4-5 desktops throw a [DataFormat.Error]ODBC-connection to 'MAS_ACT' failed." error.

When I go to the Sage server and open the DB on those problem desktops and try to open the SO Header table I get the same error.  If I use a user DSN, SOTAMAS90 connection, I can import the table into the DB.  We have office 365 and everyone has the same versions, all users are on a domain and have the same rights.  When  I run the "test connection" in the DEBUG tab the connection succeeds, it just doesn't make the ODBC connection in the Access DB?

  • Is it failing just for a single table query only?  It could be a bad date... (data format error).

  • FormerMember
    FormerMember in reply to Kevin M

    Bad dates and $00$ (nulls) are like kryptonite to Sage ODBC.

  • in reply to Kevin M

    Yes I only have one table and one query in the DB.  So do I need to reformat the date results in the DB query?  Why would this only happen on certain computers ?

  • FormerMember
    FormerMember in reply to jland47

    This sounds more like a permissions issue than bad dates.

  • in reply to FormerMember

    If all our users are on a domain and have the same permission is there another place to look?  We have a "Group or User names:" for the Sage server titled "USERS (SAGESVR\USERS)", would I have to add these troubled computers to this group?  I don't think only certain workstations would be listed on this.

  • in reply to jland47

    Update, determined to figure this out.  One troubled PC started working, I created a new system ODBC and Access DB file on the Sage server, then created a new excel file on the network and saved.  The excel sheet now refreshes and makes all the connections on this PC.  Tried the same thing for another PC and no luck, IT says everyone has same privileges.  One thing I have noticed on this particular PC is when I go to link a table it does not have the "System" ODBC connections listed. 

  • in reply to jland47

    On the workstation is Sage 100 installed? Did you open Sage 100 at least once. How did you create the new system ODBC DSN? Is it "silent"

  • in reply to BigLouie

    Yes, opened Sage and went through the ODBC administrator, then system tab and configured the ODBC.  Went ahead and unistalled Sage then reinstalled, sytem ODBC still do not show up in Access when trying to link a table.  Created a new file DSN and the table in Sage imported correctly, so I removed that connection and tried to create a new "system DSN", now I am getting the error in the ODBC Administrator, before the connection would succeed and just wouldn't connect in Access.  Here are some screen shots of the errors and the list of Data Sources(no system sources listed)?

  • in reply to jland47

    Don't call your Silent DSN that.  Use a company specific name (like MAS_ABC).

    For the missing DSN, look at the architecture of your Office... 32-bit vs 64-bit.  Install the 64-bit driver if you need to, and set up the DSN in the matching 32/64 bit ODBC admin panel.

  • in reply to Kevin M

    I thought we couldn't use 64 because MAS is 32

*Community Hub is the new name for Sage City