Differing results for the same report when run on different pc's.

[DaveK]

I have an Access database that sits on a shared network drive. When a user
connects to it from his pc and runs a report the results are erroneous. When
the same user logs into a different pc and opens the application to run the
same report, the results are as expected. The Access application is split
between a front end with the reports, forms and queries and a back end with
the tables. I checked to make sure that the front end was linked to the
correct back end and it was.
What else could be causing differing results for the same report when run on
two different pc's?

 

[Allen Browne]

Some lines to investigate:

1. What version of Access is this?
And what service pack?

2. What version of msjet40.dll is on each machine?
(Typically found in windows\system32.)

3. Are different records being returned?
Or is it just a different calculated total?

4. Is the report based on a query?
Does this query return the different numbers of records also (or is the
difference only in the report)?

5. Does the query have any parameters?
Does declaring the parameters make any difference?

6. Is there any difference with Regional Settings between the 2 machines?
(In the Windows Control Panel.)

7. Does each machine have its own separate copy of the front end?

 

[DaveK]

In reponse to your questions below, here's what I found:
1. Both pc's are running MS Access 2003 with SP2.
2. Both pc's have msjet40.dll ver 4.0.9025.0
3. Different records are being returned on the pc in question but when the
same user logs into a different pc and runs the report the results are
correct.
4. The report is based on a query and the query produces the same results
as the report.
5. Yes the query's parameters come from data entered on a form where the
report is launched from.
6. The Regional settings are the same between the two pc's we ran the
report from.
7. No, the front end is on a network share drive that is accessed by either
using a desktop shortcut or navigating directly to it through Windows
Explorer.

 

[Allen Browne]

#5: Parameters
Open the query in design view. Explicitly declare the parameters, by
choosing Parameters on the Query menu. Enter them one per row, e.g.:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
[Forms].[Form1].[cboClientID] Long Integer
[Forms].[Form1].[txtCity] Text

If there are any calculated query fields that have criteria applied,
explicitly typecast those fields as well. More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

#7: A separate front end is highly desirable.
Everyone accessing the same front end is probably the worst of all
configuations: no isloation between users, slower connection to data, and
everything coming over the network. (I can't say it will solve this specific
issue, though it might have a bearing.)

If you are still stuck, take a look at the records that are returned on one
computer and not the other. Anything peculiar about those
records--particularly the fields involved i criteria, sorting, or joining.

It is just remotely possible that a corrupt index is contributing to the
difference, but I don't think that's likely as the 2 machines with the same
versions of msaccess and msjet40 should be using the indexes in the same
way. Still, it would not hurt to compact/repair the back end.

 

[DaveK]

Allen,
Thanks for the info. I actually did open the query and explicitly enter the
criteria, then ran the query and got the same results. The one thing I
haven't tried is the compact and repair on the back end - I'll have to do
that tomorrow since someone left the office without logging off the
application.
Most of our Access applications run on a network share drive because of the
number of users who use them and the fact that they're spread through various
departments in the building. I know this isn't the recommended way of doing
things - it just happens that this is what I inherited from a previous
employee.

Thanks again for your help.

#5: Parameters
Open the query in design view. Explicitly declare the parameters, by
choosing Parameters on the Query menu. Enter them one per row, e.g.:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
[Forms].[Form1].[cboClientID] Long Integer
[Forms].[Form1].[txtCity] Text

If there are any calculated query fields that have criteria applied,
explicitly typecast those fields as well. More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

#7: A separate front end is highly desirable.
Everyone accessing the same front end is probably the worst of all
configuations: no isloation between users, slower connection to data, and
everything coming over the network. (I can't say it will solve this specific
issue, though it might have a bearing.)

If you are still stuck, take a look at the records that are returned on one
computer and not the other. Anything peculiar about those
records--particularly the fields involved i criteria, sorting, or joining.

It is just remotely possible that a corrupt index is contributing to the
difference, but I don't think that's likely as the 2 machines with the same
versions of msaccess and msjet40 should be using the indexes in the same
way. Still, it would not hurt to compact/repair the back end.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In reponse to your questions below, here's what I found:
1. Both pc's are running MS Access 2003 with SP2.
2. Both pc's have msjet40.dll ver 4.0.9025.0
3. Different records are being returned on the pc in question but when
the
same user logs into a different pc and runs the report the results are
correct.
4. The report is based on a query and the query produces the same results
as the report.
5. Yes the query's parameters come from data entered on a form where the
report is launched from.
6. The Regional settings are the same between the two pc's we ran the
report from.
7. No, the front end is on a network share drive that is accessed by
either
using a desktop shortcut or navigating directly to it through Windows
Explorer.

 

[Allen Browne]

If necessary, you can put a copy of the front end into each user's workspace
on the server. At least each user is opening a separate instance that way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for the info. I actually did open the query and explicitly enter
the
criteria, then ran the query and got the same results. The one thing I
haven't tried is the compact and repair on the back end - I'll have to do
that tomorrow since someone left the office without logging off the
application.
Most of our Access applications run on a network share drive because of
the
number of users who use them and the fact that they're spread through
various
departments in the building. I know this isn't the recommended way of
doing
things - it just happens that this is what I inherited from a previous
employee.

Thanks again for your help.

#5: Parameters
Open the query in design view. Explicitly declare the parameters, by
choosing Parameters on the Query menu. Enter them one per row, e.g.:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
[Forms].[Form1].[cboClientID] Long Integer
[Forms].[Form1].[txtCity] Text

If there are any calculated query fields that have criteria applied,
explicitly typecast those fields as well. More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

#7: A separate front end is highly desirable.
Everyone accessing the same front end is probably the worst of all
configuations: no isloation between users, slower connection to data, and
everything coming over the network. (I can't say it will solve this
specific
issue, though it might have a bearing.)

If you are still stuck, take a look at the records that are returned on
one
computer and not the other. Anything peculiar about those
records--particularly the fields involved i criteria, sorting, or
joining.

It is just remotely possible that a corrupt index is contributing to the
difference, but I don't think that's likely as the 2 machines with the
same
versions of msaccess and msjet40 should be using the indexes in the same
way. Still, it would not hurt to compact/repair the back end.

In reponse to your questions below, here's what I found:
1. Both pc's are running MS Access 2003 with SP2.
2. Both pc's have msjet40.dll ver 4.0.9025.0
3. Different records are being returned on the pc in question but when
the
same user logs into a different pc and runs the report the results are
correct.
4. The report is based on a query and the query produces the same
results
as the report.
5. Yes the query's parameters come from data entered on a form where
the
report is launched from.
6. The Regional settings are the same between the two pc's we ran the
report from.
7. No, the front end is on a network share drive that is accessed by
either
using a desktop shortcut or navigating directly to it through Windows
Explorer.

:

Some lines to investigate:

1. What version of Access is this?
And what service pack?

2. What version of msjet40.dll is on each machine?
(Typically found in windows\system32.)

3. Are different records being returned?
Or is it just a different calculated total?

4. Is the report based on a query?
Does this query return the different numbers of records also (or is
the
difference only in the report)?

5. Does the query have any parameters?
Does declaring the parameters make any difference?

6. Is there any difference with Regional Settings between the 2
machines?
(In the Windows Control Panel.)

7. Does each machine have its own separate copy of the front end?

I have an Access database that sits on a shared network drive. When
a
user
connects to it from his pc and runs a report the results are
erroneous.
When
the same user logs into a different pc and opens the application to
run
the
same report, the results are as expected. The Access application is
split
between a front end with the reports, forms and queries and a back
end
with
the tables. I checked to make sure that the front end was linked to
the
correct back end and it was.
What else could be causing differing results for the same report
when
run
on two different pc's?