Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wait for Import Dialogs (Access97 SR-2)

    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

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wait for Import Dialogs (Access97 SR-2)

    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...

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wait for Import Dialogs (Access97 SR-2)

    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.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Wait for Import Dialogs (Access97 SR-2)

    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
    Regards,

    Gary
    (It's been a while!)

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wait for Import Dialogs (Access97 SR-2)

    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:

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •