Open another MDB and path through a parameter

[Maximnl]

In MS Access we can open a form and dysplay specifiek information.
I would like to do the same but if the form is in another MDB. I guess this is not possible to do with VBA...

 

[namliam]

No you cannot display the form, not directly anyway...

Simplest way is to import the form and display the data using linked tables.

 

[Maximnl]

No you cannot display the form, not directly anyway...

Simplest way is to import the form and display the data using linked tables.

Thank you for the hint, this will do the work but I see at least two problems with this approach: 1- complexity of application is constantly growing 2- in multi user environment I simply want to distinguish between multiple front ends depending on the user roles.

I can think of putting a form into onstartup event or autoexec macro but still, certain things have to be done at the run time and I simply want to pass a parameter during the call. I can think of using an extra table for this purpose but ofcourse in multi user environmet I would prefer to do this in a more predictable way.

I wish I had more experience in interfacing between access applications besides the linked tables mechanism.

 

[namliam]

Linked tables are just about the only viable way I know to interact between databases. Anything else is headache....

User security can be done in 1 database without having multiple databases.

 

[MarkK]

What you can do in a single DB is maintain references to multiple DAO.Databases. These will be the CurrentDb's of multiple .mdb files.

Dim OtherDb as DAO.Database
Dim rst as DAO.Recordset
Set OtherDb = OpenDatabase("c:\SomeOtherMDBFile.mdb")
Set rst = OtherDb.OpenRecordset("SELECT * FROM ... ")
Forms("SomeForm").Recordset = rst

You could also do something like this...

Dim app As New Access.Application
app.OpenCurrentDatabase "c:\SomeOtherMDBFile.mdb")
app.DoCmd.OpenForm "SomeForm"

Also, I maintain a library database that contains all functions or classes or forms or anything that is not specific to a customer's data. I reference this database from the user's front end. This keeps the customer's database free of housekeeping and support objects, like search, mail, progress, soundex, etc... Those are all referenced.

But as the mailman says, the easiest is linked tables since you don't have to write any code, and the tables are indistinguishable from those that are native to the local .mdb.

 

[Maximnl]

Thank you labolt for the reply.
I found different ways to reference tables in another mdb (using linked tables and during runtime with code). The second part of your reply is interesting.

Dim app As New Access.Application
app.OpenCurrentDatabase "c:\SomeOtherMDBFile.mdb")
app.DoCmd.OpenForm "SomeForm"

This code opens another MDB from the main application, unfortunately the SomeForm (I use my own form name of course) is not shown. Even stronger, the someothermdbfile becomes locked even if I quit the main application. Any ideas why ?

Another question: You said that you put generic functions in a separate database that you reference. How can you reference functions defined in the library database?

Best regards
Maxim

What you can do in a single DB is maintain references to multiple DAO.Databases. These will be the CurrentDb's of multiple .mdb files.

Dim OtherDb as DAO.Database
Dim rst as DAO.Recordset
Set OtherDb = OpenDatabase("c:\SomeOtherMDBFile.mdb")
Set rst = OtherDb.OpenRecordset("SELECT * FROM ... ")
Forms("SomeForm").Recordset = rst

You could also do something like this...

Dim app As New Access.Application
app.OpenCurrentDatabase "c:\SomeOtherMDBFile.mdb")
app.DoCmd.OpenForm "SomeForm"

Also, I maintain a library database that contains all functions or classes or forms or anything that is not specific to a customer's data. I reference this database from the user's front end. This keeps the customer's database free of housekeeping and support objects, like search, mail, progress, soundex, etc... Those are all referenced.

But as the mailman says, the easiest is linked tables since you don't have to write any code, and the tables are indistinguishable from those that are native to the local .mdb.

 

[namliam]

You cannot open "SomeForm" inside your database, it is always in the other database...

The app.opencurrentdatabase opens a new (hidden) database, with which you may be able to work (check your task manager upon execution of the code you will find a "hidden" access running).
However exchanging data between 2 seperate databases, the best way to go is linked tables... possibly structuraly importing the from from the 'mother' database.

 

[allan57]

Use the following code to open a form in another database:

' Include following in Declarations section of form module.
Option Compare Database
Option Explicit
Dim appAccess As Access.Application

==================================================

Private Sub Form_Load()

OpenAnotherDatabase "c:\DatabasePathAndNameHere", "TheNameOfTheFormYouWishToOpen"

End Sub

====================================================

Public Function OpenAnotherDatabase(strPath As String, strFormToOpen)

Set appAccess = CreateObject("Access.Application")

With appAccess
.OpenCurrentDatabase strPath
.Visible = True
.DoCmd.OpenForm strformToOpen
End With

End Function

 

[namliam]

But that is STILL a seperate database, with a seperate window and seperate application....

 

[allan57]

Yes it is another database, but by passing a opening arguement to the form ie record filter etc, the user is unaware they have open another Db. This allows 1 or more Db's to call another Db's form, thus elimating the need to have several copies of that form in other databases.
Therefore modification of a shared form only takes place in one database.

 

[namliam]

Any semi-serious user will notice the duplicate access programs on the taskbar.... Also allowing them to switch between the two, causing all kinds of problems and things like closing the database(s) ...

If I had forms like this I would probably make 1 central database (not one the users would actually use) then import the form from there on startup of the database to ensure I have the right one active in my database, then simply open it for the user to use.

 

[allan57]

I have over 30 db's with number of forms vary from 10 to over 150 in the Db's. To make one large Db is not feasible. The Db's cover several departments and processes, example lets say a Quality Db user wishes to check back to the a raw material delivery details, its easier to open the form in the Db with an opening parameter. Otherwise they would have to open up the other database, navigate a menu and enter the argument.

 

[namliam]

IF that is OK to do....
I can imagine in segregated duties and all that you may not want to allow one person from dept a, to go into the data for Debt b.

But as long as having seperate databases is not an issue sure this will work, though maybe a bit less streamlined and less clean... the "perfect" way would be to have it all in one big database where everyone can find all data required.... Though that too is sometimes a bit overdone... and/or maybe one big back end database in SQL Server/Oracle/etc is something to consider as well.

 

[allan57]

80% of the Access data is stored in one large MS SQL Server database, the remaining 20% of data is stored over several backend Access databases. The problem arises when you have hundreds of forms/sub forms split over 30+ front-end databases. The method I use is just a quick way for a user to get from one database to another. Using code I also lock-out the first Database until the form/database being called is closed.

 

[rapsr59]

There is a way you can simulate the opening of a Form in another database superimposing the Form on the database that is presently open. And the 2nd hidden database will not even show.

I just tried it and it works great.

Open the second database using the Shell Command setting the window style to vbHide (0).

[FONT=Times New Roman]Call Shell ("MSACCESS.exe “C:\Users\Richard\Documents\dbName.mdb", 0) [/FONT]

When the database opens, in hidden mode, code opens the subject form and it appears that is a form that is part of the original db. All you have to do is set the hidden db Forms Pop Up and Modal properties to true. Design an exit button that closes the hidden db and removes it from memory.

DoCmd.Quit

As I said this simulates opening the form in the original already loaded db.

I created a table named tblOpenFormX with 1 field in the 2nd db. The field is a Yes/No field. I then linked that table to the "Original" db. Just before Shelling the 2nd db code sets the field to true. When the hidden 2nd db opens, Startup code checks if the field is set to True and, if so, opens the Pop Up / Modal form. In this manner when the the 2nd hidden db is opened normally the field is set to false and the PoP Up / Modal form will not open.

I realise this is not what you want to accomplish but it looks and works the same.

Richard