Timing Issue?

[aytee111]

(Access 2003)

The code:

1-open another database
2-for a specified query (name supplied)
for each fld in qfd.fields
write a record to a local table
3-close database
4-create query based on local table
5-RefreshDatabaseWindow
6-open form with query as record source

The code works, the table is populated and the form opens with data. However, sometimes it happens and sometimes it doesn't. I put a loop/counter in to refresh and open form until error=0 - I went as high as 2000 but again, sometimes this is successful and sometimes it isn't. The error is: 2580 The record source specified on the form does not exist.

Each time I reach the counter max I then get the error "You do not have exclusive access to the database at this time" when I try to make a change.

It seems as if it is getting to step 4 before 1-3 are finished.

Any pointers or assistance on this will be GREATLY appreciated.

 

[JHB]

Try by putting in DoEvents at relevant places in your code.

 

[aytee111]

I had tried putting it in after the Close (step 3) but that didn't help. What do you mean by "relevant places"? I have to admit to not fully understanding the way Access exits to the operating system and comes back.

 

[JHB]

I had tried putting it in after the Close (step 3) but that didn't help. What do you mean by "relevant places"?

I don't know your code, so it is difficult for me to say exact where to place it.
But places I would say, when you open/close another database, when you execute a query which involve tables with many records - "time" consuming places.
A good pointer to find out if a DoEvents would do some differences, is to put in breakpoints in your code and step slowly through the code.
If your code then execute without problem each time, then a DoEvents (mostly) helps, but where exactly to placed it (them) can only be determined by placing it in different places in your code.
A loop doesn't help because MS-Access still use resources to run through the loop.

 

[aytee111]

Solved by changing this line:

From: Set dbs=OpenDatabase("databasename")

To: Set dbs=DBEngine.Workspaces(0).OpenDatabase("databasename")