Solved One Application for different Access databases

[silentwolf]

Hi guys,

I got different Access Databases and was wondering if I can make one Application to Access those different Databases.
I am aware of linking data to another database and so on but is it possible to have say one Database to access different Databases something like one Form that can open those individual Databases including accessing those forms and reports?

Thanks for advice

Cheers
Albert

 

[plog]

No.

You cannot access the forms/reports/queries/modules/macros of AccessDatabase1 from AccessDatabase2 or from Application1.

You can access the tables of AccessDatabase1 from AccessDatabase2 and from Application1.

You can have AccessDatabase1 open and run its forms/reports/queries/modules/macros by having AccessDatabase2 or Application1 tell the operating system to do so.

 

[silentwolf]

Hi thanks for your input!

Ok understand!

Cheers

 

[MajP]

You cannot access the forms/reports/queries/modules/macros of AccessDatabase1 from AccessDatabase2 or from Application1.

Not sure I understand that statement, unless @plog has some caveat. You cannot access directly from the navigation pane if that is what he means, but of course you can open Forms, Reports, Queries, and code from an external database. People routinely make library databases to do this.

To make it easier put something like this in each external database

Public Function openForm(formName, Optional View As AcFormView = acNormal, Optional FilterName, Optional WhereCondition, Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, Optional WindowMode As AcWindowMode = acWindowNormal, Optional OpenArgs) As Access.Form
    DoCmd.openForm formName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
    Set openForm = Forms(formName)
End Function
Public Function openQuery(QueryName As String, Optional View As AcView = acViewNormal, Optional DataMode As AcOpenDataMode = acEdit)
    DoCmd.openQuery QueryName, View, DataMode
End Function
Public Function openReport(ReportName As String, Optional View As AcView = acViewPreview, Optional FilterName, Optional WhereCondition, Optional WindowMode = acWindowNormal, Optional OpenArgs) As Access.Report
    DoCmd.openReport ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs
    Set openReport = Reports(ReportName)
End Function

Then from the main db add a reference to the external dbs.

Then from the main db you can simply open the forms, reports, and queries using those functions and referencing through the external project.
Something like this in my example for the buttons.

Private Sub cmdExt1_Frm_Click()
 external1.OpenForm "frmProductList"
End Sub

Private Sub cmdExt1_Query_Click()
  external1.OpenQuery "qryGreaterThan100"
End Sub

Private Sub cmdExt2_Frm_Click()
 external2.OpenForm "frmProductList"
End Sub

Private Sub cmdExt2_Report_Click()
 external2.OpenReport "rptProducts"
End Sub

This image shows an open form from an external db appearing just like any internal form. The other buttons open forms, reports and queries from 2 different external DBs.

This is all very doable, but I highly doubt you should need to do this. It is not very common to need to do something like this.

 

[silentwolf]

Hi MajP,
thanks for your explaination!

Ok well I thought for my business it would be nice to have one "Application" to do it all but if that is not a good practice I will overthink it again )

Cheers

Albert

 

[MajP]

Hi MajP,
thanks for your explaination!

Ok well I thought for my business it would be nice to have one "Application" to do it all but if that is not a good practice I will overthink it again )

Cheers

Albert

It is doable, but it would probably end up creating and not saving work. You can always import other forms, reports, and queries from other DB. I have not heard of many people routinely doing this, but in theory it could have advantages.

 

[silentwolf]

Ok thanks MajP,

I will look into it a little more and see how I will go about it! But thanks for explaining

Cheers

 

[Isaac]

But I think your high-level thought process is very good. Maybe the way you have described it here is unusual & unnecessary/not the best idea (I agree with that), but your overall thought of combining many potential db's into one master db is (I think) a good idea, and something I have had vast amounts of success & user satisfaction with.

When I started at Company X, I'd make a database for different purposes, until they had about 6 - 8 different ones. Eventually I created one called "X Department Operations", with nice tabs and permission-based access , which they obviously preferred rather than going into a million different Access files. Overall, easier to manage too - and goes right along with how to develop in Access without making I.T. mad. (see #6 on this post).

One of the things I hate is taking over a position at a department where there are 50 Access databases, each one for a tiny purpose - and I.T. hates that too, so avoid doing it to avoid getting on their bad side.

 

[The_Doc_Man]

I'm going to add a technical comment. You are talking about having a single app that deals with several database files at once. You must remember that each thing you open - whether table, query, form, report, or module - involves a temporary connection between the underlying or "master" app and any other files. This temporary connection acts like an open file (see next paragraph) and consumes file-oriented resources. Of course how many it consumes depends on the design of the various DBs and of the master DB. However, it would be VERY EASY to open so many of those handles that you trigger an "out of resources" run-time error. The databases you open include recordsets based on queries and tables, and forms include not only a .Recordset but a .RecordsetClone - two more file oriented channels.

This file-oriented load is because Access depends on Server Message Block (SMB) which is the Windows File and Printer Sharing protocol. Access uses SMB as a way to look at PART of the app file. It counts against file resources. There are, indeed, ways to open multiple databases and to deal with them in one way or another, but remember that it is ALL controlled by the Windows session that opened the master database. It is the one that has the greatest memory requirements. That is the session whose file handles count as a consumable resource.

MajP is absolutely right that you can open multiple DBs this way - but tread cautiously because of resource issues that will clog up the master session if you keep on opening DBs. Let's just say you can do this - but if you start getting "resource", "file handle", or "memory" issues, you've gone too far and, in fact, should step back a bit.

 

[MajP]

But I think your high-level thought process is very good. Maybe the way you have described it here is unusual & unnecessary/not the best idea (I agree with that), but your overall thought of combining many potential db's into one master db is (I think) a good idea, and something I have had vast amounts of success & user satisfaction with.

When I started at Company X, I'd make a database for different purposes, until they had about 6 - 8 different ones. Eventually I created one called "X Department Operations", with nice tabs and permission-based access , which they obviously preferred rather than going into a million different Access files. Overall, easier to manage too - and goes right along with how to develop in Access without making I.T. mad. (see #6 on this post).

One of the things I hate is taking over a position at a department where there are 50 Access databases, each one for a tiny purpose - and I.T. hates that too, so avoid doing it to avoid getting on their bad side.

@Isaac Where you opening objects from external databases? Or did combine all the databases into a master and then compartmentalize the master for specific users? Curious if you are opening external objects, what the cons are if any? I know this can be done, but never actually deployed anything that way or have heard much discussion on doing this.

 

[Isaac]

Where you opening objects from external databases? Or did combine all the databases into a master and then compartmentalize the master for specific users?

The latter - simply combining many 'uses' into a single database. (I mean split of course, but just one db overall).

 

[silentwolf]

Hi guys,

thanks for the discussion this is bringing some light into my question )

However as I read through your thougts I was wondering to simpify it can you just create some kind of Switch Board in the "Master DB" to
open one DB work with it and if finished just close it?
And if you need to work on a different part of the business just open the other DB?
Just like a normal Switchboard in Access but instead of getting into different parts of each DB just open the DB's as a whole?

Hope this makes sense?

Just to keep it in one place? Not sure if that is a good idea but just wondering.

 

[isladogs]

Yes you can but whether it is worth the time needed to set this up is another matter.

 

[Pat Hartman]

Ok well I thought for my business it would be nice to have one "Application" to do it all but if that is not a good practice I will overthink it again )

Unless you have one employee who does everything, it is probably best to maintain separate FE's to have better security control.

 

[silentwolf]

Ok I understand!

Cheers Pat )