Help needed with automating Link Table Manager using SendKeys

[Scandiaman]

Although the following preface is not needed for helping me with my real issue, I have provided it for context. Skip through if you wish.

My Access app is split with the forms/reports/etc front end database and the data (tables) backend database being linked.
I am running the app on two different pcs, one in the office and one out in the field on a Surface tablet.
Although the front end and back end files are within their same local directory, the office pc's files are in a different folder location than the tablet's.
After the laptop comes back from the field, the data on the office pc is updated by overwriting the data file on the office pc with the data file on the tablet.
After updating the data file, a problem arises when the app is opened up on the office pc. The stored linked file location that worked on the laptop is not the same as the actual location of the data file on the Office pc.
Unfortunately I know of no way to set the different link locations between the office pc and the tablet pc within Access's individual configurations on each pc. I therefore am creating a VBA action to automate the relink process.

To automate re-linking my back end database my VBA code is as follows:

DoCmd.RunCommand acCmdLinkedTableManager ' Opens up Linked Table Manager - this part works
SendKeys "%(s)", True 'Should select all linked tables (but it doesn't)
SendKeys "%(a)", True 'Should put check mark to always prompt for new location (but it doesn't)
SendKeys "{ENTER}", True 'Should activate the OKAY button (but it doesn't)
SendKeys "S:\CP_DATA.ACCDB{ENTER}", True 'Should type in the correct database file location and then press Enter (but it doesn't)
SendKeys "{ENTER}{TAB}{TAB}{ENTER}", True 'Should complete the link response dialog box acceptance to complete the re-link (but it doesn't)



OK, here is my issue:
Link Table Manager opens but SendKeys is not typing anything. When Link Table Manager is open I can manually press ALT-S and ALT-A to select all linked tables and to activate 'Always Prompt For New Location'.That works.
I believe I have the proper syntax for the SenKeys actions and have tried other variations without success. I have also tried using {TAB} and {ENTER} to get to and activate the Select All and Alway Prompt For New Location but also without success.

I must be missing something. Any help would very much be appreciated. Thank you in advance!

[June7]

My code to reset table links. Review https://www.accessforums.net/showthr...+table+manager

[Scandiaman]

Thanks much June7 for your help.

I REM'd out my DoCmd.RunCommand acCmdLinkedTableManager and SendKeys code and added the code you provided (with modifications to the file locations).
I have added a pop-up form in my autoexec macro with buttons that allows me to choose which pc (file location) that the table files are located on. Then after your code is run from which ever button is clicked on, my Main Menu form opens up.
WORKS GREAT! And thanks again.

BTW, I would still like to figure out why my SendKeys code doesn't work, as I think it could come in handy for other needs that might come up. Any Ideas?

[June7]

Use of SendKeys is not recommended. Per MS knowledge base: "Security Note: Avoid using the SendKeys statement or an AutoKeys macro with sensitive or confidential information. A malicious user could intercept the keystrokes and compromise the security of your computer and data."

And from another website: WARNING: You should only use the SendKeys Method if no other option is available, because it can cause problems if the wrong window is active when the code runs."

I used SendKeys to send {ESC} only because could not get VBA Undo command to work. But maybe the answer you seek is in https://msdn.microsoft.com/en-us/lib...ffice.10).aspx, in particular the first statement under Remarks.

[Scandiaman]

June7.

I had read about the SendKeys security warnings but am not concerned since the data is not sensitive in this DB.
Also, I have been to the MSDN link that you just mentioned and have studied the SendKeys syntax but my code seems to be in error as it doesn't work.

For my purpose, the code you provided will work just fine and I appreciate your help very much. But I still would like to know what is wrong with the SendKeys code that I wrote so that I must might need it in the future.

Thanks again!

[Micron]

Since you're asking and I rarely use Sendkeys either, I will take a guess. IIRC, it only works on the active window, and opening the dialog via code doesn't do that. You have to make it the selected object before trying to act on it. Again, just guessing.

[Scandiaman]

Micron,
Opening the Linked Table Manager with a command button from within my form using the DoCmd.RunCommand acCmdLinkedTableManager action appears to become the active window (selected object). If I manually press the TAB key, the focus moves from control to control within the Linked Table Manager window.
But if for some reason it isn't the active window after running the DoCmd.RunCommand acCmdLinkedTableManager action, than what is the code to make it the active window (set focus)? I am used to doing this with tables, forms or reports but have never tried setting focus to an app such as the Linked Table Manager.

Thank you for your input.

[Micron]

Thanks for the info. I may have the answer after testing your code. I put MsgBox "test" right after the DoCmd line and it didn't execute, which suggests the LTM is application modal, which means the code that calls it stops running. That should explain why you can use sendkeys against some things but not this one.