I can't establish a connection via ODBC

SOLVED

Hello Colleagues,

I have a problem connecting to Sage 50 from ODBC, when I try to connect to the database from excel or another program it does not validate my credentials.

I have done all the processes in the documentation, I enabled the permissions so that I could generate reports, I made sure the driver was compatible with the version of Sage, I made sure I don't know the AV.

I have done everything, however, when I connect to the generic system companies that do not have a password it lets me see the data but when I connect to the companies that I have created protected it does not validate the default user of Crystal reports or my user, or anything like that.

Has anyone gone through this?

Translated with www.DeepL.com/Translator (free version)

  • +1
    verified answer

    Access to the database directly through ODBC requires several things

    1) Sage 50 must be open in Sage 50 - this starts the database so it can be accessed.

    2) Proper username and password - both case sensitive (see username at bottom of Sage 50 Home Screen for case to be used), passwords are a maximum of 7 characters - eg. if you think your password is password, then enter passwor.

    3) Username must have Third-Party Rights set up in the Sage 50 security to at least read-only (see image below - user Rick can read data from some tables but no the payroll tables for example).

    4) Must use the ODBC DSN that points to the correct database.  Sage 50 sets this up for you when it opens, so look for Sage 50: xxxx where xxxx is the first characters of your company file name. eg. you open the company file ABCBooks.sai then it will say Sage 50: ABCBooks.sai

    5) The DSN does not have username and password stored so it must be entered when prompted.  Crystal will prompt but I don't remember if a direct ODBC connection will prompt for it unless it is during the setup of the connection and SQL, I haven't used it in years.

  • +1 in reply to Richard S. Ridings
    verified answer

    Thank you very much Richard, the problem was because I was using an 8 character password and it was being reduced to 7 characters so when I typed my 8 character password it did not validate the ODBC connection.

    I now have access to the information.

  • 0 in reply to syntaxter

    I am sorry syntaxter, I just realised that you had posted in the Sage 50 US Crystal Reports forum.  The image I posted is from the Sage 50 Canadian version and I would expect some of the other information I posted is not accurate for the US version either.  They are not the same programs with different names, they are completely different programs and different databases as well.  I would think the DSN might be set up differently as well.

    I am glad some of it helped though and I apologise if I have cause any confusion for you or others who are reading.