none
Open MS Access Report from Visual Studio RRS feed

  • Question

  • This was all working perfectly a year ago.  Then I upgraded Office to 2013.  Subsequently I needed to make a change to the Access DB and now when I try to call the Access reports, I get errors.  The code is as follows:

    Imports Access = Microsoft.Office.Interop.Access
    Imports System.Runtime.InteropServices
    
    Public Class frmReports
    
        Public db_path As String
    
        Dim oAccess As Access.application
    
        Private Sub btnSumByPlan_Click(sender As Object, e As EventArgs) Handles btnSumByPlan.Click
    
            oAccess = CreateObject("Access.Application")
            oAccess.visible = True
            oAccess.opencurrentdatabase(db_path)
            oAccess.docmd.openreport(ReportName:="rptSumByPlan", view:=Access.AcView.acViewPreview)
    
        End Sub
    End Class

    I have the following References selected:

    • InteropExtension 1.0 Type Library
    • Microsoft Access 15.0 Object Library
    • Microsoft ActiveX Data Objects 2.8 Library
    • Microsoft Office 15.0 Object Library
    • Microsoft Visual Basic for Applications Extensibility 5.3

    When I run it, I get the following error:

    An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in SPJ CRVM.exe
    
    Additional information: Error HRESULT E_FAIL has been returned from a call to a COM component.

    It opens Access but fails on the oAccess.docmd.openreport line.

    Thanks for any help you can give me!

    • Moved by Weiwei Cai Friday, January 8, 2016 9:58 AM not VS IDE issue
    Thursday, January 7, 2016 9:36 PM

Answers

  • Thank you all for your responses.  Turns out the issue is in the oAccess.OpenCurrentDatabase() line.  When I hard coded the location of the Access db, it works perfectly. I don't understand what changed between last year and now.

    So now the question becomes is there a way to dynamically point to where the database is without hard coding it?  When I make the OleDb connection to read and write to the database, I can use

    Data Source=|DataDirectory|

    to make the location dynamic.  But I can find any way to do so with this or to capture that location into a variable.

    Tuesday, January 12, 2016 2:09 PM
  • Can you place your Access database in the same folder as your Visual Studio executable? You could then use the Startup Path from VB:

    strFullDBPath = Application.StartupPath & "\" & "YourDatabaseName.accdb"


    Miriam Bizup Access MVP

    Tuesday, January 12, 2016 3:14 PM

All replies

  • Hi kmcphail,

    This forum is discuss Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor.

    Since your problem is related to Office development, I help you move this thread to Access for Developer forum for a better help.

    Best Regards,
    Weiwei

    Friday, January 8, 2016 9:57 AM
  • I'm not sure why this is not considered a Visual Studio issue.  The report object works fine in Access; it is trying to open it from Visual Studio that creates the error.
    Friday, January 8, 2016 2:17 PM
  • Hi kmcphail,

    I made a test with your steps, but I failed to reproduce your issue.

    I suggest you create a new winform project with this code and reference to check whether this issue still exists.

    Also, I suggest you change the com reference to .net reference.

    If this issue still exist in a new project, I suggest you try the code under a new PC with Office 2013 installed.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 11, 2016 5:36 AM
  • Try forcing the VS project to be x86 (32 bits).

    Also keep in mind that you cannot create an instance of the runtime (you need full edition). (create object might work, but then the instance will shut down if runtime version).

    And also keep in mind that if you using click to run, then that is a virtualized edition of Access/office, and again this “can” cause issues.

    Does the code work without running the report? In other words, you are displaying a copy of Access. Since the access object goes out of scope at the end of the button, then that also may well be an issue.

    On the last line of your vb.net code, right after the docmd.OpenReport, place a simple msgbox line of code, say like this:

    Msgbox ("Wait")

    That way, you can at least see if Access becomes visible. In fact don't launch the report - just try opening the report manually. After you close the report, and click ok to the vb.net msgbox, Access should go out of scope (shut down).

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Monday, January 11, 2016 11:35 PM
  • Thank you all for your responses.  Turns out the issue is in the oAccess.OpenCurrentDatabase() line.  When I hard coded the location of the Access db, it works perfectly. I don't understand what changed between last year and now.

    So now the question becomes is there a way to dynamically point to where the database is without hard coding it?  When I make the OleDb connection to read and write to the database, I can use

    Data Source=|DataDirectory|

    to make the location dynamic.  But I can find any way to do so with this or to capture that location into a variable.

    Tuesday, January 12, 2016 2:09 PM
  • Can you place your Access database in the same folder as your Visual Studio executable? You could then use the Startup Path from VB:

    strFullDBPath = Application.StartupPath & "\" & "YourDatabaseName.accdb"


    Miriam Bizup Access MVP

    Tuesday, January 12, 2016 3:14 PM
  • Thanks!  It is in the same folder.  I'll try that.
    Tuesday, January 12, 2016 3:15 PM
  • Hi kmcphail,

    Have your issue been resolved? If you have, I suggest you mark the helpful reply as answer to close this thread, if not, please feel free to let us know your current issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, January 15, 2016 6:19 AM