Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29

    Unanswered: DoCmd.RunMacro (timing) problem

    Hi there,

    I'm running a macro in a database from within another database. I create an instance of an Access application and then call the DoCmd.RunMacro action like this:

    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strDatabase
    appAccess.DoCmd.RunMacro myMacro
    appAccess.CloseCurrentDatabase
    Set appAccess = nothing

    This works perfectly but... since 2 days this construction doesn't work anymore. Access says it can't find a specific function name. It gives this error on the DoCmd.RunMacro line so appearantly it can't find the DoCmd function? But when going into debugmode and waiting for, like, 3 seconds before (!) executing DoCmd.RunMacro, things DO work! Does anyone know what's going on and how I can solve this problem?
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    You could try:


    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strDatabase
    appAccess.DoCmd.RunMacro myMacro
    Doevents
    appAccess.CloseCurrentDatabase
    Set appAccess = nothing


    (Doevents yields to the system, maybe this will help?)

    Or put a delay (for..next) in between the close statement and the run statement ~ Not really ideal...

    Suggestion: you want to have some sort of flag, so when the macro has run it changes the flag, then the database closes.

    Regards

    Ken.

  3. #3
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    Thanks for your suggestion. That was actually the first thing I tried but it didn't work. Putting in a loop like this:

    for i = 1 to 15000
    doevents
    next i

    did work in some cases. Now i use something like

    start = timer
    while timer < start + 5
    doevents
    wend

    and this seems to work. But... i'm not sure if it works all the time. If I run it by hand, it works. If I run it using a scheduled task, it fails. I'm really puzzled here.
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

  4. #4
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Does your macro update a table ? If it does, try this:


    F_Date = CurrentDb.TableDefs("Your_Table").LastUpdated

    'your macro here

    Do Until CurrentDb.TableDefs("Your_Table").LastUpdated <> F_Date
    DoEvents
    Loop



    This will do an endless loop until your table has been updated..

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Gang:

    I'll share with you my "delay" routine I wrote ...

    Code:
    Public Function LoadingDelay()
        Dim StartTime As Date
        
        DoCmd.Hourglass True
        StartTime = Now
        Do While True
            DoEvents
            If DateDiff("s", StartTime, Now) > 5 Then Exit Do
        Loop
        DoCmd.Hourglass False
    End Function
    This is obviously set for a 6 sec delay but, can be easily modded to perform for any interval desired ...

  6. #6
    Join Date
    Jul 2003
    Location
    Netherlands
    Posts
    29
    The problem is that it won't run the macro at all. It just exits saying it doesn't recognize the function. This is what it's saying:

    "The expression you entered has a function name that Microsoft Office Access can't find."

    It tries to open the database (see previous code) and then run a macro. I think it still hasn't opened the database itself and already tries to run the macro. Anyway, it exits on the RunMacro line in the code. The weird thing is that it seems to work with 6 of my databases. It only exits on 1 of them which is not even the largest of them all. But... it does work on that database also when I wait for, like, 3 or 4 seconds before actually running the macro... Weird huh? Any suggestions?
    Bye,

    Jeroen

    A 3D editor project
    www.delgine.com

Posting Permissions

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