Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing mdbs (Acc2k VBA)

    Hi there all!

    I am in DB1 and I am wanting to open DB2, open the form DB2 and start running some VBA code in it. The VBA code in DB2 must then close DB1 so that it can do a compact and repair on it.

    I have got the VBA code to do the compact and repair, I just do not know how to get the VBA code in DB1 to open DB2 and start running the VBA code in it, and for the VBA code in DB2 to close DB1.

    Does anyone know how to do this?

    James

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing mdbs (Acc2k VBA)

    James,
    This code will get your DB2 open but you may have to be innovative once the code is running in DB2 as to how to check the the code in DB1 has terminated and therefore allows you to run the code in DB2 on DB1.

    Dim App as Object
    Dim strDB1Path As String, strDB2Path As String
    strDB1Path = CurrentProject.name
    strDB2Path = "whatever the target db path is"

    Set app = CreateObject("access.application.9")
    app.OpenCurrentDatabase (strDB2Path)
    app.run yourprocedurename, arg1, arg2...
    set app = nothing

    The Problem with this approach is if you need to pass wrkgrp info or other command line switches you can't.

    So

    Dim lngReturn As Long
    '--using the shell method allows us to pass the wrkgrp and user info--

    lngReturn = Shell("C:Program filesMicrosoft officeOfficemsaccess.exe" /wrkgrp (your wrkgrp path) /NoStartup "strDB2Path in brackets here" /cmd (pass an value to the database here, perhap DB1Path for use in you code in DB2 ))
    DoEvents

    Set app = GetObject(strDB2Path)
    app.run yourprocedurename, arg1, arg2...
    Set app = nothing
    docmd.RunCommand acCmdExit

    DB2 will do its thing and DB1 will close. You could use the argument passed in the cmd variable in your autoexec macro or startup form in DB2 to point back at the calling DB1 perhaps.

    It is a bit sketchy but hopefully it gives you a starting point.

    Cheers
    Simon

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing mdbs (Acc2k VBA)

    Thanks very much, i'll play with this on Friday, Monday and Tuesday and see how it comes how!

    James

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing mdbs (Acc2k VBA)

    Hi Simon,

    I have tried this out and I've almost got it working the way it needs to.

    The catch is that the code in DB1 wants to wait until the code in DB2 is finished and is closed before continuing. This causes a bit of a problem as the code in DB2 does a compact and repair on DB1, and since DB1 is open - DB2's code fails.

    Any suggestions of what to do?

    James

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing mdbs (Acc2k VBA)

    Why don't you simply set the second database to compact on close?
    Charlotte

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing mdbs (Acc2k VBA)

    I thought this might be a problem but wasn't sure. The only thing I can think of is in DB1 open DB2 using the shell command I gave you therefore creating no reference in DB1 to DB2. If you plan using this compact routine from different DBs then you can pass the calling DB's path in the /cmd command line switch of the shell command thereby telling DB2 which DB to work on.
    After calling the shell command in DB1 immediately use the docmd.acruncommand accmdExit or similar which will close DB1.
    Hopefully DB1 will close before your routine in DB2 tries to access it.

    In DB2 start your compact routine from an autoexec macro or startup form or similar. Get the target DB path from the /cmd argument using the Command function. You could trap the error created by DB1 not being ready then check it again after a small interval.
    I don't know if VB has a sleep function so try this api call
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    so you would construct a loop to run x times and get it to pause at the end of each loop with the sleep call..

    Sleep (500)
    then try your compact again and exit the loop if you are successful.

    Hope this helps.
    Simon.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing mdbs (Acc2k VBA)

    Got it working fine thanks!

    James

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing mdbs (Acc2k VBA)

    Finally got it working thanks!

    James

Posting Permissions

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