Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Queries Failing To Return Data From JET Database

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
matthewgreen
Asked:
matthewgreen
  • 4
  • 3
1 Solution
 
Jim P.Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President Online Computer Svcs, WNY IncCommented:
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
 
matthewgreenAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Jim Dettman (Microsoft MVP/ EE MVE)President Online Computer Svcs, WNY IncCommented:
<<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
 
matthewgreenAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President Online Computer Svcs, WNY IncCommented:
<<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
 
matthewgreenAuthor Commented:
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
 
matthewgreenAuthor Commented:
There has been no solution to the issue and I want to close off the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now