Open One DB From Another?

[David Portwood]

I'd like to be able to open one database which would run an autoexec macro
to open a "utility" database to do a few standard initialization things and
have that db open the main database which contains my actual app. The main
(second) db is secured. Is this possible?

 

[David Portwood]

I just reread my first post. Too hastily written, I think. Let me try again.

I want to have a utility database which does some standard initialization
stuff. I would open this utility database first, it would do some
initialization things (probably from within an autoexec macro) then it would
open the second database which displays the opening form of my app. The
utility database would then close itself (or maybe my app would close it)
and the app would run normally.

Am I making sense? Is this possible?

 

[Guest]

Hi David

Create a button in your 1stDB.
The OnClick of this button is a callshell to open the 2nd DB
Use quit to close the 1stDB

The callshell is
Path to your access exe
Path to your 2ndDB
1


Something like this

Private Sub ButtonName_Click()
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\MyDocuments\NameOf2ndDB.mdb""", 1)
DoCmd.Quit
End Sub



Note that
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\MyDocuments\NameOf2ndDB.mdb""", 1)
Is all on one line


Change the paths to what they really are
Change the location (if not my documents)
Change the 2ndDB name

Hope this helps

 

[Scott McDaniel]

I just reread my first post. Too hastily written, I think. Let me try again.

I want to have a utility database which does some standard initialization
stuff. I would open this utility database first, it would do some
initialization things (probably from within an autoexec macro) then it would
open the second database which displays the opening form of my app. The
utility database would then close itself (or maybe my app would close it)
and the app would run normally.

Why not just put your initialization routine in the "real" database?

Am I making sense? Is this possible?

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[David Portwood]

Because the initialization involves modifying the "real" database.

 

[David Portwood]

Thanks, Wayne. I'll give it a try on Monday.

Hi David

Create a button in your 1stDB.
The OnClick of this button is a callshell to open the 2nd DB
Use quit to close the 1stDB

The callshell is
Path to your access exe
Path to your 2ndDB
1


Something like this

Private Sub ButtonName_Click()
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\MyDocuments\NameOf2ndDB.mdb""", 1)
DoCmd.Quit
End Sub



Note that
Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\MyDocuments\NameOf2ndDB.mdb""", 1)
Is all on one line


Change the paths to what they really are
Change the location (if not my documents)
Change the 2ndDB name

Hope this helps

 

[David Portwood]

Your solution works fine, but can you include a \wrkgrp switch in the
command line? I've tried, but can't make it work. Also, is there any way to
use params instead of hardcoding everything?

 

[Douglas J. Steele]

To include the wrkgrp switch, you'd use

Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
""C:\MyDocuments\NameOf2ndDB.mdb"" /wrkgrp""C:\My
Documents\NameofWorkgroupFile.mdw""", 1)

(all on one line, of course).

You can use parameters along the lines of

Dim strExecutable As String
Dim strDatabase As String
Dim strSecurity As String

strExecutable = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
strDatabase = "C:\MyDocuments\NameOf2ndDB.mdb"
strSecurity = "C:\My Documents\NameofWorkgroupFile.mdw"

Call Shell("""" & strExecutable & """"" " & _
"""" & strDatabase & """"" " & _
"/wrkgroup """ & strSecurity & """", 1)

You can set the 3 variables however you want. For instance, you might set
strExecutable using

strExecutable = SysCmd(acSysCmdAccessDir) & "msaccess.exe"

and you might allow the user to select the database using the standard
Windows File Open dialog (as illustrated in
http://www.mvps.org/access/api/api0001.htm at "The Access Web") (Of course,
if you let them select the database that way, you'll have an issue knowing
which workgroup file to use)

 

[David Portwood]

I was able to incorporate the \wrkgrp switch per your example, which gives
me almost enough, but I couldn't get your params example to work. I think
you may have too many double-quotes, somewhere. In one place I count 10
double-quotes in a row. That can't be right, can it?

It is really quite important to be able to pass params rather than rely on
hardcoded strings. If it is not too much trouble, would you be so kind as to
check that for me? I wish I could do this myself, but I have no idea where
all the double-quotes come from or what they are for. There don't seem to be
any relevant examples in the Help files. Very intimidating, all those
quotes.

 

[Douglas J. Steele]

Yup, a couple of places I did overdo the double quotes. Inside a string, you
need 2 double quotes in order to produce a single double quote. That's why
there are 4 double quotes at the beginning: that will product a single
double quote in the resulting string. However, only """ " is required to
produce a single double quote followed by a space in the string:

Call Shell("""" & strExecutable & """ " & _
"""" & strDatabase & """ " & _
"/wrkgroup """ & strSecurity & """", 1)

Other ways to do it are to use Chr$(34) wherever you want a double quote to
appear in the string:

Call Shell(Chr$(34) & strExecutable & Chr$(34) & " " & _
Chr$(34) & strDatabase & Chr$(34) & " " & _
"/wrkgroup " & Chr$(34) & strSecurity & Chr$(34), 1)

or define a constant that represents a double quote, and use that:

Const cDbleQte = """"

Call Shell(cDbleQte & strExecutable & cDbleQte & " " & _
cDbleQte & strDatabase & cDbleQte & " " & _
"/wrkgroup " & cDbleQte & strSecurity & cDbleQte, 1)

(using the constant is more efficient than using the function call)

Sorry about that.

 

[Tony Toews [MVP]]

Call Shell("""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""

Note that this assumes the client systems all have Access installed in
the same location. If this should different from system to system you
can read a registry key entry to determine the location specific to
the version of Access you wish to execute.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David Portwood]

I forget now if the pathname to the Access .exe doesn't change from system
to system and I'm using the same hardcoded string or if it does change and
I'm using

SysCmd(acSysCmdAccessDir) & "msaccess.exe"

to get the pathname. I think the second. I don't know how to interact with
the registry and it's fortunate that I don't have to.

 

[Tony Toews [MVP]]

I forget now if the pathname to the Access .exe doesn't change from system
to system and I'm using the same hardcoded string or if it does change and
I'm using

SysCmd(acSysCmdAccessDir) & "msaccess.exe"

Ah, I had completely forgotten about that command. In my Auto FE
Updater I have to locate the path to msaccess.exe so I use the
registry for that. But you're correct. That would work from inside
Access quite nicely.

I don't know how to interact with
the registry and it's fortunate that I don't have to.

It's not that difficult. The most important thing is to set your
options for opening the registry as read only rather than read/write.
Which is what the current code samples default to anyhow. However
the code samples I used five or six years ago did not. But these days
Windows is locked down much tighter than it used to be and many folks
are running as users rather than admins. Thus not only is Program
Files folder read only so is vast portions of the registry.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David Portwood]

I just noticed I didn't return to thank you, Douglas. I do so now, thank you
very much. Your suggestions were invaluable.