2 records short when writing to a SQL Server table.

[PhilEngle]

I have a Microsoft Access 2002 ADP that goes against an MS SQL Server 2000
database.

Within this MS Access ADP I have a recordset (forward only, read only,
cachesize 1) that is the result of a SELECT. . .WHERE EXISTS query. This
recordset is gone through to create corresponding records (1-for-1) in
another recordset which is open for write (forward only, optimistic locking,
cachesize 1) to an empty SQL Server 2000 table.

This works fine if the first recordset returns 5 or fewer records. However,
if the first recordset returns 6 or more records, then the actual number of
records that appear in the SQL Server 2000 table are short by 2: In other
words, if 6 records are expected I only get 4, if 7 records are expected I
only get 5, etc. !

Debugging shows that the RecordCount is correct, that AddNew is executed the
correct number of times, and that Update is executed the correct number of
times. Yet, when I look over at the resulting table using Enterprise Manager,
it's 2 records short!

There's nothing wrong with the WHERE EXISTS query either: It works perfectly
in other contexts. Also, CacheSize has nothing to do with the problem: In
addition to the original value of 1 I tried -1, 20, and 100.

Any ideas? Any help you could give would be greatly appreciated!

 

[PhilEngle]

The problem was solved in a way I didn't expect:

It turns out that the ultimate source of the problem was that I defined only
the column LastName as the primary key of the table receiving the new
records, thus resulting in the failure of all but one of the individuals
having the same last name to be written to the table. Defining the primary
key to be LastName, FirstName, MiddleName, Suffix solved the problem.