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. 

Reply
  • 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. 

Children
  • 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.

  • 0 in reply to EricP

    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.

  • 0 in reply to RandyW

    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?

  • 0 in reply to EricP

    Eric,

    A quick and dirty way of getting the ODBC connection to work is to open the Sage 50 file in Sage 50, then connect to the Sage 50 Last Opened driver.  Your issue is probably that you had the wrong port.  Last opened should have the correct port for the file all the time, unless your computer opens a second file at the same time and you only wish to connect to the first one.  If so, use the ODBC driver with the prefix of the name of your file.  It should have the correct port but you must open the Sage 50 file before you connect with the third-party program.

    You can also confirm the port used by the file from Help, About Sage 50, Support Info.

    This of course is all manual so if you are looking for a more automated setup, maybe Randy can help you out more.

  • 0 in reply to Richard S. Ridings

    Thanks Richard,

    In a side email back-and-forth, with a few screen shots, I lucked out and spotted the login obstacle.  

    It appeared they were putting an 8 character password into the MySQL ODBC client.   I should have remembered this problem, from the 'Access Denied' error message, indicating that it's connecting fine, only rejected on Security.

    Now, the next apparent obstacle is that the application they want to use is 64 bit.  There is a 64 bit ODBC client at

    dev.mysql.com/.../5.2.html  I'm not sure which exact version(s) will work with the Sage 50 version of MySQL

    I'm not sure if it's possible to build any 64 bit component that relies on the 32 bit SDK dlls (or any 32 bit dlls).   I would think not, so the interaction with the Sage 50 data has to be at the tcp/ip level, or as a data import / export.

  • 0 in reply to RandyW

    Randy

    When I was teaching my Crystal course years ago, I had about 10 people over two weeks tell me they had major ODBC error messages trying to connect Crystal to Simply.  I asked if I could talk with them in the corner.  So I took each person out of earshot of everyone else and told them sysadmin was not a good password for the sysadmin userid.  I was right 9 times.  Seven of the people did actually think they were entering the password in Simply correctly each time the speakers dinged at them, so they knew they had it correct.

    Eric did not indicate part of the message was "using password = Yes" or something like that so I thought it wasn't getting to the password stage.  Glad you got it figured out.

    For the 64 bit issue, I have no clue.  I can't test 64 bit stuff here.  If need be, maybe Tony could help if they want to hire him.  I think he has played with the drivers a bit.  I always use internal programming and do not run through the ODBC drivers for my work, unless I am working on Crystal.  I just don't know if it would play well with a 64 bit app in general.  They might be able to create a dll to do the work for them as 32 bit, but that stuff is above my pay grade right now.

  • 0 in reply to Richard S. Ridings

    Hello everyone, this is really interesting.

    I am trying to achieve something somewhat similar to EricP: link a Sage table to Excel, so that a refresh in Excel would make the Excel table update with most recent data.

    The good news:

    I can link the table in Excel by going through the Excel menu: Data/Get External Data/From Other Sources/From Microsoft Query (and so on).

    However:

    A) When Sage is opened prior to Excel refresh

        The refresh works, but :

        - I get the error message “[Microsoft] [ODBC Manager] Data source name not found and no default driver specified”.

        - After I click “ok”, a “select data source” window, with a “machine data source” tab pops-up. I select my database from the list.

        - Once the database is selected, I get the MySQL Connector/ODBC window. All infos are already there. I click ok.

        And bingo, the table is refreshed.

    B) When Sage is NOT opened prior to Excel refresh

        - It just does NOT refresh. I get the error message “[MySQL] [ODBC 5.2(a) Driver] Can’t connect to MySQL server on ‘localhost’ (10061)

    Could someone please help situations “A” and “B” ? That would be much appreciated!

    My specs:

        - 50 Accountant Edition

        - Windows 8

        - Microsoft Excel 2010

        - The 2 default MYSQL Connector/ODBC are: 3.51 (version 3.51.28) and 5.2(a) (version 5.2.4)

        - I am working locally

  • 0 in reply to Adok
    The ODBC connection on the workstation is rewritten each time you connect. I've only been able to consistently connect with files that were created before the database was converted from 3.51 to 5.2awhatever. It seems to lose the security settings in Access and Excel every time. So you have to re-connect and login.

    Sage uses VBA code to connect, you can view by opening one of the 'Microsoft Office' reports, and pressing alt-F11.

    In the second case, the Connection Manager doesn't start until Sage 50 does. Since the Connection Manager starts the database, you can't connect, because there's no database to connect to... yet.
  • 0 in reply to RandyW
    Thanks a lot RandyW for your helpful answer.

    In Excel, I pressed the “record macro” button while performing a connection between an Excel worksheet and a Sage table. I could then press Alt+F11 to visualize the automatically generated VBA code.

    This leads to another question:

    Situation A: CREATE an INITIAL connection between Excel and a Sage table:

    Ok, suppose that we could deal with the fact that a few “additional connection windows” might pop up before the Sage table gets finally connected to Excel. Since creating an initial connection happens only once, getting through these windows only 1 time would not be a major issue (even if not ideal).

    Situation B: REFRESH an ALREADY existing connection between Excel and a Sage table:

    Since a table is likely to be refreshed many times in Excel, overcoming this issue would be awesome. Would you think a possible solution would be to create a customized VBA code that would at the same time refresh the data AND provide the extra info required by those “additional connection windows” that pop up every time a refresh is executed ?(for instance: DSN, SERVER, UID, DATABASE, PORT, …), If so, in order to refresh, we would then choose to execute this customized VBA code instead of pressing the built-in Excel “refresh” button.

    Situation C: same as Situation B, but when Sage program is closed.

    I assumed in Situation B that Sage program is open in background. Could a customized VBA code also be a solution for a flawless refresh, even if Sage program is closed ?

    If you could provide help and share your thoughts regarding situation B and C (or at least situation B), that would again really be appreciated. Thanks !
  • 0 in reply to Adok
    Regarding B, I would suggest looking at the connection method used in the VBA code for the Microsoft Office reports that are shipped with Sage 50.

    I would also suggest that you have a look at the demo of XLGL from Logicim. Collecting data from multiple Sage 50 databases can be easily set up, with passwords in the formula string.

    Regarding C, part of what the Connection Manager does, is to check for a valid license before setting up the database connection. Possibly the sample program (from the c# folder from the SDK) will open a 'cold' Sage 50 database, but it may do so without kicking off the Connection Manager and then the MySQL database.

    So there might be nothing listening, for Excel to connect to. I haven't tried this, but I will try to, when time permits.