ODBC connection failing

I am trying to connect to the Sage 50 database using ODBC/My SQL connection.  In MySQL Connector/ODBC, I enter Sage username and password, , I select TCP/IP Server : localhost (database is local on this computer), I leave the port to default (13540), and select database "simply" (also there by default).  When I click on Test I get: Connection Failed:[HY000] [MySQL][ODBC 5.2(a) Driver] Can't connect to MySQL server on 'localhost'(10061) - I have tried for hours to connect to the database without success. Please help, I really need to connect to the database!

  • If you open a Sage 50 file from the workstation in multi-user mode, that will create a configured ODBC connection set up on the system, which is left after you close the file.

    The connection is closed at the database end shortly after you close the company data in Sage 50.   If no-one is logged in from the Sage 50 program, then ODBC doesn't work.  (unless possibly you have logicim's keep-alive utility running)

    So, the easiest way to connect is to

    - create the ODBC connection settings by logging in, make sure you are in multi-user mode, then

    - connect to the database using a different account than the one that is logged in, and that has 'third party access' rights set for the user.  

    I log in with my own user account, then connect as 'sysadmin' because the sysadmin account will never be blocked from ODBC access.

    I hope that helps, please post back. 

    I saw your post earlier in French, I apologize that my command of French is not enough for complex ideas, so I must rely on Google translate, and of course, my French is also not good enough to check the translation answer back from Google. 

  • Thank you very much RandyW, I will try it first thing when I get to the office tomorrow morning. In your post you mentioned that if no-one is logged in, then it will not work unless I have Logicim's keep alive utility running: what I want to achieve is to connect CRM Dynamics Online to Sage 50 through a third party application called Scribe so I would like it to work whether the user is logged in or not. I googled "Logicim's keep alive utility" and it returned your own post to my previous question! So my question is how can I let CRM Dynamics access Sage Database whenever the computer is on? The ultimate goal is to let CRM pass information to SAGE 50 to create customers and invoices and ideally, return the invoice number back to CRM.  Do you think I am dreaming too much or it is something possible? I don't know what you do for a living but I would gladly hire you if you can help me set this up.

  • Redirected back to this thread?  Ha! Well at least I know there's you and Google reading my post.

    I found this thread with a link to the utility that I was referring to:

    http://sagecity.na.sage.com/support_communities/sage_50_accounting_ca/f/148/t/57178.aspx

    The link in the post is to a utility that Logicim, the makers of XLGL, used to provide as a free download.  It's 404 now, I couldn't find it on the website www.logicimtech.com  I think that the utility more or less 'pings' the MySQL database to keep it from timing out and shutting down when the Sage 50 program is closed.  The effect is the same as leaving at least one workstation user logged in.

    No, I don't think that's too much to do with Sage 50.  You may want to locate the Sage 50 SDK (it's available free) to make creating customers and invoices easier (than specifying and testing to insert the data using ODBC).  

    Invoices are stored in 4 tables, 2 for the inventory transaction header and detail (titrec and titrline), and 2 for the invoice header and detail (titlu and titluli).  Also, credit sale invoices and payment details are stored in tcustr, and tcustrdt.  Journal transactions in the current year are stored in tJourent, the prior year is in tJentLY.   (I always think of Douglas Adams when I see that last table name)   Also some statistical and 'quick display' records in tAccount, tCustomr, tinvExt, and tInvByLn will be updated, and there are also cost pools if using FIFO inventory.   And there are record counters in tNxtPIDs that have to be updated.

    And the SDK comes with a PDF showing the relationships between key fields, as well as samples in c#, c++ and VB.

  • www.logicimtech.com

    I found the reference to 'keep-alive' in another post on this forum but the link is now 404.  I poked around the website and didn't see it.

    The program basically keeps pinging the Connection Manager / MySQL database, and prevents it from shutting down automatically after everyone has logged out.

    I don't think that's too big a dream.  It's just data, anything that a human can do with a computer, another computer should be able to (except dust it).

    I'd be happy to help, especially with identifying which tables store which data, etc.  My time is somewhat limited and fragmented.  Of course, if anyone else reading this post has done something nearly like this, feel free to put your hand up too.

    I don't know your programming background, but you may be interested in the Sage 50 SDK, since that should make creating an invoice quite a bit easier.

  • Thanks again RandyW.I don't have much of a programming background but I know enough to know what I want to achieve.  Obviously, you know a hell of a lot more than I do. I am the president of a company so it is not my role to do that and as it is more complicated than just mapping a couple fields, I need to hire someone to do the job for me.  I would be happy if you would be available to work on this project as you are visibly an expert in this matter and you would probably set us up in no time but if you are not, do you have anyone knowledgeable to recommend?  Am I allowed to post my email address here so you can drop me some names?