Queries Failing To Return Data From JET Database

Posted on 2014-03-11
Last Modified: 2014-04-27
Hi all.

We have started encountering a VERY strange problem on our Terminal Services 2008 server.

Our Access 2010 Applications run fine for about 4 weeks, and then all of a sudden calculated fields stop displaying their values on forms. If we reboot the server without changing anything at all, the applications all start to work again without issue.

Today the issue is larger than before. Apart from the usual issue mentioned above, Rowsource queries for combo-boxes, forms, subforms, listboxes etc. have stopped returning data when they run against a backend database table that is in an Access (JET) database!

If you execute the query yourself in Query Designer, data is returned, but through the application the exact same query returns no data.

Queries that run against SQL Server tables return data without issue.

We know that if we reboot the server the issue will go away, so it seems to be a resource issue of some kind that builds up over time.

Has anyone else ever encountered anything remotely like this, or have any ideas what may be causing the issues??

It looks like it is an issue with the JET Engine somehow because accessing data through ODBC to SQL Server works fine.

We have stopped and started the Windows Indexing service and freed up the memory and disk space it was hogging, but that has made no difference.

We never had these issues with Access 2003, and they have only started with Access 2010.

Question by:matthewgreen
  • 4
  • 3
LVL 38

Expert Comment

by:Jim P.
ID: 39922619
What is the size of the Access DB it's querying? Close to 2GB?

Is the front end an Access DB? What is it's size?
LVL 57
ID: 39922827
Certainly sounds like a resource leak.

In code, are you closing all objects you open and setting all object variables to nothing?

That more then anything cleans up a lot of things like this.   Access/VBA is not very good at garbage collection.


Author Comment

ID: 39922932
To answer the questions asked. The largest of the back end databases is 320Mb, so nowhere near 2Gb. Yes, the front end applications are in the most part Access, and the largest of those comes in at 29Mb.

Regarding the clear up of objects, then the newer applications certainly do, but I would put money on the older applications not doing so. However, the application I was concentrating on yesterday was one of the new ones. I thought just this thing yesterday, so I even tried exiting and restarting, so unless Access holds onto garbage in between activations ...

Regarding the queries not returning data, that was down to a corrupted record in one of the core tables. The fields in the table were all populated with "chinese" type characters. After I had deleted this row and recovered it from backup, the query issue was gone, so that particular issue was not related to the one we experience on a regular cycle (about 4 weeks). Further, we rebooted the server last night and as expected the issue has gone away as we knew it would.

So, the only issue is the non-display of calculated (anything using a formula of any kind in the Control Source attribute (DLookup, DSum, IIf, anything). Direct linkages to the fields works without issue.

LVL 57
ID: 39922940
<<The fields in the table were all populated with "chinese" type characters. >>

  This is probably the main cluprit   A2007 and up have issues, and there are bugs with the accdb format.

I've got a client now that gets regular corruptions like this.  We're moving them off to SQL server as quickly as possible.

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 39922984
That's a bit of a nuisance, and I bet there are no plans from Microsoft to attempt a fix of this issue!

We were planning on migrating all of the back end data over to SQL Server anyway, so maybe that project has jumped up the priority list a bit!

We also have plans for a complete rewrite of all the applications into .NET, so eventually we will take Access out of the equation entirely.

Big old lump of work, but at least it will keep me in work for a while.

LVL 57
ID: 39922997
<<That's a bit of a nuisance, and I bet there are no plans from Microsoft to attempt a fix of this issue! >>

 Highly doubtful...Microsoft's focus with Access is the new web apps, which are based off Azure (SQL Server Online) and Sharepoint.

Unless it would impact a large number of customers, I doubt you'll see a fix.

With the client I have, I'm trying to pin it down so I can report it, but even if I do, not sure they'll do anything about it.


Accepted Solution

matthewgreen earned 0 total points
ID: 40015199
It would appear that there is no solution to this and it "just is" as is the case a lot of the time with Access. We'll just have to live with it!

Thanks for all the help/suggestions, but ultimately no solution for the main issue of calculated fields stopping being displayed (the data retrieval issue was down to a corrupt record so a red herring).

Author Closing Comment

ID: 40025543
There has been no solution to the issue and I want to close off the question.

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now