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.

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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.


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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

726 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