Opening one database from another

[tomric]

We have just "upgraded" to office 2007 form 2003. I have an access datbase that opens another database. Before the upgrade I would call and open the second database fine, now the second database just opens and closes. The code im using is as follows:

Dim strDB As String
    
    '  initialize string to database path
    strDB = "filepath and name"
    
    '  create new instance of microsoft access
    Set appAccess = CreateObject("Access.Application")
    
    
      'open database in access window
    With appAccess
            .OpenCurrentDatabase strDB
            .Visible = True
            .RunCommand acCmdAppMaximize
     End With

anybody have an answer to my problem.

thanks.

[ChipR]

Is there code in the other database? Is the other database in a trusted location or do you get security prompts?

[NeoPa]

I can't help you much I'm afraid.

I can link you to a thread which seems to be dealing with a similar matter (Run-time error 2501) and I can link a short article that goes through automating other Office applications (Application Automation).

[tomric]

No errors

There is code in the other database, and it's in a trusted location. I get no security promps or errors. Do I need to close the first database in order for the second one to stay open?

[ajalwaysus]

I'm afraid I cannot help you with this either, I do not use Access 2007, but no worries ChipR is our Access 2007 expert.

But if I may offer an alternative method of opening your DB, this is code I use to open DBs from a central switchboard I designed.

This function will declare the ShellExecute you will use to then open your DB:

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

Then if you run this code it should successfully open your DB:

ShellExecute 0, vbNullString, strDB , vbNullString, vbNullString, 3

Note: I don't run this in Access 2007, so I don't know for a fact that it will work, but it is worth a try.

-AJ

[NeoPa]

Good point AJ. I never considered the OP was happy to open the other database without keeping it under its own control.

If that is the situation then I expect a simple call to Shell() would also work quite adequately.

[ajalwaysus]

Good point AJ. I never considered the OP was happy to open the other database without keeping it under its own control.

If that is the situation then I expect a simple call to Shell() would also work quite adequately.

The reason I recommend this is because I think this should work across all versions of access.

-AJ

[NeoPa]

Are there any where Shell() is unavailable then AJ?

[ajalwaysus]

Oh I wasn't saying Shell() doesn't work, I was stating that I recommended Shell in general because I think this should work across all versions of access. Whether it is Shell() or ShellExecute(), I just recommended ShellExecute() because this is what I have used since I started working in Access professionally and I never had an issue, and I go by the Motto If it ain't broke, or badly designed, don't mess with it! =)

-AJ

[tomric]

Thanks for the sugestions, I've tried them and the second database still won't remain open

[ChipR]

Has the second database been changed to 2007 format also? I'm suspicious of the code that runs in the second database on startup.

[tomric]

Yes, everything has been changed over to the 2007 format.

[ChipR]

I just tried:

Set appAccess = New Access.Application
appAccess.Visible = True

A new Access window flashes up for a second, then closes. Now I'm interested.

[ChipR]

It seems the new Access.Applicat ion is closed when the current Function or Sub ends. I'm thinking the only way around this would be to declare a global Access.Applicat ion which you could manipulate in the function and leave open. This seems like it should be avoided if at all possible. Do you mind if I ask why this is necessary?

[ajalwaysus]

I have a question as well, if you used the shell function and that didn't work, can you open this DB manually?
Because as far as i know the Shell function is comparable to opening the DB manually, because you can use this function to open any file type.

-AJ