Results 1 to 8 of 8
  1. #1
    maxis is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2013
    Posts
    30

    Windows(1).Visible = True getting AUTOMATION error - Remote Proc Call Failed -2147023170

    Running a MS Access 2003 split database (.mde FE, .mdb BE) - Since upgrading from OFFICE 2010 to OFFICE 365 the 1st line of code below fails. It gets some funky -2147023170 Automation Error - Remote Proc Call Failed. This never failed in Office 2010. Running Windows 7.

    Code:
    pobjBook.Windows(1).Visible = True
        pobjBook.Close savechanges:=blnSaveChanges
        pobjExcel.Quit
    pobjBook is a

    Code:
    Set pobjExcel = GetObject(, "excel.application")
        Set pobjBook = pobjExcel.Workbooks.Open(destinationPath)
    where destination Path is the full path and file name on the c: drive.



    Basically MS Access opens up an EXCEL.xlsm spreadsheet which remains hidden (EXCEL icon doesn't display on the task bar) while we update/populate cells within the spreadsheet with MS ACCESS data, make it visible to save the file, then quit EXCEL. If we comment out that 1st line of code, the "savechanges" parameter saves the file with the file name we give it, but it's an empty EXCEL workbook with no visible or hidden sheets. Not even a blank worksheet. Even though in the original spreadsheet starts with 5 tabs, 2 visible and 3 always remain hidden, and none of the populate cell in the MS ACCESS code fails as it's populating the data.

    I've tried several things, but nothing has worked. I'd like to somehow TEST (If statement) to determine if the pobjBook.Windows(1).visible = true fails, and if it does, then not execute that line of code or the save/quit code, but I'm not sure what to TEST for?

    The EXCEL spreadsheet has vba code in it too (on activate and deactivate events only), but nothing that would seem to cause any issues. In other words, all pretty straight-forward on the EXCEL vba spreadsheet side. The worse part is that we never experience any problems using OFFICE 2010 so could be it be a SETTING issue in EXCEL or ACCESS or even IE? The MS ACCESS Tools REFERENCES are all good (none missing). The Workbook is not protected.

    Any help/hints/try this would be greatly appreciated. Thanks.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    so little code, so many possibilities!
    According to your post, we can rule out references as the problem (or is it just that they are all found but one or more are not compatible with 365?)
    A solution might be found in that there's no reason to make the book visible in the first place. Just save it and close it if it doesn't need to be seen.
    A potential issue is that for some reason, after the version update the specified window is no longer active and needs to be activated. Not that the Office update would have anything to do with that, just that the update may be irrelevant. Other factors could have changed at the same time, such as coding or how the user interacts with the db.
    Or it is no longer number 1 in the collection of windows.
    Or don't refer to the application (Excel) by using the ordinal position of a window as a reference. Use the object reference (again, which I don't see due to the minimal code provided).
    Or it is a Trusted Locations issue, seeing as how the update could require those locations to be re-established, or so I've read elsewhere.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    maxis is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2013
    Posts
    30
    It is a TRUSTED LOCATON... and that is new with Office 365... in Office 2010 we used to have the MACRO setting in both the EXCEL and ACCESS set to lowest level so the ENABLES MACROS warning would not come up, but with OFFICE 365 we went to a TRUSTED LOCATION environment for both the EXCEL and ACCESS folders/files... do you have a link(s) that addresses the Trusted Location issue or a SEARCH KEY WORD to find it please?

    Thank you... if we take that one line of code out, the saved excel file is a work book with no sheets. I've never even seen that before; there's always one sheet. And there are not worksheets hidden either. I did create a LOOP and windows.count comes back as 1, so windows(1) should be valid but why it gets the error on .visible = true is the mystery question.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It is a TRUSTED LOCATON... and that is new with Office 365
    not actually true, it was new in 2007. Either way I can think of no reason why that would have an impact unless you have not set the location of the excel file as trusted.

    is it possible you are using two different office app bit size e.g. 32bit access, 64bit excel or the other way round?

    Another possibility might be the excel file is not being updated by your other code or you are not opening the file you think you are.

    What happens if you reduce your security settings to low again? does it then work? it may be you are trying to do something the higher settings wont allow

    what happens if you enable the spreadsheet to be visible? is everything happening as expected?

    what happens if you remove the vba code and save as a normal .xlsx?

    This OP in this link had the same problem - have you got any add-in's that might be causing the problem?

    https://superuser.com/questions/1124...-e-serverfault

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Long shot......

    Quote Originally Posted by maxis View Post
    Running a MS Access 2003 split database (.mde FE, .mdb BE) - Since upgrading from OFFICE 2010 to OFFICE 365 the 1st line of code below fails.
    Just so I understand: you have an Access 2003 FE and BE being executed in A2010? And you converted to OFFICE 365? What version of OFFICE 365?? 2010, 2013 or 2016?

    Do you have the FE in mdb format? (I sure hope so)
    Have you tried converting the .mdb FE/BE to A2010 .accdb format, then executing the FE/BE under OFFICE 365?

    If that works, you could convert the .accdb FE back to an .accde FE...

  6. #6
    maxis is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2013
    Posts
    30
    Did everything you suggested. Turns out the solution was to UNPROTECTING the workbook and workbook sheets and the problem line of code stopped being an issue. What changed from Office 2010 to Office 365 for this issue is STILL unclear, but we were forced with Office 365 into using a Trusted Location (versus lowest ENABLE MACRO setting we used for years with a Office 2010) and it’s encrypted as well, so maybe all these variables contribute to the very vague error messages. Thank YOU.

  7. #7
    maxis is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2013
    Posts
    30
    Did everything you suggested. Turns out the solution was to UNPROTECTING the workbook and workbook sheets and the problem line of code stopped being an issue. What changed from Office 2010 to Office 365 for this issue is STILL unclear, but we were forced with Office 365 into using a Trusted Location (versus lowest ENABLE MACRO setting we used for years with a Office 2010) and it’s encrypted as well, so maybe all these variables contribute to the very vague error messages. Thank YOU. Your hint about the Trusted Location help us think outside the box.

  8. #8
    maxis is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2013
    Posts
    30
    Turns out the solution was to UNPROTECTING the workbook and workbook sheets and the problem line of code stopped being an issue. What changed from Office 2010 to Office 365 for this issue is STILL unclear, but we were forced with Office 365 into using a Trusted Location (versus lowest ENABLE MACRO setting we used for years with a Office 2010) and it’s encrypted as well, so maybe all these variables contribute to the very vague error messages.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-23-2024, 09:04 PM
  2. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  3. "Run Time Error 3146, ODBC call failed" :(
    By selvakumar.arc in forum Access
    Replies: 0
    Last Post: 12-04-2014, 01:57 PM
  4. ODBC -- Call Failed error
    By Coffee in forum Access
    Replies: 3
    Last Post: 07-08-2011, 10:34 AM
  5. Help: ODBC call failed -952 SQLSTATE 57014 error
    By accessnewbie in forum Access
    Replies: 2
    Last Post: 02-02-2011, 01:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums