Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open another database (2000)

    I would like to use code behind a button on a form to open a different database. I tried using DoCmd.RunCommand accmdOpenDatabase but I get an error message that says the command or action 'OpenDatabase' isn't available now. I want to mimic the action of manually selecting File, Open, and choosing the database to open. I have no problem with the current database closing which is what happens when you manually open another database.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    Good Morning Judy...

    You can use the Application object in the button's On Click event...

    Dim appAccess As Object
    Set appAccess = CreateObject("Access.Application.8") 'this is for Access '97... I'm pretty sure you'd have to use .9 in 2000 (don't have it here to check... )

    or

    Dim appAccess As Access.Application

    Here's a sample procedure I found in Access Help...

    Sub DisplayForm()
    Const strConPathToSamples = "C:Program " _
    & "FilesMSOfficeAccessSamples"

    Dim strDB As String

    ' Initialize string to database path.
    strDB = strConPathToSamples & "Northwind.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = _
    CreateObject("Access.Application.8")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB

    ' Open Orders form.
    appAccess.DoCmd.OpenForm "Orders"
    End Sub

    HTH

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

    Re: Open another database (2000)

    It depends on what you mean by "open another database". I know this sounds Clintonesque, but it does depend on what you want to do. Do you mean shutting down the current database and opening another? Do you mean opening a 2nd database that you will control control from the first database? Or do you just want to open another instance of Access with a different database?

    If the latter, you can use the Shell function.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    I tried modifying your code as follows and when I run it I get a run-time error #429 (ActiveX component can't create object)

    Private Sub Redii02_Click()
    Dim appAccess As Access.Application
    Const strConPathToSamples = "N:RediiPastYears"
    Dim strDB As String
    ' Initialize string to database path.
    strDB = strConPathToSamples & "Rediir02.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application.9")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB
    End Sub
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    Shutting down the current database and opening another would work just fine. I want the user to be able to open a past year's database. When the user gets through working in the past year's database they can exit and then reopen the current year in the same way they currently do.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Open another database (2000)

    Judy,

    Try the OpenCurrentDatabase method. Here's the example from on-line help:

    <pre>' Include following in Declarations section of module.
    Dim appAccess As Access.Application

    Sub DisplayForm()
    ' Initialize string to database path.
    Const strConPathToSamples = "C:Program " _
    & "FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb"

    strDB = strConPathToSamples & "Northwind.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = _
    CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strConPathToSamples
    ' Open Orders form.
    appAccess.DoCmd.OpenForm "Orders"
    End Sub</pre>

    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    That's exactly the example I gave Judy above Charlotte... She says she's getting a run time error (429)...
    Works fine for me?... What do you think is going on there?

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

    Re: Open another database (2000)

    The code is almost, but not exactly the same; the difference is that Charlotte uses CreateObject("Access.Application") instead of CreateObject("Access.Application.9")

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    Oh... OOPS! ... <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Sorry Charlotte! <img src=/S/shy.gif border=0 alt=shy width=15 height=15>

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    I was able to modify Charlotte's code and get it to open my past year's database and much to my surprise it also left my current database open so that when I closed my past year's database I was back to the form in my current database. The funny thing that happens that I can't figure out, is when the past year's form screen opens it is maximized in size but instead of being centered on the screen as it normally is, it appears off center to the left so that part of the initial form screen is not visible.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Open another database (2000)

    >>Shutting down the current database and opening another would work just fine. <<

    Judy,

    Unfortunately, this is not as easy as we think it should be! Using the Shell command would allow you to open that 2nd instance of Access on top of the first one. But to actually close the first one and open the 2nd is another story. I think I once saw a solution at Stephen Lebans website , but I'm not sure. You might try checking it out.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Open another database (2000)

    1. The code from Alexya/Charlotte starts another instance of Access and opens the second database in the new instance; if you look at the Windows task bar, you'll see that the original database stays open.

    2. If you start an application using Automation, it doesn't seem to heed the window settings of that app; I haven't found a good workaround that solves this reliably.

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    Thanks to everyone for their help. I have three past years and now a user can select to view any of those years. and when they exit, they are back where they started. I even discovered that the reason my form was showing off center was that the right side of the form was much wider than necessary. Once I moved it back to 8" wide, the form opens centered just as it is supposed to.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open another database (2000)

    I'm trying to do the same thing with my database. I have a button on a form that I want to click to open another Access database that has the previous years data in it. I tried the code you suggested, but I just can't seem to get it to work. Can you see what I'm missing?

    Private Sub Command0_Click()

    Dim appAccess As Access.Application
    Const strConPathToSamples = "G:JEESJennie Emery2003-04-Correct.mdb"
    Dim strDB As String
    ' Initialize string to database path.
    strDB = strConPathToSamples & "Jennie Emery2003-04-Correct.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strConPathToSamples
    ' Open Main Switchboard
    appAccess.DoCmd.OpenForm "Switchboard1"

    End Sub

    I don't know if this helps, but the G drive on my computer is a web server that I have to log into. The database that I'm putting the button in is on that drive and the database that I want to open is on that same drive in the JEES folder. Thanks so much!

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

    Re: Open another database (2000)

    There seems to be a duplication of the database name here:

    Const strConPathToSamples = "G:JEESJennie Emery2003-04-Correct.mdb"
    strDB = strConPathToSamples & "Jennie Emery2003-04-Correct.mdb"

    I think strConPathToSamples should only be the path "G:JEES". The code you have now will result in

    strDB = "G:JEESJennie Emery2003-04-Correct.mdbJennie Emery2003-04-Correct.mdb"

    Although that shouldn't matter for opening the database. What is the exact error you get, and where?

Page 1 of 2 12 LastLast

Posting Permissions

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