Timing Issue on Conn.Execute

[DerFarm]

       strTemp2 = CreateLinkedAccessTable(TargMDB, "PartA", TempMDB, "TempPartA")  'creates a linked table
       sqlTemp0 = BeneZip() 'Returns a SQL string
       For intTemp0 = 1 To 100
          DoEvents
       Next                'Supposed to make sure everything gets done
       TempConn.Execute (sqlTemp0)

[\code]

The linked table IS there .. I can open the database and see it.

The sqlTemp0 string DOES work.  I can cut it into a query and it functions.

the for ... next loop is simply to make sure that the operating system has time to do everything it needs to.  It was put there in desperation.

The execute statement ALWAYS bombs the first time.  I can wait 15 minutes and it will bomb.  It will ALWAYS work the second time (debug and then continue).  It claims that it can't find a file (the linked file)

any suggestions?

 

[CCLINT]

Is the connection object used in CreateLinkedAccessTable the same as TempConn?
If not, then pass the TempConn object variable to the CreateLinkedAccessTable function and use that.

Otherwise, we'll need to see the code in the CreateLinkedAccessTable function (or at least part of it)
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[DerFarm]

hmmmmm....The create function (like almost everything I write) is independant. It opens and closes everything it needs to function.

If this is the "bug" it would explain the flakey nature of the actions quite well. I'll try closing and re-opening the the connection.

Ok, just tried it, worked like a charm. That was it, thank you

 

[CCLINT]

There are 3 options that would help.
The first I mentioned, the second one you caught on to, and the third would be to add JRO (Microsoft JET Replication Object) to your project and flush the connection after executing it.

This is because of the lazy write cache that JET uses to improve performance... [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[DerFarm]

Any particular reason not to continue to open and close? It might be wasteful in terms of machine time/actions, but it will make future development (especially if I get hit by a bus) clearer ... at least it would seem so.