Bulk INSERT doesn't insert all records

[dirkps]

Hi - i have a very annoying problem which i hope someone can help me with.

i use MSAccess in a multi user environment with a front end mdb and a backend mdb. In essence the users would run a process that creates a text file (outside of access) - this text file is then imported into the front end mdb (in a temp like table) and the i perform an INSERT INTO statement from my temp table into the backend mdb - it works fine most of the time but quite often it fails to insert a few random records - it doesn't generate any errors. I can see the records int he text file and the temp table but form some reason it gets missed out.

There are about 6 users using access at the same time and data volumes are fairly high (500k-1mio rows) but this shouldn't be a problem. Backend db size is around 500mb compacted.

i perform the INSERT using the Database object e.g. <FrontEndDb>.Execute example sql below

sql = "INSERT INTO [" & pDestDb & "].tMain_Scalar " & _
"(RunNum,TrdNbr,RiskBook,Ccy,Type,Dictionary,Index,_Value,SourceSystem,RepDate) " & _
"SELECT " & lRunNum & ", TrdNbr, '" & sRiskBook & "', Ccy, 'PV', Dictionary, Ccy, hPV, 'FA', RepDate " & _
"FROM " & tblFrom & _
" WHERE ABS(PV) > 0"

with pDestDb being the backend db

i've tried using transactions but it made it worse. Would appreciate any help on this one !!!

thanks

 

[aman]

try the following code:

sql = "INSERT INTO tMain_Scalar(RunNum,TrdNbr,RiskBook,Ccy,Type,Dictionary,Index_Value,SourceSystem,RepDate) " & _
"SELECT lRunNum, TrdNbr,sRiskBook, Ccy, PV, Dictionary, Ccy, hPV, 'FA', RepDate FROM  tblFrom WHERE ABS(PV) > 0"

Can you also tell me whats PV and FA here.

Cheers

 

[dirkps]

thanks for the reply but just to clarify - the SQL itself works fine most of the time - my problem is that when inserting 80,000 records (in one go ie calling above statement once) it inserts only 79,996 out of the 80k for no apparent reason - a rerun will fix it - it seems to be most frequent when all 6 users are on at the same time. Must be a bug as i would expect all or nothing and was hoping that someone has seen this before and found a workaround.

 

[The_Doc_Man]

If you have errors enabled and no error trapping, you should get an error. If you have errors temporarily disabled because of the huge insert, you might be getting something like a key violation - if there are non-duplicate key possibilities here.

 

[dirkps]

hey Doc Man - thats the frustrating bit - i'm not getting an error (with no error trapping enabled) - i've tried using the property dbFailOnerror but it made no difference.

A total of around 1,500,000 records are inserted every day (i create a new mdb for every day) by 6 users roughly at the same time and for some reason it drops +- 10 random records - it only seems to be a problem when all uses aer running at the same time - i was hoping there to be a more robust way to do this than to use the db.Execute method.

 

[aman]

Hi dirkps

Have you got the answer of your problem. I am getting the same problem for so long.

Thanks
Aman