x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 1 reply
  • Subscribers 75 subscribers
  • Views 2133 views
  • Users 0 members are here
Browse Forums
Announcements
78
topics
166
replies
General Discussion
5k
topics
16k
replies
Installation and Upgrades
978
topics
2k
replies
Inventory, Projects, and Departments
337
topics
1k
replies
Payables and Receivables
1k
topics
4k
replies
Payroll
1k
topics
3k
replies
Reconciliation and Banking
315
topics
992
replies
Reporting and Analytics
206
topics
397
replies
Soutien Technique - Français
625
topics
1k
replies
Year End Activities
223
topics
595
replies
donhobs

MS Query to Sage 50

Posted By

I'm trying to query a Sage 50 database ("corpname.sai") from MS Query. I'm on a laptop, so local - no other users. This database has never been on the laptop before, but I seem unable to connect to it for any of a number of reasons.

I wouldn't think it should be rocket science, but maybe I'm wrong....(I can connect with MS Query to a full SQL database on my office servers with no issue at all, so Sage 50 should be a snap. Apparently not.)

I first open corpname.sai in Sage 50 (seems to be required, having read the ODBC issues elsewhere).

If I select Data/From other sources/MS Query I get a list of ten zillion simply files (in English and French!). Whether I choose "last opened" or "corpname.sai" I get the error:

The specified DSN contains an architecture mismatch between the Driver and Application.

Couldn't read this file.

MS Query opens, with nothing available.

---

If I select Data/From other sources/data connection wizard/ODBC DSN I get the same zillion file list. Choosing either last opened or corpname.sai and entering my user/password (odbc/odbc for simplicity's sake!) I get an:

Unable to connect to data source. Reason: Access denied. (user ID/pw combo issue)

Clearly I'm missing something, but because I'm in BC and without my usual machinery, my mind is befuddled today.  :-)

Anyone?

Thanks - Don

  • 0
    Hi Don,

    This is the second time I've tried to write this. I lost the first one when I clicked to change some spelling and switched to the page from the hidden links left over from the More Content popup box. I think my last message started with something like "So that's what you get for taking a vacation" ;)

    Here it goes again. Sage 50 must be open first. SQL Server usually runs the database server 100% of the time so you can always connect to it. Sage 50 only opens the MySQL server when needed. MS Query has no idea how to open the MySQL process with a database so it can't. The fact you are opening Sage 50 first, is the solution there.

    However, if the issue is really a userid/password problem, then make sure the userid is case-correct. Don't use don if your userid is DON. Check the bottom right-hand corner of the Home screen in Sage 50. Passwords are case-sensitive as well and can only be a maximum of 7 characters. I think you already know sysadmin is always lower case.

    The third-party rights in the security of that particular userid of that particular file you are working on may not be set yet. Make sure you set it to Read/Write.

    Lastly, the DSN's you are using should have the correct information in them. I would ignore the French one's as you never run in French mode. Assuming your file is called ABC.sai, either of the following should work for you:

    Sage 50: Last opened.
    Sage 50: ABC.SAI

    Both should have the proper server (localhost if you are just one the local computer and not running from a network), and the port. You can confirm the computer name and port from Sage 50 in Help, About Sage 50..., Support Info.

    Hope this helps

*Community Hub is the new name for Sage City