Opening one database from another | Newbie | | Join Date: Nov 2009
Posts: 5
| |
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.
| |
best answer - posted by 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
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,176
| | | re: Opening one database from another
Is there code in the other database? Is the other database in a trusted location or do you get security prompts?
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,747
| | | re: Opening one database from another
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).
| | Newbie | | Join Date: Nov 2009
Posts: 5
| | | re: Opening one database from another
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?
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: Opening one database from another
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
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,747
| | | re: Opening one database from another
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.
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: Opening one database from another
Quote:
Originally Posted by 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. The reason I recommend this is because I think this should work across all versions of access.
-AJ
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,747
| | | re: Opening one database from another
Are there any where Shell() is unavailable then AJ?
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: Opening one database from another
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
| | Newbie | | Join Date: Nov 2009
Posts: 5
| | | re: Opening one database from another
Thanks for the sugestions, I've tried them and the second database still won't remain open
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,176
| | | re: Opening one database from another
Has the second database been changed to 2007 format also? I'm suspicious of the code that runs in the second database on startup.
| | Newbie | | Join Date: Nov 2009
Posts: 5
| | | re: Opening one database from another
Yes, everything has been changed over to the 2007 format.
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,176
| | | re: Opening one database from another
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.
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,176
| | | re: Opening one database from another
It seems the new Access.Application is closed when the current Function or Sub ends. I'm thinking the only way around this would be to declare a global Access.Application 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?
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: Opening one database from another
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
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,176
| | | re: Opening one database from another
I was able to open my other DB manually with no problem. I'll try Shell today, and maybe a global application object and see how it goes.
On that topic, is there some reference you would recommend where I can learn about declaring functions like Shell, and what other functions are available? I'm not sure where these come from.
| | Newbie | | Join Date: Nov 2009
Posts: 5
| | | re: Opening one database from another
I got the shell method to work, I spent last night going over it and the dummy here should learn how to type. Thank you all for the help in this.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,747
| | | re: Opening one database from another
Quote:
Originally Posted by 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! =) Sorry for the delay responding. Good motto - Like the slight adjustment there :D
My only preference is that one is an inbuilt Access function while the other involves OS calls. Not a problem, but personally I always go for the inbuilt where available. It involves less fiddling with declarations etc.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,747
| | | re: Opening one database from another
Quote:
Originally Posted by ChipR On that topic, is there some reference you would recommend where I can learn about declaring functions like Shell, and what other functions are available? I'm not sure where these come from. I'm not clear which version you're referring to here Chip. - If it's Shell(), then nothing is required. It's found in the VBA library.
- If it's ShellExecute(), then this is a little more complicated as you need to declare this function as something defined elsewhere. I'm not sure where ShellExecute() is defined, but I can give an example of a similar function I use in my OS library.
- Private Declare Function CreateProcessA Lib "kernel32" ( _
-
ByVal lpApplicationName As Long, _
-
ByVal lpCommandLine As String, _
-
ByVal lpProcessAttributes As Long, _
-
ByVal lpThreadAttributes As Long, _
-
ByVal bInheritHandles As Long, _
-
ByVal dwCreationFlags As Long, _
-
ByVal lpEnvironment As Long, _
-
ByVal lpCurrentDirectory As Long, _
-
lpStartupInfo As typStartupInfo, _
-
lpProcessInformation As typProcInfo) As Long
NB. Because these OS provided functions are generally written to a standard that VB doesn't support natively, a bit of creative declaration is required. EG. lp... refers to Long Pointers and in VBA we need to store it in a simple Long. We cannot use these values as such, but can only pass them across.
The good news is that most functions have examples out there on the web of how they can be used in VBA. Furthermore, the Lib "kernel32" part is explicit and means you don't need to add a reference to any VBA library.
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: Opening one database from another
Quote:
Originally Posted by NeoPa Sorry for the delay responding. Good motto - Like the slight adjustment there :D
My only preference is that one is an inbuilt Access function while the other involves OS calls. Not a problem, but personally I always go for the inbuilt where available. It involves less fiddling with declarations etc. I definitely agree, but I have been burned a couple of times using the inbuilt functions. Example, I was using the Environ("Username") and for some reason it didn't work on some PCs and because i couldn't figure it out, so I resorted to... - Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
So I guess I am a little biased, cause I don't like being burned. =) Quote:
Originally Posted by NeoPa Good motto - Like the slight adjustment there :D Well my professor always told me:
1. Your code must be functional, and effective.
2. It must be a good design.
So I built that motto around that. =D
-AJ
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
