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!

Parents
  • 0

    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. 

  • 0 in reply to RandyW

    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.

  • 0 in reply to EricP

    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.

Reply
  • 0 in reply to EricP

    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.

Children
No Data