Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database already opened (Access 2000)

    What is wrong with my function that unzips files,imports them and then tries to open one of them.

    My function is

    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsDepotSo.zip C:BE"
    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsSurvey.zip C:BE"
    OpenCurrentDatabase "C:besurvey.mdb"


    The function unzippes files available in C:BEDepots into the folder C:BE and then tries to open the unzipped fil survey.mdb.


    the message says " You already have the database open".
    However this is not true,the database "survey" is not opened.The two unzipped files depotSo and survey
    ara avilable in the folder C:BE, and the survey is not opened.
    What may be the reason?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database already opened (Access 2000)

    OpenCurrentDatabase is meant for use in automation, when you control Access from another application.

    You could use ShellExecute: copy the following code to the top of a module (below Option Compare Database and Option Explicit, but before all functions and subs:

    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Public Const SW_SHOWMAXIMIZED As Long = 3
    Public Const SW_SHOWNORMAL As Long = 1

    Instead of using OpenCurrentDatabase, use this instruction to open another database:

    ShellExecute Application.hWndAccessApp, "Open", "C:BESurvey.mdb", 0, 0, SW_SHOWMAXIMIZED

    If you want to close the current instance of Acces, put the following instruction below it:

    Application.Quit

  3. #3
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database already opened (Access 2000)

    Thank you so much.Of course the code runs now smootly, i didnt doubt about that.Thanks once again.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database already opened (Access 2000)

    Dear Hans,


    As i have told you, the function is perfect.In combination with another code however
    i have come accross the same problem as before

    When i try to put another lines of code, it does not work.
    So if i add on the 2 lines for unzipping the files, the files are inzipped in the folder indeed
    but the database survey.mdb is not opened.
    How can i avoid this conflict ?


    Public Function OhMy()
    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsDepotSo.zip C:BE"
    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsSurvey.zip C:BE"

    ShellExecute Application.hWndAccessApp, "Open", "C:BESurvey.mdb", 0, 0, SW_SHOWMAXIMIZED
    Application.Quit

    End Function


    The database is opened if i exclude the 2 lines and leave only the ShellExecute line.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database already opened (Access 2000)

    What is the error message you get?

  6. #6
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database already opened (Access 2000)

    I get no message, just i do not get the database opened. I get only the files depotSofia and Survey unzipped,
    but the function stops here and the database is not opened:

    Public Function Ohmy()
    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsDepotSo.zip C:BE"
    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsSurvey.zip C:BE"
    ShellExecute Application.hWndAccessApp, "Open", "C:BESurvey.mdb", 0, 0, SW_SHOWMAXIMIZED
    Application.Quit

    End Function


    The same result is obtained when i write:
    Public Function OhMy1()
    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsDepotSo.zip C:BE"
    Shell """C:Program FilesWinZipWZUnzip.exe"" C:BEDepotsSurvey.zip C:BE"
    End Function


    If i write then only ShellExecute then the database is opened:


    Public Function OhMy2()
    ShellExecute Application.hWndAccessApp, "Open", "C:BESurvey.mdb", 0, 0, SW_SHOWMAXIMIZED
    End Function



    It follows i can achieve the result in parts.First click one button the unzip the files and second click anoher
    button to open the file.
    But i want to do it it with one function.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database already opened (Access 2000)

    Try inserting a loop between the two parts, similar to what I suggested in your previous thread:

    Do While Dir("C:BESurvey.mdb") = ""
    DoEvents
    Loop

  8. #8
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database already opened (Access 2000)

    Thank you very much for your code.I can now run the function but only once.After that i am stuck again.I think i know the
    reason.During the execution an error windows appears which i have not seen up to now, because it is minimized.It reads as follows:


    C:ProgrammefilesWinzipWZUnzip.exe
    WinZip® CommandLine Add-On version 1.1
    Zip file C:BEDepotssurvey.zip
    C:BEsurvey.mdb exists. Overwrite < [yles,[n]o,[all,[quit> ?


    I can run the function again if i close this error message window.
    I have tested the function 10 times and i am convinced now that this error window is the source of my headaches.
    Is there any remedy?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database already opened (Access 2000)

    The command line add-on for WinZip comes with a help file. This help file contains the information you need to solve problems like this. You can add the option -o to the WZUnzip instruction to make it overwrite existing files without asking (that is, answer Yes to the prompt automatically; if you use -o-, it will answer No automatically). The option must be inserted between WZUnzip and the specification of the zip file:
    <code>
    Shell """C:Program FilesWinZipWZUnzip.exe"" -o C:BEDepotsDepotSo.zip C:BE"
    Shell """C:Program FilesWinZipWZUnzip.exe"" -o C:BEDepotsSurvey.zip C:BE"
    </code>
    Note: the option uses the letter o, not the digit (number) 0.

  10. #10
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you !

    Now it is running ! Now it is running ! it works at last !My special regards and thanks for all that.it was really very
    tough for me.I wish you a nice weekend
    Samotek

  11. #11
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database already opened (Access 2000)

    Dear Hans,

    I refer to your marvelous solution for opening the database from code.I am turning to you with the following question.When i put it in the OnClick event of a form , write the line of code you have given and add Application.Quit
    then evrything is Ok. However when i use it in the OnOpen event of a form, being opened by an autoexec macro in the database, then my database sticks, and i have to log off after numerous effrots.What may be the reason for
    this phenomena?

    kind regards

    Samotek

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database already opened (Access 2000)

    Why would you want to open another database when your database opens? That doesn't make sense to me.

Posting Permissions

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