Link to home
Start Free TrialLog in
Avatar of Denys
Denys

asked on

Using CommandBars ("Tools") in distributed .mde

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?
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

What version of Access/Office is running in the vmWare environment?
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Denys
Denys

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Is that what's running ni the vmWare environment (i.e. the one where you're trying to relink)?
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.
Avatar of Denys

ASKER

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.
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.
Avatar of Denys

ASKER

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.
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
Avatar of Denys

ASKER

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.
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.
Avatar of Denys

ASKER

Thanks Scott, I will explore this! [May be a week or two now before I get back to you or Jim on this problem]