locked
VB.net Microsoft.Office.Interop.Access.Application with a workgroup file (system database). Bypass username and password log in window! RRS feed

  • Question

  • I am super stuck here.  I have a vb.net program that successfully opens an Access report and exports it to a PDF.  It's a windows form app and the database is protected by a workgroup file (MDW). When I call

    accObj.OpenCurrentDatabase(sDevServPath, False)

    The Logon window appears and asks for the username and password. This was a bit annoying because I wanted it to be fully automated, but I'm always at the computer when it runs and just entered them manually. However, now I want to run this report in a console app that will get triggered in the middle of the night by a scheduled task. I won't be there to enter the username and password. Here's the code that works in my Windows Form app.  I get prompted for the UN and PW at OpenCurrentDatabase

            accObj = New Microsoft.Office.Interop.Access.Application
            accObj.SetDefaultWorkgroupFile(Path:=PathToMyMDW_File)
            accObj.OpenCurrentDatabase(sDevServPath, False)
    
            Dim sWC As String = "filenum = '" & sFileNum & "'"
    
            'Open the report in preview mode using a WhereCondition: 
                    accObj.DoCmd.OpenReport(ReportName:="rCaseReview", _
                        View:=Microsoft.Office.Interop.Access.AcView.acViewPreview, _
                        WhereCondition:=sWC)

    I have tried:

     accObj.DBEngine.DefaultUser = sUn
     accObj.DBEngine.DefaultPassword = sPW


    But it prompts me for the UN and PW when that first line runs.

    I've also tried

     Threading.Thread.Sleep(5000)
     My.Computer.Keyboard.SendKeys(sPW & "{Enter}")

    It doesn't want to work. Please help!

    Please help!



    • Edited by Alex Z_ Thursday, September 20, 2018 8:46 PM fix formatting
    Thursday, September 20, 2018 8:07 PM

Answers

  • Oh boy!

    You have to use shell().

    To my knowledge you cannot do this with the application object model in access. Many have attempted this, but I not seen a working solution.

    The ONLY work around is to shell() out a copy of access, and THEN use GetObject() in .net.

    Workgroup security and the knowledge of the "gurus" in the Access community are hard to find - the folks that used this technology are few right now.

    I am simply not aware of a approach other then shelling out to access (with workgroup information), and then in .net using GetObject() to fetch the running instance of Access.

    This is thus a bit “ugly”

    I can only say that the “added” bonus is this means your code you will ALSO work with the Access runtime.

    (interop code that creates a instance of Access WILL fail with runtime, but not with this approach).

    So at the end of the day, you at least get code that will work with both full version of access, and that of the runtime.

    So now, your .net code will become this:

       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim strExe As String = """C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe"""
            Dim strFile As String = " ""C:\RidesDev\SkiRides\RidesXP\RidesXP.mdb"" "
            Dim strWorkGroup As String = " ""C:\RidesDev\SkiRides\RidesXP\RIDES.MDW"" "
    
            Dim strUser = "RidesAdmin"
            Dim strPass = "snowsnow"
    
            Dim strParms = strFile & " /wrkgrp" & strWorkGroup & "/user " & strUser & " /pwd " & strPass
    
            Shell(strExe & strParms, AppWinStyle.NormalFocus)
    
            Dim objAccess As Object
    
            ' we have to wait for access to finish loading
            ' else GetObject will fail.
            Dim i As Integer
            For i = 1 To 20
                Try
                    objAccess = GetObject(, "Access.Application")
                    Exit For
                Catch ex As Exception
                    Application.DoEvents()
                    System.Threading.Thread.Sleep(200)
                End Try
            Next
            If objAccess Is Nothing Then
                MsgBox("Could not load access")
                Exit Sub
            End If
    
            'now you can do whatever
            objAccess.docmd.openReport("rptHotelList")
    
            objAccess.application.quit()
    
        End Sub

    This I guess means you can dump the access interop referances. However, if you want to keep intel-sense, then keep your declare as access.applcation, but simply set the object with GetObject as per above.

    so you can do this, but I am not aware of any possible "alternate" solution.

    I will ALSO VERY (BUT VERY VERY) strong suggest that you fix your defaulted workgroup join command that you executed. (this means you have re-set the DEFAULT workgroup file that Access is using. If you launch ANY mdb file, then the long time code that prompts the user for a logon will NOW kick out a logon prompt (and that will kill your automation code). Not fixing this issue will ALSO interfer in your testing and debugging process. (I am trying to save you starving and getting sick here - you need to fix this issue and un-do the setting change you made).

    And you can't set the workgroup file = "" or empty. While 2010 does NOT really use workgroup, it STILL is required!!!

    So to undo that mess, launch access, (even a accdb)

    Hit ctrl-g, and in the debug window type in:

    docmd.RunCommand acCmdWorkgroupAdministrator

    This will display that “setting” you  changed. You want to hit “join” and browse to:

    C:\Users\AlbertKallal\AppData\Roaming\Microsoft\Access\

    And select system.mdw.

    You have to replace “my name” above with yours.

    If you don’t do this, then your messing around with access testing will “stick”. You set a defaulted setting in access.

    Also, many do suggest using "process" in place of shell(), it is a newer library. However, the "wait" issue as per above (get access + app loaded) is still required. I can post "process" use code in place of shell() if you wish, but it only 2-3 more lines of code, and really don't help or change much here

    And, with a "happy" and "sad" smile, good luck!

    I really wish I could give you something better, but I can "expand" a bit more as to why you not find an alterative to the above  - and my post is already too long!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada



    • Edited by Albert D. Kallal Friday, September 21, 2018 8:43 AM
    • Marked as answer by Alex Z_ Friday, September 21, 2018 5:35 PM
    Friday, September 21, 2018 8:38 AM

All replies

  • Check out DBEngine.CreateWorkspace.

    Then use Workspace.OpenDatabase.


    -Tom. Microsoft Access MVP

    Friday, September 21, 2018 3:58 AM
  • Oh boy!

    You have to use shell().

    To my knowledge you cannot do this with the application object model in access. Many have attempted this, but I not seen a working solution.

    The ONLY work around is to shell() out a copy of access, and THEN use GetObject() in .net.

    Workgroup security and the knowledge of the "gurus" in the Access community are hard to find - the folks that used this technology are few right now.

    I am simply not aware of a approach other then shelling out to access (with workgroup information), and then in .net using GetObject() to fetch the running instance of Access.

    This is thus a bit “ugly”

    I can only say that the “added” bonus is this means your code you will ALSO work with the Access runtime.

    (interop code that creates a instance of Access WILL fail with runtime, but not with this approach).

    So at the end of the day, you at least get code that will work with both full version of access, and that of the runtime.

    So now, your .net code will become this:

       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim strExe As String = """C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe"""
            Dim strFile As String = " ""C:\RidesDev\SkiRides\RidesXP\RidesXP.mdb"" "
            Dim strWorkGroup As String = " ""C:\RidesDev\SkiRides\RidesXP\RIDES.MDW"" "
    
            Dim strUser = "RidesAdmin"
            Dim strPass = "snowsnow"
    
            Dim strParms = strFile & " /wrkgrp" & strWorkGroup & "/user " & strUser & " /pwd " & strPass
    
            Shell(strExe & strParms, AppWinStyle.NormalFocus)
    
            Dim objAccess As Object
    
            ' we have to wait for access to finish loading
            ' else GetObject will fail.
            Dim i As Integer
            For i = 1 To 20
                Try
                    objAccess = GetObject(, "Access.Application")
                    Exit For
                Catch ex As Exception
                    Application.DoEvents()
                    System.Threading.Thread.Sleep(200)
                End Try
            Next
            If objAccess Is Nothing Then
                MsgBox("Could not load access")
                Exit Sub
            End If
    
            'now you can do whatever
            objAccess.docmd.openReport("rptHotelList")
    
            objAccess.application.quit()
    
        End Sub

    This I guess means you can dump the access interop referances. However, if you want to keep intel-sense, then keep your declare as access.applcation, but simply set the object with GetObject as per above.

    so you can do this, but I am not aware of any possible "alternate" solution.

    I will ALSO VERY (BUT VERY VERY) strong suggest that you fix your defaulted workgroup join command that you executed. (this means you have re-set the DEFAULT workgroup file that Access is using. If you launch ANY mdb file, then the long time code that prompts the user for a logon will NOW kick out a logon prompt (and that will kill your automation code). Not fixing this issue will ALSO interfer in your testing and debugging process. (I am trying to save you starving and getting sick here - you need to fix this issue and un-do the setting change you made).

    And you can't set the workgroup file = "" or empty. While 2010 does NOT really use workgroup, it STILL is required!!!

    So to undo that mess, launch access, (even a accdb)

    Hit ctrl-g, and in the debug window type in:

    docmd.RunCommand acCmdWorkgroupAdministrator

    This will display that “setting” you  changed. You want to hit “join” and browse to:

    C:\Users\AlbertKallal\AppData\Roaming\Microsoft\Access\

    And select system.mdw.

    You have to replace “my name” above with yours.

    If you don’t do this, then your messing around with access testing will “stick”. You set a defaulted setting in access.

    Also, many do suggest using "process" in place of shell(), it is a newer library. However, the "wait" issue as per above (get access + app loaded) is still required. I can post "process" use code in place of shell() if you wish, but it only 2-3 more lines of code, and really don't help or change much here

    And, with a "happy" and "sad" smile, good luck!

    I really wish I could give you something better, but I can "expand" a bit more as to why you not find an alterative to the above  - and my post is already too long!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada



    • Edited by Albert D. Kallal Friday, September 21, 2018 8:43 AM
    • Marked as answer by Alex Z_ Friday, September 21, 2018 5:35 PM
    Friday, September 21, 2018 8:38 AM
  • Thank you very much for your help, Albert.  Although I did run across an article that <g class="gr_ gr_145 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="145" id="145">lead</g> me to this shell code that worked, I appreciate your response and follow up.  I like your For loop approach to connecting the shell to the Access Object, I'm going to add that.  

    Yep, Access databases with workgroup security <g class="gr_ gr_371 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="371" id="371">is</g> old, old, old skool! But it's a legacy app/database that has served us well.  A re-write would take months!!!

    Thanks for the heads up about the workgroup file.  Sure enough, I tried to open a <g class="gr_ gr_657 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="657" id="657">non</g>-<g class="gr_ gr_677 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="677" id="677">mdw</g> access database this morning and poof, the log-on window showed up.  But I found the immediate window fix pretty quickly.  In the vb.net windows form app that I mentioned, I run this near the end

            Try
                accObj.CloseCurrentDatabase()
                accObj.SetDefaultWorkgroupFile(Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) & "\AppData\Roaming\Microsoft\Access\System.mdw")
                accObj.Quit()
                accObj = Nothing
            Catch ex2 As Exception
                MsgBox("There was a problem when resetting the default system.mdw file. See application documentation under the section named Create a system.mdw file if it doesn’t exist." & vbNewLine & _
                "Error number: " & Err.Number & ".  Error description: " & Err.Description, vbCritical, "Error resetting system.mdw")
    
            End Try

    Thanks again!

    Friday, September 21, 2018 5:46 PM
  • Keep in mind that SetDefaultWorkgroup file is a “join” process.

    (and it will cause a logon prompt to occur – and with automation, then we are stuck)

    In fact the DBengine.SystemDB = “path to workgroup” is the correct method to use. It is the same as specify a /workgroup on access start up.

    So DBEngine does have provisions for setting the workgroup file to be used – that SetDefaultWorkgroup is for a “different” purpose. (And as noted, is a permanent setting).

    However, even with the DBEngine.SsystemDB, I am not aware that a com “automated” copy of access can be produced that sets the workgroup file.

    There “might” be a possible solution if you launched a copy of Access, and then had access create another copy. (but that would not work with your inter-op and creating an instance of Access – you would still then have to grab the 2nd  copy of access via GetObject().  )

    Also, I don’t know of a way around the “loop” to wait. We do have the “process” object in .net, and it has a “wait for idle”. However, access has “some” threading, and the wait for idle becomes true before everything is finished loaded – and thus GetObject() will fail.

    You might want to increase that loop time. I have “20” of 200 which = 4 seconds. You could up the 200 milisecond to say 300 or 400 for a longer wait time.

    And one could use shell() with wait, but that would assume the access application prints the report, and then quits – no .net side automation then.

    All in all, I would not at all mind if someone could prove me wrong about setting workgroup in a automatated copy of Access. Such egg on my face would be rather yummy!

    Don’t think a solution exists. So shell() + that “wait loop” is quite much the deck of cards you have to work with.

    Good luck!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada


    Friday, September 21, 2018 8:55 PM
  • Have you tried temporarily programmatically altering the registry value indicating the desired mdw file before creating the object programmatically, and then restoring the registry value (Ver 8/97 this must happen around the CreateObject element, Ver 10/2002 (and I imagine later ver.) around the OpenCurrentDatabase() element). You may need to utilise a copy of the mdw with slightly different permissions so that it will automatically log in and have the rights for your task - normally Admin will have had its permissions revoked, so your mdw copy (which can be run from anywhere unavailable to others) will have a passwordless Admin as part of the Admins group. The registry location can be found by searching for system.mdw for your particular version.

    d

    • Edited by Dv Mk Wednesday, December 11, 2019 5:09 PM
    Wednesday, December 11, 2019 12:05 AM