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.....).

 

[Rafi]

Dear Mr.Kallal,
Hereunder are answers to your suggestions:

I would suggest you do a few things:

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

Everything working, no problems in compilation.

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

Already tried that, last week...

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.

There is absolutely no problem with Linked tables.
They are defined correctly and all the information is displayed correctly.

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.

Can't argue with you on this one,- YOU ARE RIGHT.

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

As I explained, the only thing that stopped working is the search data
within the
combo (client name) box. The information in combo is shown correctly,- but
when you type the client's name in the combo box,- usually tou get the closest
name moved to top. If name exists,- you choose it from list. If not in
list,-
there is a function that defines new client. Presently the search and move
to top
option doesn't perform. Believe me,- I checked everything.....
At this stage if I check the links,- the performance returns to normal.
All I have to do is to open the Link Table Manager and close it without doing
nothing. Just open and close,- and everything works perfectly.
I agree with you that it's not professional solution,- but atleast this will
give some
time to look for another solution.
I think that perhaps my qustion has been misunderstood.
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.

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.....).

Really apreciate the voluntering help of MVPs, as in my field I also help
others
without any charge, and the reason for it is the way to learn new things...
If new problem that requires a non-standard solution is raisen by somebody,-
I see it as a way to fix bugs in the future by knowing about their existance
today.
and if Microsoft professionals make an OPEN command,- then they are the ones
who should make sure that there is a CLOSE command.

Besides, doesn't matter how silly the solution is,- it's stil called
SOLUTION when
it solves the problem !!!!!!!!!

Rafael

 

[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