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?
Microsoft AccessVBA

Avatar of undefined
Last Comment
Denys

8/22/2022 - Mon
Scott McDaniel (EE MVE )

What version of Access/Office is running in the vmWare environment?
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Denys

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

Is that what's running ni the vmWare environment (i.e. the one where you're trying to relink)?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jim Dettman (EE MVE)

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.
ASKER
Denys

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 )

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Denys

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 )

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
ASKER
Denys

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott McDaniel (EE MVE )

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.
ASKER
Denys

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