[AccessD] Data on Report does not match data in the Report's RecordSource - Problem Resolved!

Brad Marks BradM at blackforestltd.com
Sun Oct 16 21:12:17 CDT 2011


Oh Boy... Do I ever feel STUPID!

Because of the suggestion, to Relink the Firebird table in question, I
decided to do so.

In the process of doing the Relink, Access displayed a message "To
ensure data integrity and to update records, you must choose a field or
fields that uniquely identify each record".

As I understand it, the field(s) selected results in establishing the
Primary Key on the Access side for the linked table.

When doing the Relink, it dawned on me that I had earlier selected the
wrong field to uniquely identify each record.   I believe that this
mistake resulted in the strange results that I was seeing.

This mistake was mine.  For a time, I thought that I had stumbled on
some sort of bug, or angered the "ODBC gods"   :-)


Thanks again for the help.  Once again, the advice given by others here
in the AccessD forum has helped to resolve a problem (and keep me from
pulling out even more of my hair).

I really appreciate the help and advice that is provided on this forum.

Sincerely,

Brad




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Sunday, October 16, 2011 8:22 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Data on Report does not match data in the
Report'sRecordSource

>  It would be good if you could go into a little more detail on what it
is
> your seeing.  Ie. more records?  Different field values for the same
>records?  


Jim,

Thanks for the help.

Here is what I am seeing...

I use the purchased application to add two records to one table.  

When I view the two records with an Access 2007 query, both records
appear to be identical (this is NOT correct).

When I view the two records with an Access 2007 report, the two records
are correct (the data shown on the report is exactly what I entered into
the application system). When I view the data in the two records via
Firebird's isql utility, the data is correct.

What I really can't understand is how the report can show the right
data, but the record source for this very report shows incorrect data.

Brad 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Sunday, October 16, 2011 8:00 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Data on Report does not match data in the
Report's RecordSource


 Watch out with reports; 2007 introduced Report View, which will show
you
odd data at times.  Lots of strange things can happen there as most of
the
report events don't fire. If your depending on any code to support the
report, you may see different things.   Sounds like your problem is more
then that though.

  I'd also look at database corruption; create a new DB and import
everything into it, then see if you get the same results.

  If you do, that would be wild; I've never heard of anything even
remotely
close to this.  I would also seriously doubt it could be wide spread as
2007
has now been out for over four years.

  It would be good if you could go into a little more detail on what it
is
your seeing.  Ie. more records?  Different field values for the same
records?  

 Keep us updated....

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Sunday, October 16, 2011 05:14 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Data on Report does not match data in the Report's
RecordSource

All,

I believe that this is the strangest thing I have ever seen in Access
2007.

We have a purchased package which uses a Firebird Database. All of the
tables are defined by the application vendor.  We are able to access the
Firebird tables from Access 2007 via ODBC.  This combination has worked
nicely in numerous tests that we have conducted over the past 2-3
months. 

A few days ago, I happened to notice that the data on one report does
not match the data in the Report's Record Source (a query).  I have
conducted many tests on two different PCs and the results are
consistent.  The data on the report is correct, but the data in the
underlying Record Source is not correct.  If I look at the Access
"Linked Table" (Which is stored in the Firebird Database), the data is
not correct (but I can make it appear correct by doing a Refresh in
Access). 

If I view the data with the Firebird Utility called "isql", the data is
correct.

I have spent many hours trying different things and running various
tests (and pulling my hair out).


In summary...

Data on Report = Correct

Data from Firebird "isql" utility = Correct

Data in Report's Record Source (query) = Not Correct

Data shown by viewing "Linked Table" = Not correct (although I can make
it appear correct by doing a "Refresh" in Access) 

Data as viewed via the purchased application = Correct

We have conducted many tests with the Firebird Database - Access 2007
combination over the past few months, so we have some experience, but
not a huge amount.

My latest theory is that the problem lies in the ODBC driver.

This would explain why the data appears correct with isql but appears
incorrect when initially viewed as an Access 2007 Linked Table.

However, this still doesn't explain why the data on the report is
correct when the data in the report's record source is incorrect.

Has anyone ever heard of anything like this before?

Has anyone worked with Access in combination with data from Firebird
database tables?

Thanks for your help,

Brad   


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.





More information about the AccessD mailing list