Repository for common forms across multiple .mdb's

[topher23]

I have several forms and code subroutines that are common across the many databases that I support. The solution my predecessor, who built many of these databases, used was to put a copy of the forms and code into each database.

Over time, I've pulled most of the duplicated code out of the front-end applications and consolidated it into an .mdb used as a shared code repository. The code repository is then referenced in each application that it's needed in. This works great for code - not so great for forms.

Does anyone know of a way that I can do the same thing with the duplicate forms?

[ADezii]

@topher23
If you use the same Code Routines across many Databases, then you should consider creating a Library Database(s) (*.mda). These Databases contain Code Modules shared by a number of Applications and they relieve you from having to create and maintain a separate Version of a Function in each Database where it is called. To reference these Library Databases:

1. Tools
2. References
3. Browse
4. Select "Add-ins (*.mda)"
5. Locate your Library Database
6. OK

[topher23]

Right. If you read a bit closer you'll see that's exactly what I've already done. Originally, I named the file .mda, but I decided that the name was confusing as the database wasn't exactly an add-in, but a code repository, or library. When I hear library, I think books, so I don't use the term for db's. Sorry for the confusion there. Anyway, Access will reference the file as an add-in whether you name it .mda or .mdb, it doesn't really care.

The point is, is there any way to do it with forms? I looked into creating a true .mda add-in, complete with system tables and registry entries, but all indications are that simple forms still wouldn't work properly. Or am I reading too much into it, and creating an add-in is the way to go?

[MMcCarthy]

I'm not sure if it could even be done with an add-in as I think forms need to be stored locally in a database. However, since I'm not sure I'll put out a call to some of our experts to see if anyone has any ideas.

Mary

[missinglinq]

I agree with Mary's assessment here! You're referencing code in libraries, but forms have to physically be present in a database. I think the closest you can come would be to have a database where you stored these forms and simply import a copy into you db that is currently under development.

Linq ;0)>

[ajalwaysus]

@missinglinq
I also agree.
I have a tool I use right now that acts as a centralized DB for all my forms, references, and modules, which also houses all my DBs. So when I deploy any new DBs or make a changes to a "Shared" module, reference, or form, I can push out the changes through my central DB. But bottom line, I still have to store them in a DB.

-AJ

[ADezii]

I liked the concept proposed by topher23 so much, that I created code contained within a single Form that will:

• Open an Office File Dialog filtered for Access Databases only (*.mdb).
• Once a Database is selected, a List Box on the Form (MultiSelect = Extended, RowSourceType = 'Value List') is populated with the Names of all Forms residing in the External Database.
• Simply select 1 or several Forms as so desired.
• Click on a Command Button to Import the Selected Form(s) into the Current Database.
• For any Project you create, include this Form Template to automate the Form Import process.
• If anyone is actually interested, let me know and I'll post the code.

[topher23]

Adezii, I like your thinking. You may even be able to set it up so you can push the forms from one database out to a completely different database using another list box. That would be some serious automation.

Well, as for my original question, I was able to slap together a sort of work-around. I used CreateObject to initiate my "library" in another instance of Access. For some reason, it hadn't occurred to me previously that CreateObject could be used to create more Access instances. Anyway, referencing that instance, I was able to pull up the form I wanted, manipulate the recordsource, and even populate data in the form from the original database based on data entered into and displayed on the form in the other instance. In my "calling" form, I used a loop to make sure the second form was closed before the calling subroutine killed the instance.

I won't post this code, since I figure it's pretty straightforward for anyone with a bit of experience and the ability to look up CreateObject in the help file (plus it's really ugly right now, as I've only just been testing it and I'm not certain it won't break).

Even more fun, I used a function that I found on the webs a while back to hide the second access window while still displaying the form (form has to be set to popup in order for this to work properly), making the transition between different instances of Access totally invisible to the user.

For anyone interested, this is the code that hides the Access window.

Option Explicit
 
Private Declare Function IsWindowVisible Lib "User32" (ByVal hWnd As Long) As Long
Dim dwReturn As Long
 
Const SW_HIDE = 0
Const SW_SHOWNORMAL = 1
Const SW_SHOWMINIMIZED = 2
Const SW_SHOWMAXIMIZED = 3
 
Private Declare Function ShowWindow Lib "User32" (ByVal hWnd As Long, _
     ByVal nCmdShow As Long) As Long
 
Public Function pfnAccessWindow(Optional Procedure As String, Optional SwitchStatus As Boolean, Optional StatusCheck As Boolean) As Boolean
If Procedure = "Hide" Then
    dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
End If
If Procedure = "Show" Then
    dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
End If
If Procedure = "Minimize" Then
    dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMINIMIZED)
End If
If SwitchStatus = True Then
    If IsWindowVisible(hWndAccessApp) = 1 Then
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
    Else
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
    End If
End If
If StatusCheck = True Then
    If IsWindowVisible(hWndAccessApp) = 0 Then
        pfnAccessWindow = False
    ElseIf IsWindowVisible(hWndAccessApp) = 1 Then
        pfnAccessWindow = True
    End If
End If
End Function

[NeoPa]

Nice work Topher.

I've never really played much with different types of Access files, but I hed a question the other day about MDEs and thought I'd experiment a little.

When I read this I decided I'd do the same with MDAs (this afternoon). Fun isn't it. I just wish I'd bothered earlier. It's only recently that I've done much work creating new databases and requiring much of the functionality to be available that I had previously developed, but this is certainly worth knowing about. I'm sure I'll play more soon.