Only Imports 50% Data

[Tony Wainwright]

Using Access 2003

I have a routine that imports data into a table from an Excel spreadsheet
that is programmatically linked to the database. The following code segment
illustrates how I import the code into a table:

Set rstLookup = DBEngine(0)(0).OpenRecordset("SELECT * FROM Soap;")
'EXCEL
Set rstSoap = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSoap;")
'Access Table
BeginTrans
Do While rstLookup.EOF = False
If IsNull(rstLookup!ACCOUNT_REF) Then
Exit Do
Else
With rstLookup
rstSoap.AddNew
rstSoap!ID = !ID
rstSoap!AccountRef = !ACCOUNT_REF
rstSoap!Name = !Name
.........
rstSoap.Update
End With
End If
vCount = rstLookup.AbsolutePosition + 1
rstLookup.MoveNext
Loop
CommitTrans

There are 24,000 records in the spreadsheet yet only 12,000 are imported. I
thought that there may be a limit in access. But even if I reduce the
spreadsheet to 80 records it still only imports 40. I also thought that it
might be going too fast, so I tried a For time = 0 to 5000 Next loop before
the MoveNext - no change.

Any ideas anyone? - I have used this format many times but never come across
this before.

Cheers
Tony

 

[gandalf]

not sure
but I would let Access haul over the complete list

if rstLookup.recordcount>0 then
rstloopup.movelast
rstlookup.movefirst

as soon that rstLookup!ACCOUNT_REF is empty,the import
will stop. Is this correct?
could be that somehow your newly added records are found
before you are finished
(what happens if you leave away exit do?)

leaving away
With rstLookup
End with
but keeping
rstSoap.AddNew
rstSoap!ID = !ID
rstSoap!AccountRef = !ACCOUNT_REF
rstSoap!Name = !Name
.........
rstSoap.Update

what does it do?