use VBA to close Linked Table Manager dialog box in Access 2007

[Rafi]

Well, seems like the Microsoft MVPs are not able to answer a simple question,
and according to previous replies are suggesting to blame SP3 of Access 2003.
We are talking here about Access 2007 and the program that was working
fine for nearly half year,- untill this problem.

The problem is that combos within one of the forms stoped doing search for
existing data, but the data is visible within the combo list. When you type,
it just doesn't give you marked on screen alphabetically next close to it
line. All the tables are linked correctly and all the forms and querries are
working fine. I noticed that to fix this problem all I need is to open the
Link Table
Manager dialog box and then do cancel or ESC without changing nothing,- and
everything works fine again. I spent almost a week trying to figure out the
problem,- but I can't find anything that would cause it. Then in order to
release users from touching the data links,- I used the
acCmdLinkedTableManager line to open the dialog box of Linked TAble Manager
on Swithchboard Form load. All I need now is to close it thru the next VBA
line. I tried using Sendkeys "{ESC}",- but it doesn't work.

Open for any ideas anyone might have....

 

[Albert D. Kallal]

I would suggest you do a few things:

1st, make sure you code compiles. (while in code,, go debug->compile).

2nd do a compact and repair. if the problem still persists, create a blank
new database and import everything, and see if that helps.

3rd, why not try some re-linking code here to re-link to the back end. After
all I think most developers find it makes sense to provide some kind of
re-linking system in your code. Providing such a re-linking system means
that your end users will not have to learn, or even be knowledgeable about
how to use the linked table manager.

Furthermore since the runtime system is now free for access 2007, there's
always the possibility that you might want to deploy your application to
those users machines that don't have the full blown version of access
installed, and therefore once again you will NOT have a menu option for the
linked table manager at your disposal, and most versions of the runtime you
can NOT pop open that dialog.

Furthermore, you gotta have to admit that's a pretty bad kludge to fix the
problem at hand. I suppose if this was a medical group and your doctor was
smoking a cigarette during open heart surgery, you are now standing here
asking for someone to hold out an ashtray over the open heart to catch the
dropping ashes from the cigarette. So, realistically no self respecting
developer would even begin to enterain the idea of popping open the link
dialog, and then closing it as a reasonable fix to your problem. I would
likely fire you as a developer on the spot for even suggesting such a a
solution *unless* all other reasonable approaches to this problem had been
exhausted.

So the first thing I would attempt to try writing your own custom re-linking
rouintes in code, and see if the first fixes this problem.

You can find some sample code here to re-link:

http://www.mvps.org/access/tables/tbl0009.htm


And, furthermore, to pop open the file browse dialogue can be found here:
http://www.mvps.org/access/api/api0001.htm


I would also test your applicaton as a mde, or in your case a accDE to you
end users (this will force you to ensure your code is compiled before you
distribute the front end to your end users machines).

At the end of the day it's pretty much standard fare to provide some type of
re-linking mechanism for a access application, as this approach not only
will relieve your end users from ever having to use or know about the linked
table manager, it also tends to make your application more reliable as you
provide a custom solution to link to correct back end of your choice under
program control.

You could even consider re-linking the front end to the back end on startup
if they are in the same folder...and the location is changed, your code
could even detect the situation....

Well, seems like the Microsoft MVPs are not able to answer a simple
question,

Well, perhaps that is the case, perhaps not. I would suggest you keep in
mind that the people giving information out in these newsgroups are doing it
strictly on a volunteer time, and strictly on a free as is basis. So keep in
mind the people here including MVP's are not being paid, and are for the
most part VERY busy professional developers giving some of their time back
to the community and industry that's been so fortunate to them. it's
probably just a simple suggestion to note that these people don't owe you a
solution, and you'll likely don't start barking at the hand that's feeding
you.

If your question is not being answered, either most of us don't think
there's a reasonable solution to your problem, and don't have anything handy
in our fingertips to suggest, it really is that simple.

I would also consider perhaps making a blank new database from scratch, and
importing everything from the old one, and see if this fixes, or helps this
problem. The other possible suggestion would be to delete all the linked
tables, and recreate them, (not a very great suggestion, but it certainly up
in the list as opposed to the kluge of simply popping open a dialogue box
and then closing it....especially in some cases with the runtime you won't
even know that we have that dialog box available.....).

 

[Robert Morley]

What I'm looking for is the oposite of command acCmdLinkedTableManager.
If there is a built-in command for opening this dialog box,- then there must
be the
oposite command that closes this dialog by VBA and not by pressing the ESC or
Cancel.

Unfortunately, that's not the case. The Linked Table Manager is opened
modally, so there cannot be a command per se that will close it, as no code
can run while it's opened.

That said, you *might* be able to trick the Linked Table Manager using
SendKeys. I don't have 2007, so I can't test it and be absolutely sure, but
I believe the code you'd want would be something like:

SendKeys "{ESC}", False
DoCmd.RunCommand acCmdLinkedTableManager

This works in Access 2003. The gist of it is that you send a keystroke, and
by the time Windows actually processes it, the Linked Table Manager window
is open, and then you're closing it again. It's definitely a kludge on top
of a kludge, but if you have no other recourse, it should get you where
you're going.

But just to add to the fun, this won't work if you're using Windows Vista
(or so I gather). Karl E. Peterson has developed a work-around that
reportedly works fine under all OSs, which can be found here:
http://vb.mvps.org/samples/project.asp?id=sendinput

Definitely, before going through all this trouble, I would investigate the
options that Albert has suggested, most especially trying to replicate the
problem in a clean database, and if you can't replicate it, importing all
your objects from the old database to a new one.


Rob

 

[Rafi]

Dear Rob,

Thank You Very Much.

Best Regards,

Rafael