Problem scrolling through Access Database programatically

J

JeffM

I am programming in C++. Moving data from a large number of text files into
an Access database with several tables. While adding a record to one of the
tables I get an Exception with the message
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship"
When I am adding the records rather than using an autonumber for the primary
key I am using a number and scrolling through the current records from first
to last using the code
MoveFirst();
while (!IsEOF())
MoveNext();
I then get the ID of the last record and increment it by 1 to make my next
Primary Key ID. I have stepped through the code and found that at some
undetermined point in the import the MoveNext() stops returning the last
record, so the next ID I try to add is already in the database. Hense the
above error.
If I go into the database in Access it appears fine and all the records upto
the exception are there including the one that MoveNext will not go to.
I am begining to think it must be an ODBC driver issue but I am running XP
and Access 2000 (I am unsure which service packs).

Has anyone got any ideas?
 
A

Albert D. Kallal

The problem might be that you are not setting the "order" of your data set.

You can not assume physical order.

If you add 10 records to a database, and later open the database, you can
NOT assume that the records will be returned in the same order.

In old data processing systems, like punched cards, or even old FoxPro/DBase
systems, the physical order of data was in fact maintained, and in fact each
record was fixed length. Thus, these old systems even had a concept of a
record number, where the 4th record on disk could be determined by 4 *
reocrdlength = byte offset. Thus, you would calculate a physical offset into
the file to determine the record.

Fast forward to today:

, that data might be coming from a another computer system half way around
the world, and thus we can't hope, or assume that retrieval of a piece of
data resolves to a particular location on disk (we simply ask some data
engine to go and get a record via a key id...as now we don't open the file
across the large distance!). Further, there might be 10 other people adding,
or deleting, or working with that data. So, as a developer, we CAN NOT
assume any order from the data anymore. Client to server has caused the
conceptual change. And, while ms-access and "JET" is a file based
system..the modern concepts of non ordered data was adopted (and, this
likely explains why ms-access is still INCREDIBLE popular today, and have
been in the market place for more the 10 years!). Hence, in modern systems,
data is consider a un-ordered plate of spaghetti.

If you need some order in your data...then you MUST use a sql statement to
order your data into a reordset. You can't assume order here.

The above notes might be complete obvious to you. However, it does mean that
you MUST set your data order for that reocrdset, and you cannot assume the
order will be the same unless you actually specify the order.

It is interesting how older developers naturally assume record order, and
new developers never have even learned, or are aware that old systems, and
old file systems actually kept order of data!! So, not sure if the above
info is a surprise or not!!

And, it may, or may not help you!!...but you might check if your recordset
has any order set to it....
 
Top