How to make DAO run on a thread.

[jal]

I have a bit of Access experience but am new to threading. I'm using C#.Net to load millions of records into an Access DB. I spun off a thread as to give the user the option to cancel the operation and resume later.

Actually I'm using OLEDB to populate the DB. However, I'm not aware of a way to use OLEDB to compact it. So I am trying to use DAO to compact it after every 100,000 records. But DAO doesn't seem to work on my thread. It seems to work fine, however, when run normally, that is, from the main thread.

I was also trying to use DAO to append a property value to the DB. This too failed on the thread - but I was able to move this code to the main thread.

However, I don't see how I can move compaction to the main thread since I probably need to compact after every 100,000 records or so.


Doe anyone have any suggestions?

 

[jal]

Come to think of it, I wonder if there is a way to create the cancel button on the thread. Then I could move all the code back to the main thread. I'll have to try that next.

 

[jal]

Well, that's not working either. The whole form blocks - the cancel button never becomes visible or accessible.

 

[jal]

Someone advised me to use the BackgroundWorker to spin the thread. For some reason DAO works fine when the BackgroundWorker creates the thread. Solved.

 

[gemma-the-husky]

you would have to put a DOEVENTS somewhere in the loop, and test whether the button has been clicked (set a boolean flag by clicking the button) - then it should work. The DOEVENTS is critical, as it gives Access time to multi-task, as it were.

this is dao , rather than ado, as I am not very familiar with ado - but this should give you the idea (there may be an exitwhile statement - not sure offhand without checking). Note also - I am not sure about the need to compact. I can't see the need if you are just adding records.

sort of

button_clicked = false
while not rst.eof
  doevents
  if button_clicked then goto abort
  process ....
  rst.movenext
wend

abort:

 

[jal]

I heard somewhere that anytime the filesize grows beyond 2 Gigs, even if momentarily, there is a danger of corruption. So that's why I want to compact every 100,000 new records.

 

[jal]

Ah, I'm beginning to grasp your post about DoEvents. Nice tip. However,I'm not using the Access engine. I'm just driving a Jet database using C#.Net.

I don't have permission to install the Access runtime on the user machines (even though it's free).