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.