Can you link to a sub-form in a 2nd database??? (Access2K SR1)

Topic

New Reply

We have a form that we use in a number of applications at different clients, and are toying with the idea of putting it into an add-in database so we don’t have to maintain code in each of the 50 or so different databases. However this form uses dynamically linked subforms to display different view of data, so if we were to do that it would require that we link the master form in the add-in database to a sub-form contained in the main database. Does anybody out there know if you can do that trick programatically? Thanks in advance for any advise or help.

Reply | Quote

Replies

Not in anyway that I’m aware of. The SourceObject property expects the object to be within the current database.

If you explain in a bit more detail what it is your trying to do (for example why the subforms must remain in the main database), I might be able to offer other suggestions.

Jim.

Reply | Quote

Your opinion pretty much matches ours. You can work in multiple databases in code, but there doesn’t seem to be any way to set a subform control in an add-in to link to a subform in another database.

The scenario we have is a large form used to select various views of data using several subforms that are linked dynamically. Our clients like this form so much that we currently use it in nearly 20 different databases. However, if we decide we need to make a design change to the form, we now have to change it in 20 different databases. It would be wonderful if we could simply put the form into an add-in and reference the add-in from each of the application databases – we do that with several common forms we use in multiple apps. Hope this explains our objective better. If you have any insights or suggestions we would welcome them.

Reply | Quote

Wendell,

I don’t know if you can link the subform but you can import it.
DoCmd.TransferDatabase acImport, “Microsoft Access”, “Source.mdb”, acForm, “frmToBeImported”, “frmDestination”, False

Hope this help

Reply | Quote

That’s true, but then we would have to load all the subforms we ever want to use into the add-in, not to mention linking to all of the tables used by the subforms, etc. We would be better off to import the main form and it’s associated bits into each of the applications – that way we would only be adding one object (more or less) to each application database. It’s a puzzle, isn’t it.

Reply | Quote

The part I don’t understand is:

“using several subforms that are linked dynamically”

Do you mean you modify the master/child links and change recordsources, change controls or what?

In other words I don’t understand why the whole main/subform combination cannot be in the MDA and called from the main database. Why do the subforms have to reside there?

Jim.

Reply | Quote

I suspect from what Wendell posted earlier that the subforms may be customized for various applications. Putting them in the MDA would require customized add-ins for each application instead.

Reply | Quote

It’s kind of a convoluted subject, but basically we change the sub-form to display different data depending on the view that is chosen on the main form. Thus in one application database we may have three different sub-forms that are linked dynamically to a single sub-form control on the main form, depending on which of the three choices the user makes. These subforms are quite complex, often involving a tab control and several sub-sub-forms on different tabs. In a second application database we might have 4 different sub-forms that the user can select, and a third application database might have two views, etc. By the time you get 15 or 20 application databases all using the same main form, it gets to be a maintenance nightmare if you need to make a change in the design of the main form. Hope this clarifies things a bit.

Reply | Quote

Wendell,

and the only goal is to propagate changes in the main form to the various application databases?

Jim.

Reply | Quote

Yes, that’s true. The main form contains several ActiveX controls which are updated from time to time by the component supplier (Microsoft, FMS, etc) so we have to make occasional changes to the code as the controls are upgraded (or supplanted by newer or better controls). I know it seems like overkill initially, but we do have about 15 databases that use the same main form, and updating is getting to be an onerous task. Thanks for all the interest in the problem.

Reply | Quote

My thought then would be to have the new form in a “patch” MDB file and moved in automatically when the software detected a new version available. Only drawback to this is database bloat.

You would also have a problem if your using MDE’s as you would not be able to recompile the app.

Jim.

Reply | Quote

I think that’s the general approach we will take – the database bloat isn’t too big an issue as we update the application databases fairly regularly, and they are all front-ends residing on the local hard drives which have lots of space. You’re right about the MDE issue, but we only have one case of that, so we will probably automate the generation of a new MDE file on the server side and then copy the whole thing down to the workstation. Thanks for the input.

Reply | Quote

i have never done this, but here’s what the help says in AXP:

Set References to Type Libraries
– – –
When you set a reference to another application’s type library, you can use the objects supplied by that application in your code. For example, if you set a reference from Microsoft Access to the Microsoft Excel library, you can then use Microsoft Excel objects through Automation (formerly called OLE Automation). If you set a reference to a Visual Basic project in another Microsoft Access database, you can call its public procedures. If you set a reference to an ActiveX control, you can use that control on Microsoft Access forms.

So by the last sentence wording, it seems that it is (hypothetically) possible.

Perhaps the question is: how would this be done in another application? and translate the answer to Access.

I would research creating a dll or ActiveX component that i could call from access.

Reply | Quote

Excellent point – we do that all the time when we use add-in and library databases, but we always do it from the application database to the add-in. What popped into my head when I read your post was that we could try doing it the other way – establishing a reference both ways in effect. The application would reference the add-in mdb file to get the main form, and then the add-in database would programatically set a reference to the application database in order to get at the subforms. Will do some testing and see if it really does work. Thanks for continuing to pursue the question even if it seemed impossible!

Reply | Quote

Taking it all the way back to 1’s and 0’s, there HAS to be a way!
I’d be interested in reading your solution, since i am still a newbie at DB work. Maybe that’s why I can offer a solution – I don’t know enough to know what’s impossible.

Reply | Quote

I would be interested in reading a solution too, but don’t count on there always being a way. Type libraries are typically written in a language like C, which has a lot more control over the whole process than VB has. Creating a COM object might be a possibility, but if you’ve ever worked much with ActiveX controls, you know that you can’t just add on to them or mix them with other controls, so it’s not likely you could add subforms from a user’s database to one.

Reply | Quote

From what I have read concerning this matter here you have the main form that is identical for each database.
What you don’t want to do is manually change it in each of the production front ends, right?

I would do the following.
Have your own maintenance database with said main form and a table listing the functional front ends.
A piece of code that does the following.
Open table and read records
while got record
Open front end #1
Delete main form
import main form from maitenance database
close front end #1
read next record

Reply | Quote

In fact we may have to resort to something like that – unfortunately our scenario is somewhat more complicated earlier, as I didn’t want to cloud the basic issue with more details than necessary. We have from 9 to 20 application databases at each of several clients which use the common main form. In addition at three of the largest we use a deployment manager to download changes to each user’s workstation (from 60 to 250 users at each client). So we would need to add a second layer to the current deployment manager that would update the front-end database copy which is downloaded to the workstation.

We are currently testing various approaches using an add-in database, but I’m not terribly optimistic as it appears you can only touch code objects in an add-in, and not the properties of objects such as forms. Will post the results of that testing when we finish it. Thanks for the suggestion – if we end up implementing it I will also post the results of that.

Reply | Quote

I’m missing something. Why should it be easier to update the end users with an mda file rather than an mde (mdb) file?

Reply | Quote

It’s a special case for us, as we already deploy an add-in to the user’s workstation which contains a number of common forms, so if we could put it in the add-in there would be little or no extra work. In other cases, the work woujld probably be comparable. BTW, add-ins can be mde or mdb files as well as mda files.

Reply | Quote

Thanks but is that really relevant. A file still has to be transferred somehow at some stage.
I do infact call procedures in one mdb from another at times. To display a form in the second mdb I provide a stub of code. However that is of no use to you as you wish the sub form from the first interface.
I may be wrong but I believe an Access add-in specifically has an mda extension. Using incorrect terminology can be misleading.

Reply | Quote

Nothing I can find suggests an Access add-in has to be a .mda file, though the MS library databases are that kind. And technically we are doing what you also do – in Access 97 we actually had the database referenced as an add-in (with all the usys…. registration stuff, but in 2000 there didn’t appear to be any advantages to doing that, so we haven’t. I think we will probably end up doing similar to what you and several others suggest. Thanks for the input.

Reply | Quote

I’ve been looking up the question of terminology again. I suspect that every add-in I have seen and read about has been an mda file so I have assumed that the add-in only refers to mda files, rather than reading it somewhere. Maybe someone out there has a definitive answer.
If you can get it working with the sub form in another DB then it could be interesting to know it is possible.

Reply | Quote

I believe you can distribute an add-in (usysreginfo and all) as at least an mde. The extension is more to tell Windows how to consider it than a functional thing. Library references can use just about anything that has code in it.

Reply | Quote

Further to the extension that defines an add-in. Looking at the add-in manager in Access 97 that filters on mda files, no mde/mdb. Access 2000 however filters on mda and mde files, no mdb. To me that means that the programmers at Microsoft in the past expected only mda files to be add-ins but that the definition was relaxed with Office 2000 to include mde files. For both Office versions mdb files are not considered as add-ins.

Reply | Quote