Wait for Import Dialogs (Access97 SR-2)

Topic

New Reply

I am enhancing an old application in which the user begins by importing new inventory data into two tables. If I place the line:
DoCmd.RunCommand acImport
twice (one for each .xls that needs to be imported into a table), it doesn’t wait for the Import Dialogs to be displayed and used. It just flies through all the code (there is more after these lines) and I end up with no data imported.

How can I make it Wait for those Import Dialogs to be displayed and used? If my approach is wrong, please tell me a better method for accomplishing this. TIA

Reply | Quote

Replies

This doesn’t really answer your question, but have you considered just creating a link table that links to the .xls file. If you can do it this way, you won’t have to explicitly import the tables each time and the link tables will always be referencing the latest versions of the .xls files.

Just a thought…

Reply | Quote

Thank you for your suggestion, but this application has a signifcant amount of code that depends and takes subsequent actions on these two tables. So, I really need to get them into the Access table structure that has been set up.

Reply | Quote

I am sure there are better ways to do this but here are two ideas:

1. Pause the code
I have run into a similar situation with graphics not updating as fast as the code being run. Thus, the graphics switch views before they are completely loaded. To fix this, I used a pause function (See below) that pauses the code long enough for the process to catch up. I then put the pause statement after the command, in your case, the docmd.runcommand.acimport.

Function Pause(ByVal nSecond As Single)
Dim t0 As Single
t0 = Timer
Do While Timer – t0 < nSecond
Dim dummy As Integer
dummy = DoEvents()
If Timer < t0 Then t0 = t0 – 24 * 60 * 60 ' if we cross midnight, back up one day
Loop
End Function
'Pass 10 to pause for 10 seconds

BTW – I got the pause function from someone else in this forum….

2. Another way I have worked around this is to run the import code through a macro and the last line of a macro supplies a message box that the data import is done. Thus, in your code, call the macro to run the import. Put another message box after the macro call that says Is the import done. Thus, you will get the second message box while the data is importing. Do not press OK on the second message box until you get the message box from the macro indicating that the import is done. The code will then continue to run after you press OK on the second message box. (This crudely pauses your code while the macro is running.) This is not elegant, but it works when importing large quantities of data.

I am sure there are much better ways to do this. I will be watching to see other solutions that are suggested.

HTH

Reply | Quote

I like the Pause function and will probably use it in the future, but I couldn’t on this one because I could not be sure of the times that it would take to import these tables. They vary in sizes and the users vary in speed.

I started working with your MsgBox idea and found that just the following:

'Import 1st Table
MsgBox ("Please import 1st table")
DoCmd.RunCommand acCmdImport
'Import 2nd table
MsgBox ("Please import 2nd table")
DoCmd.RunCommand acCmdImport

caused the Import Dialogs to wait for user completion before continuing with the code (there is more code after the part shown above). I do not know why this works (which really bugs me), but thank you very much for guiding me to this solution.

Reply | Quote