Intermittent Query Failure

[jayde]

I am working on a small Win 2000 network on a workstation running Win 2000 Professional. I have an Access 2000 frontend and a SQL Server 7 database with all of the available service packs. I use a form to locate records in the database based on Name, Organization or Postal Code. When the user enters search text and presses enter the code changes the recordset to a query that references the form field.

This had been working correctly for a long time. Recently it has been failing intermittently. Once it fails if I close and reopen the frontend it works again. After a search fails I can still go into the linked table in the frontend and fine the record manually. This behavior has occurred with both new and old records. I am essentially clueless about what would cause occasional failure. I am using standard Access Queries not PassThrough SQL Queries.

So far I have tried the following:

I have rewritten the queries referenced in the code.
Used the Link Table Manager to refresh all of my links.
Used Repair and Compress Utility
Exported everything in the database into an empty database to try remove any corruption that the utility failed to fix.
Deleted and rebuilt the SQL indexes

Any suggestions would be welcome. I am stumped and the client is not happy.

Thanks,



\ \ | | / /
[---O-O---]
\ (_) /
{{{-}}}

--OooO--OooO---

Jayde Donnelly

 

[thornmastr]

My first inclination would be to address the possibility of corruption but you seem to have already approached the problem from this perspective with no real results.

Have you downloaded the latest SR for A2K, if not, perhaps you might want to try that. Conversely, if you have downloaded the latest SR, did the problem begin immediately after you downloaded the SR. if yes, check the list of “fix” changers. Probably, one of them is biting you.

You also might want to look closely at the queries being passed to SQL SERVER and how closely “SQL standard” compliant they really are.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[jayde]

Do you think that I might cure my problem if I made the offending queries passthrough queries? My current queries are sql compliant, but Access 2k does add a lot of superfluous punctuation in queries it generates. The queries are strait-forward select queries with a where clause which is referring to the text the user has entered into a text box on a form. The code is fired when they hit tab or enter and the afterupdate event is activated

Thanks,

Jayde

 

[thornmastr]

Jayde,

Since you are using Sql Server 7, I assume you are using ODBC as your connection layer rather than ACTIVE Data objects. The only reason I would suggest doing one query as a passthrough is a as a test to eliminate the possibility of it mattering at all.

I would not do them all. However, if one of them now works properly as a passthrough, change another one. It then becomes the usual fix which you know cannot be the reason but for some reason it seems to solve the problem.

My feeling is this cannot be the reason, but eliminate the possibility of it working.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com