Menu system

[Andy]

For year I've had an Access 97 database that the users could switch from one
mdb to another with out opening multiple instances of Access. This used
sendkeys like so.

DoCmd.Close A_FORM, "FrmMainMenu"
SendKeys "^(o) Sales.mdb~", False

This has always been cumbersome and as I'm now porting the app to
Access2007, is there a better way to do this? I have the ribbon customized
the way I want it and would like to add functionality to replace the previous
sendkeys method.

can this "<button idMso="FileOpenDatabase" size="large" visible="true"/>"
be modified to open a specific mdb without the user seeing the Eplorer like
selection window. The mdb names are cryptic and I'd like to give them user
fiendly labels.

Are there any other ways to do this?

 

[Albert D. Kallal]

Just have the ribbon onAction run some VBA code to launch antoher database.

You could go:

<button id="button1" label="View Sales"
onAction="=BtSales()"
supertip= "Launch the Sales Database"
/>

then, simply define a public function called:

Public Funciton BtSales

application.FollowHyperlink "c:\MyDatabases\sales.mdb"
application.Quit

end function

Sendkeys should be a last resort option when you exhausted everything
else.....

 

[Andy]

Thanks for the tips. I looks like it should work. I'll give it a try
tomorrow.

Andy

 

[Andy]

Just have the ribbon onAction run some VBA code to launch antoher database.

You could go:

<button id="button1" label="View Sales"
onAction="=BtSales()"
supertip= "Launch the Sales Database"
/>

then, simply define a public function called:

Public Funciton BtSales

application.FollowHyperlink "c:\MyDatabases\sales.mdb"
application.Quit

end function

Sendkeys should be a last resort option when you exhausted everything
else.....

 

[Andy]

This action works well but has one problem. Maybe you can guide me. I use
a common system.mdw on the server to get the users credentials. The
Followhyperlink doesn't carry the credentials to the new .mdb and the user
can open it but has no rights to it and can't do anything. It also doesn't
force a new login. The sendkeys, which uses the the open database dialog
window, doesn't have this problem.

Is there another way that I can open an mdb, leave the existing mdb, without
closing the Access application and keep the existing log in credentials?

 

[Albert D. Kallal]

This action works well but has one problem. Maybe you can guide me. I
use
a common system.mdw on the server to get the users credentials. The
Followhyperlink doesn't carry the credentials to the new .mdb and the user
can open it but has no rights to it and can't do anything. It also
doesn't
force a new login. The sendkeys, which uses the the open database dialog
window, doesn't have this problem.

Was not sure you wanted to keep the existing application open....... (just
remove the application quit).

Is there another way that I can open an mdb, leave the existing mdb,
without
closing the Access application and keep the existing log in credentials?

You can leave/keep the 1st application open, but launching anohter with
credentials is not possbile...

Also, if the workgroup is set by using shortcut (it should be so only those
mdb files that are secured will prompt the user), then you can pass the
workgroup file when you launch access via a shell. However, you can't keep
the logon credentials (that would be a big security hole if you could).

So, you can use the following to launch with the workgroup (however, in your
case it sounds like users are already joined to the workgroup file, so this
follwing code does not help you a lot...).


Const q As String = """"

strRidesRun = q & SysCmd(acSysCmdAccessDir) & "msaccess.exe" & q & " "
& _
q & strCurrentDir & CurrentProject.Name & q & " " & _
"/wrkGrp " & q & SysCmd(acSysCmdGetWorkgroupFile) & q


Shell strShellProg, vbNormalFocus

Unfortunately, the above will still prompt the user for a logon....

The only way around this would be to launch an access application with a
workgroup file that does NOT prompt user. You then build a startup screen in
which YOUR code prompts the user, and then you can shell out to any of the
other programs with that username + password...

You would add to the above shell:

' " /user " & q & "Admin" & q & " /pwd " & q & "Admin" & q

So, you build your own launcher program that prompts for the user name +
password, and then use that to launch other secured access programs...

So, if your startup application prompts the user for name + pass, the it is
possible to launch secured databases as per above....

 

[Andy]

I'll try the ideas in your code but this whole thing seems more complicated
then it should be. This is why the send keys worked so well. This is what I
want to have done.

User starts MSAccesss from a desktop icon. The target entry sets the
wrkgrp/system.mdw for login credentials, and sets the starting mdb. The
starting mdb has an autoex that opens the main menu. After this I want the
user to see only my application. I have six mdbs that I want them to open
when needed. Close the existing one and open the new one all within the same
instance of one MSAccess.exe. They have aleady logged into the mdw with
their credentials.

Manually, in Acccess 2007 this is done by by clicking the round Office
button, clicking open, which opens the open dialog box (like a windows
explorer). Clicking the new mdb closes the old and opens the new. I would
like to do this programatically. DoMenuItem 0, 1, "sales.mdb" doesn't work
because there is no command to open the sales.mdb.

FollowHperlink works but I think it opens a new instance of Access but
doesn't bring up the mdw dialog box.

NewCurrentDatabase works as well but again doesn't bring up the mdw dialog
box. On both of these there does'nt seem to be a way to pass the credentials.

I did a lot of research into this with Access97 and Sendkeys was what always
worked the best but it always seemed hokey to me.

So how does one more elegantly do DoMenuItem 0, 1 "sales.mdb" (and get it to
open) or a better substitute for SendKeys "^(o)sales.mdb~", false

Albert,

Thanks for your help. This is my first venture into Access2007 and I
thought that this would be simpler now. I do like the ribbon programming
howerver.

Andy

 

[Albert D. Kallal]

FollowHperlink works but I think it opens a new instance of Access but
doesn't bring up the mdw dialog box.

It will/should if there is a workgorup file. But, you have no way of passing
the user name + pass with HyperLink anyway.

I suggested FollowHyperLink above before I realized a workgroup file was
invoved.

The only real stable way here is to use shell with the user + pass word
included (but, then you need to get/prompt for the user + password via
some prompt system)

NewCurrentDatabase works as well but again doesn't bring up the mdw dialog
box. On both of these there does'nt seem to be a way to pass the
credentials.

You can with the shell example, and then quit the existing application (or
keep it running...your choice).

But, this assumes you prompted the user for logon (and have that info).

I did a lot of research into this with Access97 and Sendkeys was what
always
worked the best but it always seemed hokey to me.

Unfortnally Sendkeys was a bad leg to stand on. I just don't have a good
answer for you.

So how does one more elegantly do DoMenuItem 0, 1 "sales.mdb" (and get it
to
open) or a better substitute for SendKeys "^(o)sales.mdb~", false

The keybord to open a file in 2007 is alt-f o then file name...but, I
am unable to get sendkeys to work
eg:

SendKeys "%(fo)", True
DoEvents
SendKeys "test1.accdb", True
DoEvents
SendKeys "%o"

Thanks for your help. This is my first venture into Access2007 and I
thought that this would be simpler now. I do like the ribbon programming
howerver.

Anytime you have a system in which name + password is used, then your
choices are going to be somewhat more limited here. I thinking the only way
to really do this is to build your own name+password prompt system..and then
use that to shell out to the needed applications. You could/would use the
openDataBase method to verify if the name/pass entered was correct....

 

[Andy]

Thanks for your help.

Andy

It will/should if there is a workgorup file. But, you have no way of passing
the user name + pass with HyperLink anyway.

I suggested FollowHyperLink above before I realized a workgroup file was
invoved.

The only real stable way here is to use shell with the user + pass word
included (but, then you need to get/prompt for the user + password via
some prompt system)


You can with the shell example, and then quit the existing application (or
keep it running...your choice).

But, this assumes you prompted the user for logon (and have that info).


Unfortnally Sendkeys was a bad leg to stand on. I just don't have a good
answer for you.


The keybord to open a file in 2007 is alt-f o then file name...but, I
am unable to get sendkeys to work
eg:

SendKeys "%(fo)", True
DoEvents
SendKeys "test1.accdb", True
DoEvents
SendKeys "%o"


Anytime you have a system in which name + password is used, then your
choices are going to be somewhat more limited here. I thinking the only way
to really do this is to build your own name+password prompt system..and then
use that to shell out to the needed applications. You could/would use the
openDataBase method to verify if the name/pass entered was correct....