Failed to retrieve data from the database

[Former Member]

We recently upgraded to CR 2013, and are using ACCESS as our back-end (although we have a combination of ACCESS 365, ACCESS 2010, and ACCESS 2007, we are using .mdb files because that's what Crystal Reports supports).   We have some large tables that are over a gigabyte, with over ten million rows.  Until we upgraded, we were able to use these tables successfully.

I now have a fairly simple report which contains a sales table linked to a customer table and product table.  The report groups by product class, and gives total number of products and customers, along with total sales.  When I display only the product class and description, no problem.  When I add in a "count distinct" on Product number, no problem.   When I add another "count distinct" for customer#, or a sum of sales, I get the following errors, one after the other:

Failed to retrieve data from the database

Database connector error: DAO Error Code 0xc6f

Source: DAO.Database

Description: Not enough space on temporary disk'

I am running both Crystal Reports and ACCESS on my desktop PC, and have moved all the data files there as well. I have over 700 gigabytes free space on my C:drive, so I am confident that there is plenty of space. My earlier efforts had the data files on our server, which has almost 3 terabytes of free space.

The first efforts connected the database as an ACCESS database, which is how we've always done it and which should be a no-brainer.  When that failed, I tried connecting using OLE DB (ADO).   The same problem happened - except it took longer!   I tried saving the databases in ".accdb" format, but then OLE DB wouldn't recognize them.

What can I do to make this simple report work?!

[former_member11696]

Hi Bob,

Wow now that is an MDB. Try ODBC to see if it works for you. Other wise the problem is simple, you are running out of system resources for CR to allocate the links. Filter more so it does not have to do so much filtering client side ( in CRD )

If that doesn't work I suggest moving to SQL Express, it can handle large amounts of data like this. Or export to XML and then create the report off the XML file. 10 million rows is not a problem for XML, different data engine using xml.

To use accdb files you need to configure your ODBC connection but it sounds to me like you may have Access Driver issues. In the ODBC Admin you should see this option:

For OLE DB be sure to use this driver:

and not the JET, it may be limited to 32 bit addressing.

Bottom line is you are using a PC database that CR needs to do all of the filtering and linking etc. within the Designer. If you look in the %USERS%\temp folder you will likely see 100's of temp files being created while the report is processing ( maybe ).

A quick Google search found this posting:

Access MDB: do access MDB files have an upper size limit? - Stack Overflow

Don

[Former Member]

Thank you Don.   Your answer made sense, but I couldn't find "Microsoft Office 12.0 Access Database Engine" on my list of OLEDB (ADO) choices.  I went to Microsoft's web site to download and install it, and to my surprise, it still wasn't on the list.   I restarted my PC, and still it wasn't there.   So I must have installed the wrong driver. 

I've been using .accdb files for ACCESS databases which I don't need to link with Crystal Reports with no problem; I would have though that the installation process for Office 365 would include an ODBC driver.    Can you recommend a link for downloading the correct driver?

[former_member11696]

Hi Bob,

Why not simply Google "Microsoft Office 12.0 Access Database Engine" and you should find it on MS's site. It's the first link.

Don

[Former Member]

That's exactly what I did.

[former_member11696]

http://www.microsoft.com/en-ca/download/details.aspx?id=13255

[Former Member]

Thanks Don.  I believe it is the same URL and definitely the same filename I downloaded last week and installed.   Nonetheless, this is what I get from Crystal Reports when I try to use OLE DB.  ACCESS 12 does not appear.

[former_member11696]

Hi Bob,

If that doesn't do it then possibly it is part of MS Office installer. Try doing a Custom install or change option and see if it lists ODBC drivers.

Office came installed on my Work PC so I don't know what options they had selected.

But maybe it got installed with MS SQL Server 2008 Client Tools or Visual Studio 20xx since it uses SQL Express as the Team Foundation Server... I've always seen it so I don't know what MS component actually installed it.

I found this one, may help:

Windows 2008 RC1 / ODBC Drivers : The Official Microsoft IIS Forums

And maybe this one...

https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error...

Sorry, post on MSDN and see if they can tell you specifics.

Don

[Former Member]

Thanks Don.   I'll check these links this afternoon.   I appreciate all of your efforts to help me!

Bob

[Former Member]

The second link you gave me had a few links of its own which I followed.   I'm now able to see "Microsoft Office 12.0 Access Database Engine" in the OLD DB (ADO) window.   However, when I use it, I am still unable to open a simple .mdb file.

Don, I appreciate all of your efforts.  Frankly, I'm disappointed that an SAP engineer hasn't seen this thread and figured out that I'm only trying to do a very simple task that should be a no-brainer - and they should have some sort of idea what's happening!  I have rewritten my report in ACCESS - it wasn't too hard - and will continue on my way. 

You've gone out of your way to try to help a stranger, and I appreciate it very much.

Bob Kimmelfield, Evergreen Consulting

[former_member11696]

Hi Bob,

You have to ask yourself also, "Why am I the only having a problem". Maybe your system is messed up....

Try installing on a new VM-ware Image ( or similar Virtual software ) and test again. I believe because you may have multiple versions on the same PC your Access Drivers are possibly linking to wrong dependencies.

On the VM just install one version of Office 2010 or above and VS and CR for VS and test again.

I'm the next best thing to R&D so you need to do more debugging on your side. Start with the basics, simplify...

For example does a smaller mdb work? If that doesn't work fix that part first.

Use other SQL Tools and copy the query from CR to that tool and see what happens, do you get all 10Mil rows of data or do you run out of memory?

You may want to look at your project files, you may have mixed references also.

Try a new project, create a new report and simply load it, if you can't create a new report then it's not a CR issue.

The error being returned:

Database connector error: DAO Error Code 0xc6f

Source: DAO.Database

Description: Not enough space on temporary disk'

Is simply being passed through CR to you, fix Access first.

Don

[Former Member]

Good morning Don.   You are raising some good points.  As it is - I upgraded (and I use that term loosely) from Windows 7 Home to Windows 8 Professional - about two weeks ago - and that appeared to wipe all of my software off the PC.   I then re-loaded Office 365 (previously I had both Office 365 and Office 2007), and lastly loaded Crystal Reports 2013 (Previously, I had CR 2007).  So it should be a fairly clean installation.   I have worked successfully with both CR2013 and previously CR2007 on smaller mdb files.  In fact, I can create a query on the large Sales table as long as I only have one formula using grouped sums.  It is always the second formula that causes the breakdown.  That's why this was so perplexing.

I also owe you a personal apology - I didn't realize that you are the SAP engineer whose help I was hoping for until this morning!  These forums often draw good Samaritans who are willing to share their knowledge in their spare time; I thought you were one of them.  I do appreciate having a professional looking into this issue.  Unfortunately, I have neither the time nor the expertise to dig as deeply as this may require.  So it will be back to ACCESS for me!

Bob

[former_member11696]

Hi Bob,

Yes I am, and I agree the "Windows Upgrade" is never good. I always use FDISK to wipe the drive and start over with a fresh install. I'll check with my Son who works in IT here and ask him about Windows 8.1 "upgrade" and what it actually does.

This may simply be a limitation of the DAO and JET drivers on Windows 8.1, you may want to search MSDN to see if anyone has run into this problem also.

I don't have an Access MDB that large to test with so if you can provide one for me I can test with this new info that you are now using Windows 8.1 I can give that a test also.

You won't be able to attach it to this forums so if are willing to I'll send you a link to upload it to.

Or if you have an FTP server you can make it available for me to download I can do that also, I'll send a notification so it isn't public.

And as I mentioned before, with that much data you really should use a real database like MS SQLExpress. Something that can manage that amount of data and is not a PC database.

Don

[Former Member]

Thanks for a very good offer.   Since the database is for the business of one of our clients, we have to treat it as confidential.   I spoke to my boss and he said that he would be willing to let SAP see the data only if a non-disclosure agreement was signed.

However, I believe that our long-term solution may be more about what you suggested - getting SQL-Server Express or something like that to handle our larger databases.  I'll keep you posted!

Bob

[former_member11696]

Hi Bob,

Sorry can't do that, we are not allowed to sign NDA's, you have to become a Partner of ours and then NDA's are covered under our contract.

For the heck of it I found some simple code to create a huge MDB so I'm running that now. I am just curious when the JET is going to run out of memory. Googling suggests it can handle up to a 2 Gigabit file but I doubt it, and so did those who posted the info. That would be the 32 bit limit... But we all know 1.2 gig is about all the OS can reasonably handle... So may temp files and handles are required to manage it you simply run out of system resources. MDB's do not have temp tables for handling queries like MS SQL Server does so it has to create files or allocate memory to deal with the data inProc.

But yes, please do use a real SQL database for those customers who need a huge amount of data.

Good luck

Don

[Former Member]

Good afternoon Don.   I've been directed to ask you about the procedures for becoming one of your Partners.  Please feel free to communicate with my at the e-mail which is my login-id.   If you don't have access to that, we'll find a way to communicate.

The largest ACCESS database that I'm using (there are actually several which link to each other's tables) is about 1.7 gigabytes.  As long as I'm working with ACCESS queries, it seems to work.  It's more clumsy that way - I have a series of queries that make temporary tables with various counts and subtotals, then link all of the tables for the final report.  It's less elegant than using Crystal Reports to create formulas which use Sums and Count Distincts and such tools in a single report.

[former_member11696]

Hi Bob,

Go here: Home | SAP Support Portal

At the bottom you will see a link to Contact Us.

There you will find telephone numbers and other links to becoming an OEM Partner etc.

You do know that MS SQL Server does have the ability to import Access Tables, so migrating to SQL Server will be relatively simple to do. It's then just a matter of converting your reports to SQL Server. Most of the data types should map over but you will need to verify each report...

So what you are doing in Access you can do in SQL Server.

Thanks again

Don

[Former Member]

Thank you Don.   We'll check that link out.

We will have an interesting issue if we use SQL-Server.   Our procedures perform the preliminary work in ACCESS and Crystal Reports, then create files sent to SQL-Server for the hard number-crunching, and then return the data to ACCESS and Crystal Reports for presentation.  About two years ago, my predecessor switched to an Azure (cloud) environment for SQL-Server, instead of running it on a local box.

When I looked into what is involved in linking an ACCESS table to an Azure SQL-Server database, I was unable to make any sense of the procedures.   I don't know if it's any easier with Crystal Reports. 

On the other hand, we might be able to use SQL-Server Express locally and from there link to Crystal Reports.   I haven't investigated this possibility yet.