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.

Thank you Richard.

Yes, I am using the Canadian version.

There is ODBC 64 bit installed in my computer.

You mentioned Sage 50 installs the 32-version of the ODBC. I think it is the problem between Sage 50 and ODBC.

Since I am not a IT person, I believe I am not able to solve the issue at this point. I am not able to find any solution on the internet as well. 

Unfortunately, I can't help much at a distance as I do not have a suitable environment like your's to test with.

The basic steps to test would be to download the 64-bit 5.2.4 ansi driver from the MySQL website.  This is the same level version as the 32-bit version you currently have installed.

Install it.

Use that 64 bit ODBC Administrator you found on your computer to set up a DSN for your Sage 50 database.

Connect to it from Excel.

I can't guarantee this will work but short of reinstalling your Office as 32-bit, it is the easiest option.  It is only based on info I have read on various MySQL website forums.  I do know the Excel 32-bit version will connect ok with the ODBC drivers currently installed on your system but have never had a system setup like your's to test it with 64-bit drivers.

If you want to contact me off forum you can find my email address in my profile here on this site.  I might be able to help you out later this week.  I am not a Sage employee and this does go beyond the free help users get on this forum and would be considered part of my consulting services.  We can discuss this off forum if you wish.

Thank you. I will try.

Hi Alvin,

I was wondering if you tried the 64-bit MySQL ODBC 5.2.4a driver and if it worked for you?

I just delivered a VBA programmed spreadsheet to a client who didn't tell me they were using a 64-bit version of Office.  Once I determined there wasn't anything else wrong, we downloaded the driver and installed it, I made some programming changes to account for the new driver and the programming worked.

I remembered this thread and thought I would check to see if this worked for you using the ODBC driver directly.

Thank you

Hi Richard,

Thank you for following up my question.

I installed the 64-bit MySQL ODBC 5.2.4a driver in my company, but it did not upgrade the Sage 50 ODBC platform from 32-bit to 64-bit.

Alvin

[email protected] said:

I installed the 64-bit MySQL ODBC 5.2.4a driver in my company,

I'm confused by this.  Are you talking about your Sage 50 company file or just your business in general?

The drivers are for the computer you are operating on, not the server.  When I installed the driver this morning, the Platform column in the images you posted indicated 32/64bit instead of just 32bit.

Do you see the MySQL driver under the Drivers tab in the 64bit ODBC Administrator (second image).

The image below shows the file I downloaded this morning.  More current versions may work as well but I decided to keep with the base version released with Sage 50.

Sorry, it is a typo. I mean "installed in my computer (NOT COMPANY)". The driver I downloaded is not the one shown on your screenshot. I might downloaded the wrong one. I will try it today with the driver shown on your screenshot, and find out what happen. I will update my progress later.

Thank you.

Thank you Richard. The 64-bit MySQL ODBC 5.2.4a driver works perfectly.

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.