I am assuming you are using the Sage 50 Canadian version not the US version.  I believe Pervasive is the database for the US version so that information is not accurate for us.

Your issue is that Sage 50 installs the 32-bit version of the ODBC drivers and you are using the 64-bit version of Excel.  Even MS recommends that if you are working with connections between software that the MS Office product should be installed as 32-bit.

However, if you downloaded the MySQL ODBC 64 bit drivers of the same MySQL ODBC version of your 32-bit driver, and you use it for your connection, it might work for you.

Hi Richard,

I am wondering how does this work if the Sage files are located on a local server. or Does it ever work?

It seems like I cannot even add the DSN on my ODBC Data Source Administrator. (I have the Windows admin account, but not the Sage 50 admin account)

In addition, do you know if there are any courses about Sage data interpretation that I can take?

Alvin

Hi Richard,

I am wondering how does this work if the Sage files are located on a local server. or Does it ever work?

It seems like I cannot even add the DSN on my ODBC Data Source Administrator. (I have the Windows admin account, but not the Sage 50 admin account)

In addition, do you know if there are any courses about Sage data interpretation that I can take?

Alvin

When you open a Sage 50 file on any computer, the 32-bit DSN's for the file will be updated with the proper Server name and Port.

Open the Sage 50 file, then go to the 32-bit ODBC DSN called Sage 50: Last Opened.  If your 64-bit DSN does not have the same settings, change them and see if it works.

With the proper security setup in the Sage 50 company file, you should be able to access the modules setup for that user, it doesn't have to always be the Sage 50 admin account that is used in external programs.

[email protected] said:

In addition, do you know if there are any courses about Sage data interpretation that I can take?

I've thought about putting one together many times over the years.  However, most people are only looking for info on certain parts of the program, so a complete database review would be overkill.

Some use the datarel.pdf in the SDK to help sort out the tables necessary and linking between the tables while others post what they have and what they want in messages on this forum and they can get some help that way.

Looks like it is not working for me in my office.

Are your 64-bit ODBC DSN settings the same as the image above?

Did you allow Third-Party products access to the database using the username Alvin?

Is the username and password in Sage 50 file security the same case as you entered into the 32-bit test above?  eg. is the Alvin username in Sage 50 at the bottom right-hand corner of the Home Screen displaying Alvin or alvin?

Is your password entered above less than or equal to 7 characters?

1. The settings are the same for both 32-bit and 64-bit ODBC. Both test on 32-bit and 64-bit ODBC failed with same message.

2. I am not sure if the Sage has been setup for third-party products access. How do I set it up? I may get my manager to set it if I know how to set up.

3. usename and password in Sage 50 are exactly the same as I entered into the 32-bit test.

4. password entered is less than 7 characters.

Based on your answers, I would guess the problem is #2.  When logged into the Sage 50 file as Alvin, under Setup, Change Password, you can tell if you have Third-Party rights.  It needs to be set at Read-only as a minimum.  When reporting, you should not need Read/Write.  If it says No Data Access then outside of Sage 50, you get .....

Why did it work before and now it doesn't?  What changed?

Hi Richard,

You are right. I will get my manager to set it as Read-only, and try it again. I will update my result when I tried it.

Hi Richard,

It works after my access changed. Now is time for me to figure out the VBA parts. Thank you so much.