We help IT Professionals succeed at work.

Using CommandBars ("Tools") in distributed .mde

Denys
Denys asked
on
56 Views
Last Modified: 2016-05-30
I have written an Access 97 application as an extension to a legacy Access 97 .mde for which we have no source code, running under Windows 7. Ideally, I want to be able to relink the tables when the "extension" .mde is placed alongside the "legacy" on the Windows 7 target machine.  I have created a form button "Re-link the tables" which works fine on my development environment (XP in VMWare on a Mac) where I have a full Access 97 licence, but does not work  when run using the system.dsw Access runtime on the target machine.

The current workaround is to "prelink" the tables so that the "extension" .mde already contains the correct references, but I want to allow the user to relink to other folders containing alternate sets of data.

The form button runs the VBA code statement:

   CommandBars("Tools").Controls("&Add-Ins").Controls("&Linked Table Manager").Execute

But under Windows 7 the .mde does not carry the toolbar which runs the Linked Table Manager, so it just gives an illegal function error message, although it runs perfectly in the development environment.

Any suggestions on how to get around this?
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
What version of Access/Office is running in the vmWare environment?
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks Jim, I have Access 97 so the Jstreettech download won't run with this antique, but thanks to your suggestion I have found an MS article on how to relink Access 97 using code which I will now try to understand.

Scott, I am running Access 97 SR1 Professional Edition and the Developer Edition Tools.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is that what's running ni the vmWare environment (i.e. the one where you're trying to relink)?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I have old code here that I can dig out.   Fundamentally it's still the same operation; updating the tabledef's connect property.

let me know if you need help with that.

Jim.

Author

Commented:
Scott

The main data tables are in the "legacy".  The "extension" holds a couple of new supplementary tables, but needs to link with the "legacy" tables on a read-only basis to provide its results.

Jim

That would be very helpful, thank you.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I see.

So are you creating links in the LEGACY database, or in the EXTENSION? Just trying to be clear on this so we can give you the best direction.

Author

Commented:
Scott

I am putting the code to do the linking in the EXTENSION because I can only write code for that.  The EXTENSION reads the tables in the LEGACY, then produces some reports and e-mails which the LEGACY is unable to do. The EXTENSION contains SQL or Access queries which need to reference tables held in LEGACY.

If I can perform linking programmatically, I will also separate the EXTENSION into a front-end and back-end (it has a couple of user-populated tables of its own), which would allow me to leave the back-end data tables for EXTENSION undisturbed on the target machine too, and provide updates just to the front-end logic.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I see - so you're creating the links in your EXTENSION, and you're linking to tables in the LEGACY.

Be aware that you can't create links to those tables unless they're LOCAL tables in the LEGACY. If those tables are linked (in the LEGACY), you'll have to create links from the source.

You can, however, Import linked tables using DoCmd.TransferDatabase. If you use that to import a Linked Table, you basically import the Link itself, not the table.

https://msdn.microsoft.com/en-us/library/office/ff196455.aspx?f=255&MSPPError=-2147217396

Author

Commented:
Thanks Scott.  If I have understood you correctly, this shouldn't be a problem, because the LEGACY is a stand-alone package with no links to other databases, hence using LOCAL tables.  The links between the two all work fine at the moment on the target machine, but that is because I have replicated the target's relevant directory structure on the development machine, got it all working with a copy of LEGACY, then transferred the copy of EXTENSION.mde to the target.  

The problem arises when LEGACY working data is periodically archived by the user, and a new period's database is created in a differently named folder.  At this point I want the user to be able to declare the new folder name for the data tables to EXTENSION and have it relink to this new folder at the press of a button, rather than ask me to provide a new "pre-linked" EXTENSION and the user having to set up the EXTENSION's own data tables anew.

I don't really want to transfer the LEGACY database into EXTENSION, because that would be a copy and would raise synchronisation issues, as he is likely to have both databases open and in use concurrently. But it's an interesting learning point for me, thank you.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I don't really want to transfer the LEGACY database into EXTENSION,
The TrasferDatabase method can be used to link the data in addition to importing the data. It's not really very well named, in other words.

So my point is - you could use TransferDatabase to create a linked Table in your EXTENSION database to the local table(s) in your LEGACY database.

Author

Commented:
Thanks Scott, I will explore this! [May be a week or two now before I get back to you or Jim on this problem]
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.