batch of queries not executing

[Stuartp]

I am writing a VB program where when the program is started it will perform a number of deletions and calculations on an Access database table. There are therefore 4 or 5 queries listed on after the other e.g.

strSQL = "DELETE purcheck.* From purcheck WHERE purcheck.[stockno] like """ & searchTerm & """ or purcheck.[stockno] is null "
    Set dbfPurcheck = DBEngine.Workspaces(0).OpenDatabase(PURCHECK_PATH)
            dbfPurcheck.Execute (strSQL)
            
            'recalculate averages for Leeds
            Let noMonths = DateDiff("m", #8/1/2001#, Date)
            dbfPurcheck.Execute ("UPDATE purcheck SET purcheck.[avg mth] = [sales full yr]/" & noMonths & " WHERE (((purcheck.[sord stk loc])=3))")
            
            'recalculate averages for Bolton
            Let noMonths = DateDiff("m", #12/1/2001#, Date)
            dbfPurcheck.Execute ("UPDATE purcheck SET purcheck.[avg mth] = [sales full yr]/" & noMonths & " WHERE (((purcheck.[sord stk loc])=2))")

etc etc...

If i run my program in debug mode using F8 then everything is fine. But if I run the program normally most of the queries don't execute e.g. records are not deleted, the maths are not performed accurately etc.

Could it be that my program is running too quickly and MS Access can't keep up and so ignores the queries? If so is there a way to slow it down?

Or is there another explanation for this strange behaviour?

Many thanks

Stuart

 

[NickBulka]

Stuartp,
When you run queries in this fashion using VB, MS Access is not running. It's actually the JET engine. You can run queries like this without even having Access installed.
These queries are synchronous. That is, the first must finish before the second can start, etc. JET will not ignore queries. If there's a problem, you'll get an error.
Are you sure that the event that executes these queries is actually firing? I'd put a message box or debug.print message there to make sure.
nick bulka

 

[Schroeder]

I don't remember where.. and I really don't know anything about it, but I recall, I think it was, petermeacham complaining about some write-back cache delay or some such thing. The way he told it, certain actions taken against an Access database didn't take effect for a few seconds after the fact. Perhaps the alterations you're making to your tables are not "taking effect" before the calculations are being made. Your .Execute statements are finishing but the table isn't being updated for a second or two after that.

Sounds unreasonable, I know, but I'm just throwin' it out there for ya.

Maybe put a little Sleep delay between each of the actions could be tried.. at least to rule this idea out.

 

[Stuartp]

Thanks for your replies. I have discovered that if I stop using this code to run a macro in Access to import an Excel spreadsheet into the database:

Dim app As New Access.Application
Dim importsheet As New AllMacros
app.OpenCurrentDatabase PURCHECK_PATH
            app.DoCmd.RunMacro "importsheet"
            app.CloseCurrentDatabase
            Set app = Nothing

That the rest of my queries execute the way I want them to, so it looks like this is causing the problem.

But now I have a new problem - how to import an Excel spreadsheet into Access from a VB program. I have searched the forums for this but did not find anything. Is there a way to do this, or should I start using CSV files instead?

Thanks

Stuart

 

[petermeachem]

The write back problem is in thread709-229140 plus lots of others. Strongm and others discussed this at great length in another post.

Worth reading about. Peter Meachem
peter@accuflight.com