INSERT INTO - Error 3073

[ruza13]

Hi all. (For first i have to say, that my english is bad, so sorry ... )

I have this special problem. In VBA i have function, that is inserting new records to the table.

sql = "INSERT INTO ...VALUES... " - its basic INSERT INTO, nothing more

CurrentDb.Execute sql


I fill the sql from txt lines. In txt i have 283000 same lines. All lines are (for testing) totaly the same. And in the loop i fill the sql and try to insert it into the DB by function currentdb.execute sql. Many lines work perfekts. But sometimes i get Error 3073-Operation must use an updateable query. But if i set the breakpoint in that makro and i try to pass line CurrentDb.Execute sql again, it works fine.
Where is the problem? Why inserting of 283k totaly samerecords works fine for 99,9%, but for some not? Why repeating the step, that crashed with the error message, works on second chance fine? ... I don't know, what to do whit this :-(

Thanks to all.

 

[Khalid_Afridi]

Hi, which version of Access you are using? is it Access-2010?
In Access-2010, the maximum 80000 (don't remember the exact number) rows can be copied & paste in one go. astonishing.

 

[mdlueck]

I have an Access written data replication application which downloads records from one SQL DB and publishes the records to another SQL DB.

I am using ADO objects to publish the records to the target DB. Perhaps transitioning to code like posted here will prove more reliable.

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

 

[ruza13]

Khalid:
I have try it in Access 2010 and 2007 too, but result is same. Maybe in Access 2007, this problem appears not so often.
Another interesting thing is: i have another import function, in this function i have to transform some fields, i have more columns, ... but it works faster and without problems. (Records are still about 250k in this second txt too)

mdlueck: thanks, i can try it

 

[ruza13]

mdlueck:I tried it, but it still not works. Sometimes it fail after execute of 6000 inserts, sometime it fail after execute 2000 inserts...

Then I tried to use querydef.execute sql, but it chrashed too, but not so often. Sometimes it crashed after 6000 inserts, sometime it crashed after 50k of inserts...

It's really curious problem :-( ... and I don't know, what to do...

 

[mdlueck]

Another thought, I did have to put this call inside the loop which is transferring records one-by-one...

    'Keep UI responsive
    DoEvents

Otherwise the UI completely froze up while the transfer was underway. So the only LONG running replications I have actually running have that call inside the loop.

 

[ruza13]

Yes, with DoEvents it works better and without freezing, but problems still persist. Today it chrashed after 28k of inserts... Its better, but not good.