Question Other factors which affect access - queries producing incorrect results occassionally

[LomaxE]

Hello, this is my first post and I'm really hoping someone else will have encountered this.
I have an access database which has a run of 100 plus queries which all append data to a single table.

I have a macro set up to run these in order so that I can run it over night when nobody (in theory) should be locking the various source tables.
The individual queries all seem to work fine and will produce the expected number of records, 1 for each month in the most simple example. However, when I run the macro overnight some queries will produce additional incorrect lines for a few months and not others. The queries affected differ each time and the months that have the additional lines change also. When I then use another query to delete the affected lines and re-run the individual query, it again works fine.

Does anyone know if access gets it's knickers in a twist over macros with large numbers of 'open query's in them or ever come accross any similar problem? Our server constantly borders on being almost full and I'm hoping that when we finally migrate to the new server that this might have been the problem but I thought I'd ask in case there is some other obvious cause. The database itself is not huge (319,204kb) - I'm using 2003 with service pack 3.

Thanks in advance!!

 

[DCrake]

I would suggest you convert your macro to a visual basic module and in the resulting module insert a DoEvents between each query. This will render Access to wait for one to complete before running the next one. It may be that Access is getting ahead of itself.

Place the resulting code into a public function and you the Call command to run the function from a commnd button or some other method.

 

[LomaxE]

Thanks for getting back so quickly!

Although I use VBA in excel, I've never used it in access before. I basically stick to the queries and basic macros in access.

I don't mind the idea of working out how to do what you described above but how would I then get that to run as a scheduled task?

Or would there be a doevents equivalent in the macros module? Would a close - query in between each open query work or is it struggling with the number of queries in general? It's possible that the number of queries could double too.

If you think the VBA route is best I'll look into how I'd do that.

Cheers
Emma

 

[DCrake]

You can follow the steps I descibed and save them as a public function. Then create a new macro to run the public function. RunCommand.

 

[LomaxE]

Great, well i've gotten a test script working and I've just set it up to run the 100 odd queries overnight. Fingers crossed for no more anomoly lines in the morning!!

Thanks again.

Emma

 

[gemma-the-husky]

the way you describe the problem sounds strange

... deleting the incorrect lines?

are these action queries?

is it possible that you have some cross-product error producing spurious duplicate/extra lines?

 

[LomaxE]

Thanks All,

Setting up my queries to run in VBA and DoEvents in between each one seems to have sorted it! No more wacky results in the morning.

Dave - I'm not really sure what you mean but I wasn't getting up any error messages. I suppose it might have had something to do with access not closing one query properly before starting the next as most of the queries were trying to look at the same tables it could have caused the odd results.