Access.Application connection to WorkGroup Secured Database

[GohDiamond]

:banghead:I'm trying to connect to a 2nd MS Access database in order to get to the Forms collection

When I'm connecting to a 2nd database with no security, no password, I can use:

strPath = "C:\Path\To\remote\Database.accdb"
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase (strPath)

For Each obj In appAccess.CurrentProject.AllForms

BUT HOW Do I get connected to a Secured Database that uses a /workgroup file that is not System.mdw
(btw: I know the name and where that secure mdw is)

I need to pass the valid Username and password to Logon AND be able to access the .AllForms collection.
If I use dbx as database and define a PrivDBEngine and Workspaces I can get logged on and into the 2nd database.

Dim dbe As DAO.DBEngine
Dim ws As DAO.Workspace
Dim dbx As DAO.Database

Dim SysDB As String, username as string, pswd As String

'https://arstechnica.com/civis/viewtopic.php?t=437292

    Set dbe = New DAO.PrivDBEngine
    ' Add variable for Database string
    SysDB = "c:\workgroup\securefile.mdw"
    dbe.SystemDB = SysDB

    ' Enter WorkGroup Credentials
    
    username = InputBox("Enter your PND User Name", "Credentials", "")
    dbe.DefaultUser = username

    pswd = InputBox("Enter your PND password", "Credentials", "")
    dbe.DefaultPassword = pswd

    Set ws = dbe.Workspaces(0)
    Set dbx = ws.OpenDatabase(dbFullPath)

BUT I can't seem to get to the forms collection that way. dbx doesn't offer a path to Forms. TableDefs, sure. QueryDefs, sure. but no forms...

Is this possible? OR is there a better way to do this from outside the target database.

Thanks in advance!
Goh

 

[The_Doc_Man]

Well, my question is whether there ARE any forms in the Forms collection if opened that way. The Forms collection is supposed to be the forms that are open, but when you open the database this way (programmatically via VBA), no forms SHOULD be open - unless you are opening an already opened, shared front end or shared non-split database. Did you want the AllForms collection, perhaps?

If you can see TableDefs, you should be able to see other entities using the same type of scope-qualifier syntax. But you've got to use the right name for the collection.

 

[Cronk]

The names of the forms in an external database can be found in the database's forms container

dim ctr as container
dim n as integer

set ctr =dbe.Containers("forms")
for n = 0 to ctr.documents.Count -1
   debug.print ctr.documents(n).name
next

 

[isladogs]

You've been told how but I'm wondering WHY you want to do this?
Do you just want a list of forms so you can select one or more to import?
Or what?

 

[GohDiamond]

Doc_Man
1) yes there are quite a few forms in the remote database that I'm connecting with
2) I want to get the names of the forms, so that I can open the form and programmatically pull other information like recordsource, controls and control types etc
3) That's what I was trying to find out, how to get to the "...right name for the collection"

Cronk
Thanks for pointing out the answer to #3 above, I'm going to look deeper into that, I tried .container but I guess I didn't set it up correctly

Ridders
The reason is: I'm building custom Documentation for a database to include a dictionary of sorts for general knowledge, development, and migration purposes. It's complicated but, there has never been any documentation on this database before. I know there is a built in documenter but it is not helpful in this case except as a cross check for only some of the defining data I want to pull.

THANKS FOR ALL YOUR SUPPORT! I'll let you know what happens and if I have any other questions.

CHEERS!
Goh

 

[isladogs]

Beware of reinventing the wheel....

The documenter is very detailed but not the easiest tool to use.

There is a very easy way of getting a list of all database objects and their properties. Use the hidden, system table MSysObjects
See this example database I created which will do that for you.
https://www.access-programmers.co.uk/forums/showthread.php?t=295597

If you want a utility to list all fields, record sources etc, look at free addins like VTools. Before I was aware of this, I created my own version with similar features. It was a LOT of work.

There are also several excellent utilities you can purchase like MZ Tools

If you want a set of statistics about your database, have a look at post 2 in this thread. http://https://www.access-programmers.co.uk/forums/showthread.php?t=296860

I expect other forum members will have their own suggestions

I'm not suggesting you abandon you task. However I do suggest you look at using similar items that already exist as these will save you many hours of development work.

Good luck

 

[GohDiamond]

Ok Back for more help

I can get to the names of the forms that way
dbx.containers("Forms").documents(Number from loop).name

BUT I can't open the document/Form to get information about the controls.
I need to do something like this:

Dim appAccess As Access.Application
 Set appAccess = CreateObject("Access.Application")
 appAccess.OpenCurrentDatabase "C:\MyPath\DataBaseName.mdb"
appAccess.DoCmd.OpenForm "FormName"

And I run into that same issue of getting into a workgroup secured database on appAccess.OpenCurrentDatabase

If I can take over the dbx with the created appAccess object that would allow me to Open the Forms and loop thru the controls.

So the goal is to logon to the workgroup secured database with something like:

appAccess As New Access.Application

AND get to

appAccess.CurrentProject.AllForms

OR any alternative that will let me open those forms and loop thru the form controls

Looking forward to hearing back
Cheers,
Goh

 

[isladogs]

See my previous reply.
Start by using the tools Access provides.

You can link to the MSysObjects table in an external database.
To do so, set hidden objects visible in the host database you are working on.

 

[GohDiamond]

Thanks Colin
I can't use any outside tools or addins, this is a state government controlled project. You know how that is. It would take forever to get funds or approval to use anything I didn't create. I'll look into the links you've suggested, but still hoping to figure out how to gain control of those forms.

Goh

 

[static]

You can write your own addin.
Make all subs public functions in standard modules in one database.
In VBA Editor change project name to 'myaddin' or whatever.
Link that db as a reference in the one you are querying.
Call your functions.

Private Sub Command0_Click()
    myaddin.doSomethingCool
End Sub

Might help, might not, but I think it should run as if it's part of the db you are working with so might get around some of the issues.

 

[isladogs]

Good point Static. For info, the free VTools addin is actually linked in much the way described.

And there's nothing to stop you using MSysObjects or including code like the two examples in the links given earlier