How do I hide the error "The Microsoft Office Access database engine could not find the object..." when an Access database runs with an AutoExec Macro?

I have a Microsoft Access database that is scheduled to run at 8:05am everyday. It uses an AutoExec macro, and the first step of the macro is to SetWarnings = "No". The 2nd step is to import a file. The File Name that it imports is ="E:\Brad\Transfer\DDP Order Confirmations\RecOrd" & Format(Date(),"mmddyy") & "0801". This file is created off of orders in our database, and if no orders have been taken by 8:05am, this file is not created, and will not exist for Access to import.

The problem we encounter happens when the file doesn't exist. When the file does not exist, we get the following error: "The Microsoft Access database engine could not find the object 'E:\Brad\Transfer\DDP Order Confirmations\RecOrd0527090801.XLS'. Make sure the object exists and that you spell its name and the path name correctly. (Error 3011)" We would like for the Access database to disregard the error, and just close, or move on to the next step of the query if this error is encountered, but it gets hung instead. We thought the SetWarnings action would resolve this, but it doesn't.

What options do we have here to bypass this error when it is encountered? We do not want the Access database to be hung indefinitely when it occurs.
LVL 5
Naithan ArroyoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<What options do we have here to bypass this error when it is encountered? We do not want the Access database to be hung indefinitely when it occurs.>>
  In short, from the Autoexec macro, nothing.  The main drawback of using a macro is that there is no error handling, so it's imposssible to trap the error.
  You need to move the import into a code procedure and then call the code procedure from the Autoexec.  Since you'll be calling the procedure from a macro, it will need to be a function so a value can be returned to the macro (it doesn't matter what you return, just that you return something, which the macro is expecting).
  If you need help with that, let us know.
JimD.
0
Naithan ArroyoAuthor Commented:
Yes, in fact, I do. I do not know the coding side of Access at all, which is why I try to resort to Macros whenever possible.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Yes, in fact, I do. I do not know the coding side of Access at all, which is why I try to resort to Macros whenever possible.>>
  Need a little more info on the import; can you post all the parameters of the macro command please?
JimD.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Naithan ArroyoAuthor Commented:
Let me finish the macro today, and I'll post the entirety later this afternoon. Thanks for the responsiveness, J!
0
Naithan ArroyoAuthor Commented:
I finished the macro, and converted the macro to Visual Basic. The code is copied below. From here, I could use your help.
Option Compare Database
 
'------------------------------------------------------------
' AutoExec
'
'------------------------------------------------------------
Function AutoExec()
On Error GoTo AutoExec_Err
 
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "0_RecOrd_Current", acViewNormal, acEdit
    DoCmd.TransferSpreadsheet acImport, 8, "RecOrd" & "_121", "E:\Brad\Transfer\DDP Order Confirmations\RecOrd" & Format(Date, "mmddyy") & "_121" & ".XLS", True, ""
    DoCmd.TransferSpreadsheet acImport, 8, "DDP_RecOrd_History_121", "E:\Brad\Transfer\DDP Order Confirmations\RecOrd" & Format(Date, "mmddyy") & "_121" & ".XLS", True, ""
    DoCmd.OpenQuery "1_Not_Confirmed", acViewNormal, acEdit
    DoCmd.OpenQuery "2_Not_Confirmed", acViewNormal, acEdit
    DoCmd.OpenQuery "3_Confirmed", acViewNormal, acEdit
    DoCmd.OpenQuery "4_Confirmed", acViewNormal, acEdit
    DoCmd.Quit acExit
 
 
AutoExec_Exit:
    Exit Function
 
AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit
 
End Function

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I finished the macro, and converted the macro to Visual Basic. The code is copied below. From here, I could use your help.>>
  Well you got most of the work done already.  The procedure you posted has error handling in it, although you want to change it slightly because you don't want the database to stop.  So instead of:
AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit
You'll want:
AutoExec_Err:
    Resume AutoExec_Exit
  That means if any error is hit, the procedure will simply exit.
  Now from the Autoexec macro, in place of the commands you have, do a RunCode action.  Name of the function is AutoExec()
That's it!
JimD.
 
0
Naithan ArroyoAuthor Commented:
When the Macro Runs, an error is displayed. "The expression you entered has a function name that Microsoft Access can't find." The Macro Name is AutoExec and it has one action - RunCode / AutoExec(). I even tried clicking the Elipses on the Function Name to select the function I had built, but it returns the same error. Does it have to do with the name of the VB script being AutoExec also?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Does it have to do with the name of the VB script being AutoExec also>>
  I'm not sure.  What it is more likely is that you don't have it in the right place.  To be accessible to all the objects in the DB (ie. the macro), the code needs to be in a standard code module.
  You get this when you click modules in the database container window, then click new.  A code window pops up.  The code should be paster there.
  If you put it in a class module, or into a form or report module, the macro won't be able to find it.
JimD.
0
Naithan ArroyoAuthor Commented:
Great, got that fixed. I recreated the VB manually and then renamed it. Only remaining issue is that when the file is not found, the Access db does not close. It does not present any error, but it does not close.

FYI, when the file exists, the db closes as prompted in the last line of the VB.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Take this out of the VB code:
    DoCmd.Quit acExit

 and in the autoexec, add a Quit action after the RunCode, or modify the VB code to attached.
  I think it's cleaner and clearer as to what's going on though to do it in the Autoexec.
JimD.

Option Compare Database
 
'------------------------------------------------------------
' AutoExec
'
'------------------------------------------------------------
Function AutoExec()
On Error GoTo AutoExec_Err
 
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "0_RecOrd_Current", acViewNormal, acEdit
    DoCmd.TransferSpreadsheet acImport, 8, "RecOrd" & "_121", "E:\Brad\Transfer\DDP Order Confirmations\RecOrd" & Format(Date, "mmddyy") & "_121" & ".XLS", True, ""
    DoCmd.TransferSpreadsheet acImport, 8, "DDP_RecOrd_History_121", "E:\Brad\Transfer\DDP Order Confirmations\RecOrd" & Format(Date, "mmddyy") & "_121" & ".XLS", True, ""
    DoCmd.OpenQuery "1_Not_Confirmed", acViewNormal, acEdit
    DoCmd.OpenQuery "2_Not_Confirmed", acViewNormal, acEdit
    DoCmd.OpenQuery "3_Confirmed", acViewNormal, acEdit
    DoCmd.OpenQuery "4_Confirmed", acViewNormal, acEdit
 
AutoExec_Exit:
    DoCmd.Quit acExit
 
    Exit Function
 
AutoExec_Err:
    Resume AutoExec_Exit
 
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naithan ArroyoAuthor Commented:
Very good communicator, and right on track with every follow up! Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.