Solved

Queries Failing To Return Data From JET Database

Posted on 2014-03-11
8
257 Views
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.

Thanks,
Matt
0
Comment
Question by:matthewgreen
[X]
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
8 Comments
 
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?
0
 
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.

Jim.
0
 

Author Comment

by:matthewgreen
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.

Thanks,
Steve
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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.

Jim.
0
 

Author Comment

by:matthewgreen
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.

Thanks,
Steve
0
 
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.

Jim.
0
 

Accepted Solution

by:
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).
0
 

Author Closing Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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