Passing Current DB filename to another DB

[David Mulholland]

I've got a thread started in the multiuser section, but I wanted to post this
question in the meantime.

Can an open DB pass it's filename to another DB it opens with code? If so,
how? I'm guessing I'll need a local table to store that data.

Thanks for any thoughts you guys can give.

 

[Tom van Stiphout]

On Fri, 28 Mar 2008 11:06:02 -0700, David Mulholland

How are you opening the second database? If using automation, you
could then call a public method or property and pass in the other db
name.

-Tom.

 

[David Mulholland]

I'll be opening the 'Update.mdb' with automation. My goal is to pass the
user's currently open FE DB filename to that 'Update.mdb' so it knows which
newer FE to get and overwrite. I'm looking for some examples on how I would
pass that filename to a table in that 'Update.mdb'.

 

[Peter]

David,

You can link to the table in the "Update.mdb" in code, using the
DoCmd.TransferDatabase method, then do the update, or I think you can
use the "IN" clause on your update statement to update a table in an
external db. Ex. (untested):

UPDATE table IN mydb.mdb
SET newvalue
WHERE criteria;

Hope this helps,

Peter De Baets
http://www.peterssoftware.com

 

[Chief Mathis]

Use ShellExecute to open your 'Update.mdb' and pass your FE DB filename as a
command-line parameter. The code in your 'Update.mdb' could then read the
command-line parameter value (no table is needed to store this temporary
value). This method creates no direct link between the two MDBs and would
provide the flexibility to use your 'Update.mdb' with any FE DB. If you need
a more detailed description, let me knw.

 

[David Mulholland]

This does sound interesting. I've never used ShellExecute. Details would be
appreciated.

 

[Alex Dybenko]

hi,
here a sample:
http://www.mvps.org/access/api/api0018.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Chief Mathis]

First, thank you Alex for providing the link. Now the bad news... this
method doesn't work. When using ShellExecute to open an mdb, the parameter
is ignored. I was trying to prepare a simple example and realized that this
was the problem that I had when I originally tried this. You can use Shell
and it does work but it's a pain to get the syntax right. Here is an example:

Sub RunUpdate()
Dim Update_DB_FilePath As String
Dim FE_DB_filename As String
Dim CommandLine As String
Dim ReturnCode As Long

Update_DB_FilePath = "\\servername\foldername\Update.mdb" 'path to update file
FE_DB_filename = CurrentDb.Name

CommandLine= Chr$(34) & Application.SysCmd(acSysCmdAccessDir) &
"msaccess.exe" & Chr$(34)
CommandLine = CommandLine + Chr$(32) & Chr$(34) & Update_DB_FilePath &
Chr$(34)
CommandLine = CommandLine + " /cmd " & Chr$(34) & FE_DB_filename & Chr$(34)
ReturnCode = Shell(CommandLine, vbNormalFocus)
If ReturnCode = SUCCESS Then 'I don't know the correct value
DoCmd.Quit 'close calling app to allow updates
Else
MsgBox "Unable to perform updates"
End If
End Sub

You then use the 'Command' function to return the value passed with /cmd
when launching Microsoft Access. This value will be available until you
close Update.mdb unless you change it manually
Click Tools>Options>Advanced>Command-line arguements
read via code.
Calling_FE_DB = Command() 'read the value

Best of luck,

 

[Alex Dybenko]

Hi,

When using ShellExecute to open an mdb, the parameter
is ignored.

have you tried to use lpParameters argument for parameters?

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Chief Mathis]

Alex,

The following is excerpted from:
http://msdn2.microsoft.com/en-us/library/bb762153.aspx

lpParameters
[in] If lpFile specifies an executable file, this parameter is a pointer to
a null-terminated string that specifies the parameters to be passed to the
application. The format of this string is determined by the verb that is to
be invoked. If lpFile specifies a document file, lpParameters should be NULL.

Unfortunately, it sees the MDB file as a document not an application. As I
write this, I'm inspired to try something (that I think should work) and put
MSACCESS.EXE in the lpFile position and the MDB file and command-line
switches in the lpParameters position.

Thanks for the inspiration,
Chief Mathis