Unanswered: Oracle ODBC (Oracle9.2), and records that do get imported into MS Access
Preexisting Oracle table was analyzed by regularly scheduled job in the morning and that afternoon I added records to it tripling its size (yes, I did COMMIT the inserted data). Oracle group by queries showed that I had the data I wanted and this morning I imported that Oracle table and recreated the group by query in MS Access 2000 to prepare for creating a report and discovered that 67% of the expected records were missing. I looked at the properties of the Oracle table (in Oracle database) and the row count was exactly what was in the Access version of that table which was based on statistics from the previous morning's table analyze. On a theory I reanalyzed the table in Oracle and then reimported it into Access and all of the records were there. I have worked with MS Access 97 for reporting production the past 3 years and just recently all of our clients (State Medicaid, Worker's Comp, and State Employee Agencies) upgraded to Access 2000, and I have never noticed this problem before and none of my colleagues have reported such an occurrence. Could it be MS roulette wheel spinning in the background and I just happened to win, or is there a known ODBC and table analyzed information that causes this to occur? Many tables I work with don't even get analyzed and this hasn't caused any known discrepencies in the data imported into Access.
Originally posted by Bart71
Did you re-import the entire table or only the data into an existing access-table? Wich way you use to reimport data? ODBC or some VBA-Code?
I always import Oracle tables from within Access (97 now using 2000) using the Oracle 92 ODBC driver. I deleted the original (and incomplete) table and reanalyzed the Oracle table from within the Oracle(9i) database before repeating the import via Access' own right click-IMPORT method. Only that last time all of the expected data was imported. Which makes me question whether Access or the ODBC driver uses information from the properties/statistics that have been gathered from having analyzed the table while its in Oracle. No I haven't experimented with this and try to recreate the scenario, it was either do that or write this question. I have too much production pressure I feel guilty spending time on this issue instead of meeting internal deadlines as it is right now.