Query returns different answers (Access 97)

Topic

New Reply

Access database has 3 linked odbc tables from sql server.
Other tables are resident in the db for month names/sequence numbers etcetera, nothing bigger than 100 records/5 fields

Query/query results in report format.

On my desktop the results are complete, with all expected data returned.
On colleagues desktop (same setup re:OS/application etcetera) the reports are intermittently correct/not correct.

Anyone else experienced this?

TIA
Alan
Cheshire
UK

Reply | Quote

Replies

You’ll need to fill in more details before anyone can answer. What does correct/not correct entail? Are there error or system messages? Are the reports only partially printing? Is there any indication of network difficulties on that machine?

Reply | Quote

You’ll need to fill in more details before anyone can answer. What does correct/not correct entail? Are there error or system messages? Are the reports only partially printing? Is there any indication of network difficulties on that machine?

Reply | Quote

I would compare resident tables espesially those aren’t big.

Reply | Quote

odbc table contains many records of invoices and their values. Each invoice is referred to by invoice number (primary key unique) and each record has a tax year and tax period and client number.
The local tables are months: where the tax period is related to a month number and month name, this is essentially a lookup table
and sequences: where there is a list of period numbers and years against a sequence which allows me to sort the results any way up.

I know for a given client number that I should have invoice values from November 2003 up to and including December 2004.

On the report which is run from my desktop, all of the months and values are returned i.e. Nov 2003 through to December 2004

On a colleagues desktop, sometimes the report returns all of the values, other times it returns only up to November 2004.

Late today we had the situation where he rang me and he had the report missing December 2004 whilst at the same time I then ran report and it returned all of the values.

Any suggestions?

TIA
Alan

Reply | Quote

Grasping at straws here: is it possible that people have different date settings in Windows (US date format vs UK date format)?

Reply | Quote

Is it possible that the colleague has a different Jet service pack installed? I’ve seen even weirder behavior result from that.

Reply | Quote

Hmmmm.. couple of things to go at there:-
Date – the date for the desktop is set each time user logs on, but that wouldn’t explain why the report gave full list one minute and part list the next.
Jet service pack – easy way to check?

Thanks
Alan

Reply | Quote

MSKB article How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine explains how to determine which Jet service pack has been installed.

Reply | Quote

Since you are working with Access 97, you may also have an issue with the ODBC driver for SQL Server. For one thing, you may need to know what version of SQL Server is being used. In addition, you are working with Jet 3.x with Access 97 so you may want to search for the latest version of that, as well as making sure you have all of the 97 Service Packs installed. Hans’ suggestion about checking the date settings of each PC is also a good possibility.

Reply | Quote

Latest:
Just going to check the Jet engine versions (thanks guys for the links) but message received saying:
“when I open the database and run the report it works fine. But when I try to run the report a second time, then the data comes back incomplete.
The workaround is to open the database, run the report, close the database…open the database, run the next report, close the database…etcetera”

WOW, tht’s a lotta work for someone today running reports

Alan

Reply | Quote

Can you duplicate that behavior on your PC?

Reply | Quote

So far I have been unable to repeat the behaviour on my desktop.

Results are in from Jet versions:
My desktop: 4.0.8618.0
Errant desktop: 4.0.6218.0

Does anyone know the significance of the later version and what the fixes (if any) there were?

Thanks
Alan
Cheshire
UK

Reply | Quote

The specific fixes don’t matter as much as matching the two machines. It’s one of the first things to do when you get errant behavior on a machine.

Reply | Quote

In this case, I don’t think the version of Jet 4.0 is an issue – we are apparently dealing with Access 97 which should be using Jet 3.51. Or am I confused?

Reply | Quote

I just checked the number of records in invoice table and it is currently 172,000 rows. Could this be an issue or is size unimportant?

Other than that, ODBC driver is 03.85.1025

Alan

Reply | Quote

I’m not talking about the version of Jet, Wendell, but about the Jet service pack installed.

Reply | Quote

Right, but the version numbers he is reporting are for the Jet40 DLL. With Access 97 he would be using the Jet35 DLL.

Reply | Quote

But if you have multiple versions of Jet installed and you go looking for it, you’re likely to get the highest version rather than the one appropriate to the version of Access. We still don’t know the Jet 3.x service packs installed.

Reply | Quote

msjet35.dll
3.51.2723.0

Other strange behaviours I have only just been notified of include:
from the odbc linked table, the same user has a report which displays records with all of the order numbers.
If the user sees a blank order number he goes into the application and corrects it by inputting an order number.
Rerunning the odbc table report does not appear to be updating the data to the latest inputted values.Sometimes it does, sometimes it doesn’t.
There is no pattern to whether the data is updated is after a certain period or time.
(This has an annoyance factor of 9 on a scale of 1-10)

Alan

Reply | Quote

Edited by HansV to make URL clickable – see Help 19

Searching (when time permits) revealed MS compatibility issues with versions:
http://support.microsoft.com/default.aspx?…kb;en-us;237994%5B/url%5D

On checking the drivers used for access I see that 4.00.6019.00 is currently the Access driver version.

From the MS search I see there are bugs associated with it’s use on Access 97 which require recoding. Quite what the extent of it is yet I do not know.

Alan

Reply | Quote

Every day I get a little further:
I had concerns that the queries appeared to be quite straight forward and robust, but the values seemed not to be updating.
I knew that a number of fields in the underlying ODBC linked tables had changed, but when I viewed these same table fields in Access the values had not updated. e.g. an order number was changed in the source application from C01234 to 4500001234 and the application closed. In access, opened moments later, the field value was still being displayed as C01234
From this result I presume that the jet engine is now not the concern as first thought but the ODBC driver – would this be correct?
I have run the componentchecker app as downloaded from MS and saw that there are two mismatches:
ODBCBCP.dll expected version 2000.85.1022.0 yet was currently 2000.85.1025.0
and
SQLSRV32.dll expected version 2000.85.1022.0 yet was currently 2000.85.1025.0

This was result from viewing MDAC 2.8 RTM

Does anyone have experience or advice on righting this wrong?

TIA
Alan
Cheshire
UK

(thanks Hans for setting link clickable in previous post)

Reply | Quote

Unfortunately I do have experience in this area, and it has almost always been painful. Debugging ODBC errors is both tedious and complex. But all that aside, I have several questions:

Are you still seeing correct behavior on your PC, but not on that of your workmate?
Have you seen the MSKB Article 244040 How To Have Your ODBC Jet 3.5 and 4.0 Applications Co-exist?
Would it be possible to upgrade your application to Access 2000, 2002, or 2003?
Do you have other applications on either PC that might have installed MDAC components – for example, Visual Studio, SQL Server Enterprise Manager, or Access runtime apps?

The fact that you get a small difference in MDAC components suggests that you may have two applications installed that each want to do their own thing. In addition, Access 97 is now an 8 year old product and has not been upgraded for several years makes it very problematic. For example, Access 97 knows nothing about UniCode – so if SQL Server returns data in that format, the results are likely to be totally hosed. Sorry I can’t be more encouraging.

Reply | Quote

Thanks Wendell, although as the old adage goes (I think), “It’s gonna hurt more before it gets better”.
Unfortunately the client is not of the opinion that an upgrade will be worth his while despite the recommendations and business case. If it costs money then why not just use more labour to get the job done differently. (I know, labour = money, but it’s a tunnel vision thing).
Behaviour on my desktop is now as described with tables showing previously corrected data.
I’ll be checking the conflict of applications as a new arrival to my desktop has been “Crystal reports” which muscles itself into just about every other app.
Thanks
Alan

Reply | Quote

After a brief respite from this problem (other work priorities) I am now delving into the following page which is most worrying:
http://support.microsoft.com/default.aspx?…kb;en-us;237994%5B/url%5D
On checking the Microsoft Access driver *.mdb….it begins with 4.00 (This is access 97 remember)
This article describes a lot more problems than are currently apparent on our system and I am a little worried to say the least.

Checked recent application installations on desktops and most recent one was Crystal Reports 10. I’m just popping over to crystal to see if any of their forums
have similar prob’s.

Alan

Reply | Quote

It’s a good bet that Crystal installed the latest version of the MDAC. I think the issue here may well be that Access 97 doesn’t know anything about Jet 4.0 – so it’s probably still trying to use Jet 3.5 with the latest ODBC drivers. One of the unfortunate challenges in trying to use 8 year old software.

Reply | Quote