Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link to another DB (2K)

    I have an application that I want to open another instance of Access to do a file conversion. I have the following code:

    Function linkConvert()
    Dim ws_Path As String
    Dim returnValue
    ws_Path = Application.CurrentProject.Path & "Convert.mde"
    ws_Path = """" & ws_Path & """"
    ws_Path = "msaccess.exe " & ws_Path
    returnValue = Shell(ws_Path, 1)
    End Function

    This works fine on my system. However when my client runs this routine on their system it responds with an error message indicating the path can't be found. The path that it displays is the path that is on my system. (it includes my user id from my windows xp pro system). What I wanted to do was retrieve the path of the current application and use another mde in the same directory. Is there something wrong with this? Does it have anything to do with making the database an MDE?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Link to another DB (2K)

    If someone is running this code on another computer and then:

    "The path that it displays is the path that is on my system. (it includes my user id from my windows xp pro system)."

    This makes no sense, and have no explanation - why would path display a UserID??. CurrentProject.Path should return the path for the currently open project (database). It doesn't matter if the database is an .MDB or .MDE. Obviously the .MDE file you want to open has to exist in the same folder as the current db. Anyway, when building string for path to shell out to another instance of Access, I'd recommend using the SysCmd function to return the full path of the Access executable for the instance of Access in which code is being executed to avoid any ambiguity in event user has more than one version of Access installed on their system. Example, from Immediate window (using A2K):

    ? Chr$(34) & SysCmd(acSysCmdAccessDir) & "MSAccess.exe" & Chr$(34) & " " & Chr$(34) & Application.CurrentProject.Path & "Convert.mde" & Chr$(34)
    "C:Program FilesMicrosoft OfficeOfficeMSAccess.exe" "C:AccessConvert.mde"

    Note the Chr$() function is used to add path delimiters in case there are any spaces in path, this makes code more readable than enclosing the double-quotes in double-quotes. I don't know if specifying full path for MSAccess.exe will resolve problem, but that's the first thing I would try. Otherwise no suggestions, other than test for existence of .MDE file before attempting open second instance of Access.

    HTH

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to another DB (2K)

    Thanks Mark. Did some more checking and the user led me astray(again...) . There is nothing wrong with the linking code. I will need to get more info and of course I am waiting for a call back...

    Tom

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to another DB (2K)

    I try to get in a habit of writing Error Handling routines. In addition to displaying MsgBox's to the user.... who typically ignore the messages, I write the messages to a log file.

    When said users call and and lodge a complaint, I asked them for the log file, their data file, and their front end... even thought they swear they haven't changed the FE.

    After x number of years I'm still trying to hold my tongue when customers complain and give erroneous error messages.

    -Mike

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Link to another DB (2K)

    Your code assumes the database is in the same directory as Access itself, which apparently it is on your machine but not the other machine. You need to use the SysCmd method to get the directory for Access separately from the path to the database.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •