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?
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