OBDC 64 bit vs 32 wont work.

[shophoney]

Hi I've spoken to Microsoft about this issue for months now. It seems that they are saying there is a bug in Excel for several versions.

We have a retail management system using SQL and use Access to run QRY. We now use power pivot and pull the SQL statement. But when you use 64bit office and OBDC connection it wont work. We have tried it on many systems and confirmed with Microsoft that it is a bug they know of but have not solution.

Our problem is our DB and XLS files are so large they require 8gb of ram or more. But 32bit office only uses 3.2GB.

What can we do?

Do anyone have suggestions on reducing the file size of excel. Some are only 320mb but I guess when it runs it uses more overhead as it says that we need 64bit and more memory...

HELP Please

Would it improve things if I used less tables and more querys or combined many QRY to one large one, in powerpivot?

 

[RonB1111]

I ran into basically the same problem some years ago when I was using Access to run queries on our SQL database and then pulled the results of the Access queries into Excel. As the company's database grew each year I experienced slowdowns in the speed of refreshing data in Excel and some Excel files exceeded the 1 million row limit. My eventual solution to both was to eliminate Access entirely, and rewrite the queries directly in SQL as "views" and "stored procedures". Then data was pulled into Excel directly from SQL. I also added more parameters within Excel to further limit records, such as only the past couple years, or only records related to a particular customer, or whatever the user was realistically looking at. It was fairly timeconsuming re-writing everything but well worth it in my case. If you can write queries in Access, you'll be able to figure out how to write them in SQL. Microsoft SQL Management Studio has a "query builder" to help with much of it.

By the way, I originally tried to modify the queries in Access to limit records being pulled from SQL and while it worked in a lot of cases, there were a good number of key Excel reports where it didn't work or only marginally helped. I'm not a programmer by trade and all the programmers I spoke with about this problem advised me to learn how to write the queries directly in SQL and eliminate Access.

 

[Joe4]

Cross-posted here: ODBC - 32 vs 64 Bit Problem

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not spend time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).