Queries Failing To Return Data From JET Database

Posted on 2014-03-11
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 58
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.

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 58
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 58
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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