Run app.SaveAsText remotely in a secured Ms-Access Database

Question

I have an MS-Access Database which is dedicated for managing my other MS-Access Databases. Let's call it: AppManager

It stores info regarding other Dbs I've developed.

All of them have different Workgroup file(s) which is used to implement security-permissions per User/Group-

The location of .MDB/.MDW is stored within my AppManager along with the username & password (of the administrator)

Notes:

AppManager is for my own benefit -Since I'm the administrator/developer of those Dbs-
.mdb is because in our firm we're still using MS-Access 2k -to be specific 2003-
.mdw is the security workgroup file (without it any secured-Db cannot be opened)

Now I've incorporated miscellaneous functionalities in AppManager: such as viewing groups/users info, resetting a password, other custom features, etc.

I'm trying to add a new feature to AppManager which will enable me to export all objects within any of these Dbs remotely -from inside AppManager-

Here's this code:

    Function ConnectSecuredDB 
    ( MDBPath As String, MDWPath As String, UserName As String, Password As String) As Boolean

        Dim wsp As Workspace, db As Database, objEngine As DBEngine

        Set objEngine = New PrivDBEngine
        
        objEngine.SystemDB = MDWPath
        Set wsp = objEngine.CreateWorkspace("New", UserName, Password, dbUseJet)
        
        Set db = wsp.OpenDatabase(MDBPath)
    
        'I Do some stuff here...
        
        db.Close
        wsp.Close
        
        Set db = Nothing
        Set wsp = Nothing
    End Function

I need to combine the above-code with the Application object which has Docmd, SaveAsText methods

like this code taken from MSDN, but the problem that it doesn't deal with secured-mdw Databases-:

    Dim appAccess As Access.Application 
       
        Sub DisplayForm() 
        
        Dim strDB as String 
        
        ' Initialize string to database path. 
        Const strConPathToSamples = "C:\Program " _ 
    & "Files\Microsoft Office\Office11\Samples\" 

        strDB = strConPathToSamples & "Northwind.mdb" 

        ' Create new instance of Microsoft Access. 
        Set appAccess = _ 
        CreateObject("Access.Application") 

        ' Open database in Microsoft Access window. 
        appAccess.OpenCurrentDatabase strDB 

        ' Open Orders form. 
        appAccess.DoCmd.OpenForm "Orders" 
        End Sub

in my case I would have to replace the last line with:

        With db.Containers("Modules")
          For Each doc In .Documents
            appAccess.SaveAsText acModule, doc.Name, "C:\temp\" & doc.Name & ".mod"
          Next doc
        End With
        'and repeat it with other objects reports, macros, ...

my ConnectSecuredDB function doesn't incorporate an Access.Application object

Any ideas of how to accomplish this?

score 1 · Accepted Answer · answered Jul 24 '17 at 10:55

After searching more extensively I managed to come up with this solution Inspired by "An Article" (I've added my touch)

Code:

Dim app
Dim db As Database, doc As Document
Dim DBPath As String, MDWPath As String
Dim UserName As String, Password As String
Dim strRunCmd As String, msaccessPath As String

Set app = CreateObject("Access.Application")

DBPath = "D:\My Documents\myDb.mdb" 'full path of DB
MDWPath = "D:\My Documents\Secured.mdw" 'full path of Workgroup file
Username = "your User id" 'a legitimate user account (in the workgroup)
Password = "user's password" 

msaccessPath = "C:\Program Files\Office2003\Office11\msaccess.exe"

strRunCmd = """" & msaccessPath & """ """ & DBPath & """ /WRKGRP """ & MDWPath & """ /User """ & UserName & """ /Pwd " & Password
Shell strRunCmd, vbMaximizedFocus

Do
    Set app = GetObject(DBPath)
    DoEvents
Loop While app Is Nothing

Set db = app.CurrentDb

bkPath = "C:\Temp\bkDB\"
With db.Containers("Forms")
   For Each doc In .Documents
       app.SaveAsText acForm, doc.Name, bkPath & doc.Name & ".frm"
   Next doc
End With
'repeat the loop to other object types such as reports, modules, macros

'cleanup
app.CloseCurrentDatabase
app.Quit
Set app = Nothing

db.Close
Set db = Nothing

Run app.SaveAsText remotely in a secured Ms-Access Database

1 Answers1