DB seems to do next task before waiting for prev. one to finish 1

[DanielleH]

Hi All,
I am developing an application which has a VB 6.0 front-end and an Access 97 backend. I use a Shell command to execute a macro in Access which:
- deletes a table named SCHEDULE_STAGING
- copies SCHEDULE_STAGING_TEMPLATE and pastes it as SCHEDULE_STAGING

After that macro is run, I use SQL in the VB code to execute queries on SCHEDULE_STAGING. The problem is, I receive an error stating that "The Microsoft Database Engine could not find the object 'SCHEDULE_STAGING'" etc, yet when I step through to code to debug it, it works fine! I have encountered this error before with another Access database and I used a timer to pause the code but surely there is a better way to combat this problem.
Does anyone know of a function which waits until a process is finished before moving onto the next one? In my case, I need the app to wait until the new copy of SCHEDULE_STAGING is present before conducting queries on it.

Thanks in advance for any help y'all can offer on this matter. )

 

[djsiders]

Why not use VB to perform the operation instead of calling an Access macro. Also, if you're just trying to clear the data out of the table for use with new data, use a Delete Query (DELETE * FROM SCHEDULE_STAGING. Then use VB to put new data in the table to start working with.

 

[DanielleH]

I need to delete the whole table because each time I run the process, there is a different number of fields. I have four or five fields that are always there, however the number of fields that are added to the table each time the process runs is different. Hence I cannot simply delete all rows from the table, I need to delete the variable fields from it too.

 

[Steve101]

Danielle,

I concur with djsiders approach. The fact that the structure of the table is changing is a bit of a concern in itself. Its possibly an indication of poor data normalisation, which could be exactly the reason that you're running into the sort of problems you're running into.
What is the nature of the table definition changes (ie. additional columns). Perhaps you want to consider whether its worth/possible to change this to a more static table design.

Food for thought,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[JohnYingling]

The Shell command starts another program to run asynchronously. It does not wait for completion of the started program.
Perform an Advanced Search with "Shell Wait", All words to see how to wait.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[DanielleH]

Thanks JohnYingling.
Steve and djsiders, this design is being used as a last resort - the client requires a report in a very particular format with variable columns and rows each time the report is generated. I have had to generate the table in Access and then send it to Excel to format in order for it to look exactly the way they require.
I wish I could have had some expert help early on however restrictions on time, budget and expertise blah blah blah..... )