Does anyone know if it is possible to start Access from VBA and pass an argument to it?
I would like to start a database from vba, but have it behave in two separate ways depending on the situation (for example, start with a different form loaded). It seems like passing an argument with the shell command (or some other equivalent method) would work well, if possible.
Thanks.
30 8283
Hi, there.
Take a look at "OpenCurrentDatabase Method" Access help topic.
Regards,
Fish
Does anyone know if it is possible to start Access from VBA and pass an argument to it?
I would like to start a database from vba, but have it behave in two separate ways depending on the situation (for example, start with a different form loaded). It seems like passing an argument with the shell command (or some other equivalent method) would work well, if possible.
Thanks.
To Open another Instance of Access with the C:\Test\Test.mdb Database loaded, and pass to this Instance of Access an Argument defined by the strArgumentToPass Variable in the Current Database: - Dim strArgumentToPass As String
-
-
strArgumentToPass = "TheScripts is great!"
-
-
Dim retVal As Variant
-
retVal = Shell("C:\Program Files\Microsoft Office\Office\Msaccess.exe C:\Test\Test.mdb /cmd " & _
-
strArgumentToPass, vbMaximizedFocus)
To return the Argument passed to Test.mdb from the calling Database, use the Command Function wherever appropriate as in: OUTPUT NeoPa 32,556
Expert Mod 16PB Application Automation is also a possible answer to consider. I think the one provided already is probably better for you, but it depends on your exact requirements.
Great! Thanks for your help. I used the OpenCurrentDatabse method and it works well.
How would you open a form in an already open database from another vba project? Each possible method I have read about seems to require a new database being opened first, followed by the form. Presumably there is also a way to reference an already open database...
Great! Thanks for your help. I used the OpenCurrentDatabse method and it works well.
How would you open a form in an already open database from another vba project? Each possible method I have read about seems to require a new database being opened first, followed by the form. Presumably there is also a way to reference an already open database...
Use the GetObject() Function to reference a current Instance of an Access Database and manipulate its Objects.
Use the GetObject() Function to reference a current Instance of an Access Database and manipulate its Objects.
I've tried implementing the GetObject() function but I (sometimes) get the following error:
"Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user."
Any ideas how I can get round this?
When I don't get the error, GetObject() creates a new instance of the database. From what I have read it should create a reference to the object, rather than create a new object.
this is the code I am using: - Dim dbobject As Access.Application
-
strDB = gDatabaseFilePath & "db1.mdb"
-
Set dbobject = GetObject(strDB, "Access.Application")
-
With dbObject
-
.DoCmd OpenForm, "Main"
-
End With
NeoPa 32,556
Expert Mod 16PB
When you get the ...
Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user.
... do you try to open it independently to see if this is related to your code or if it's simply that the file is currently unavailable? (I know that's an obvious question but you'd be surprised how many even experienced developers miss the obvious)
When you say a new instance of the database is opened, what do you mean exactly? Does it create a copy of the database on the disk? If so what does it name it as?
When you get the ...... do you try to open it independently to see if this is related to your code or if it's simply that the file is currently unavailable? (I know that's an obvious question but you'd be surprised how many even experienced developers miss the obvious)
When you say a new instance of the database is opened, what do you mean exactly? Does it create a copy of the database on the disk? If so what does it name it as?
NeoPa, I'm not sure exactly what you mean by "open it independently"...but those errors no longer seem to be happening (and in any case, the other issue is more important for now).
I'll try to explain what I mean by new instance by outlining the steps my code goes through. If I look at the location of the first database (database1) I do not see any copies made.
1. Firstly, I load database1 (i.e. double click the icon in windows).
2. On frmMain (in database1) I click a button which runs vba code. This code closes frmMain and then opens frmAutoUpdate in database2. -
-
Dim strDB As String
-
strDB = gDatabaseFilePath & "database2.mdb"
-
Set appAccess = CreateObject("Access.Application")
-
appAccess.Visible = True
-
appAccess.OpenCurrentDatabase strDB
-
appAccess.DoCmd.OpenForm "frmAutoUpdate"
3. Database2 opens in a new Access window, updates some tables and then attempts to reopen frmMain in database1 using GetObject(). database2 then quits itself (application.quit) as follows: -
Dim dbObject As Access.Application
-
strDB = gDatabaseFilePath & "database1.mdb"
-
Set dbobject = GetObject(strDB, "Access.Application")
-
With dbObject
-
.DoCmd OpenForm, "frmMain" '(I'm also having problems with this, btw)
-
End With
-
Application.Quit
-
4. A new access window opens with a new "instance" of database1. After database2 has quit, this leaves me with two access windows with database1 open in both.
If I then try changing vba code in the original "instance" of database1, for example, I get the following message:
"Access can't save design changes or save to a new database object because another user has the file open..."
If I close the second "instance" of database1, I can then modify the vba code.
I hope this is helps to clarify the problem I am having...
I've tried implementing the GetObject() function but I (sometimes) get the following error:
"Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user."
Any ideas how I can get round this?
When I don't get the error, GetObject() creates a new instance of the database. From what I have read it should create a reference to the object, rather than create a new object.
this is the code I am using: - Dim dbobject As Access.Application
-
strDB = gDatabaseFilePath & "db1.mdb"
-
Set dbobject = GetObject(strDB, "Access.Application")
-
With dbObject
-
.DoCmd OpenForm, "Main"
-
End With
You syntax simply needs a little adjusting. The following code will Open the Form 'Main' in db1.mdb, within the same Instance, from an External Database: - Dim dbobject As Object, strDB As String
-
-
strDB = gDatabaseFilePath & "db1.mdb"
-
Set dbobject = GetObject(strDB, "Access.Application")
-
-
dbobject.DoCmd.OpenForm "Main"
You syntax simply needs a little adjusting. The following code will Open the Form 'Main' in db1.mdb, within the same Instance, from an External Database: - Dim dbobject As Object, strDB As String
-
-
strDB = gDatabaseFilePath & "db1.mdb"
-
Set dbobject = GetObject(strDB, "Access.Application")
-
-
dbobject.DoCmd.OpenForm "Main"
Unfortunately the adjusted syntax didn't work for me...I got the same results, namely a new access window with db1 open in it...hmmm...
Unfortunately the adjusted syntax didn't work for me...I got the same results, namely a new access window with db1 open in it...hmmm...
I realize that this is going to look utterly ridiculous, but you do have a Form named 'Main', in db1.mdb, don't you?
NeoPa 32,556
Expert Mod 16PB
I think the form sould be called "frmMain" shouldn't it?
Anyway, back to the logic flow. There are a number of issues here. I'm no oracle, but I believe the following to be true : - GetObject() returns an instance of the Access Application in the same way that CreateObject() does. This should certainly not be used for opening a separate database in the same instance of Access.
- A database whose code is running cannot be closed without the code stopping.
- From B, there can be no way (that I can even conceive) of controlling a process which includes work on the file (Compact/Repair; Backup; etc) within it (that is to say no way within VBA code).
- If this is your intention then you need to consider invoking a command line batch job (preferably a CMD script - BlahBlah.Cmd) to run the task. Your code would need to :
- Invoke the CMD file.
- Close down your current instance completely (at LEAST the database).
- Allow for the CMD file to open your database (probably with a special switch) so that it can continue on as if there had been no interruption.
NeoPa 32,556
Expert Mod 16PB
Actually, after further thought, it's not necessary to introduce a CMD file into this at all. If the second database can be invoked just before the first is closed instead, then the second database can re-open the first after it has done the job. This would, of course, still involve opening the first with a switch if continuation were required. If simply opening it again is all that's required then even that could be simplified.
The second db (also true for the CMD file by the way) must include checks to ensure the first db successfully closed before trying to start the process.
I think the form sould be called "frmMain" shouldn't it?
Anyway, back to the logic flow. There are a number of issues here. I'm no oracle, but I believe the following to be true : - GetObject() returns an instance of the Access Application in the same way that CreateObject() does. This should certainly not be used for opening a separate database in the same instance of Access.
- A database whose code is running cannot be closed without the code stopping.
- From B, there can be no way (that I can even conceive) of controlling a process which includes work on the file (Compact/Repair; Backup; etc) within it (that is to say no way within VBA code).
- If this is your intention then you need to consider invoking a command line batch job (preferably a CMD script - BlahBlah.Cmd) to run the task. Your code would need to :
- Invoke the CMD file.
- Close down your current instance completely (at LEAST the database).
- Allow for the CMD file to open your database (probably with a special switch) so that it can continue on as if there had been no interruption.
This seems to be getting more complicated than I anticipated! The difference in form names is an inconsistency in my explanation, not the coding. - At the point of trying to open the form in the first database, there are two Access windows open (this is what I call an 'instance'). I don't want to open a new database in the second Access window, just create a reference to the first database and manipulate an object in that database.
- Using Application.quit is fine for what I am trying to do as I want to change the other instance of the database before I quit (and believe I can, hopefully, maybe...).
- Database B should be able to control code in database A, though...Which is what I'm trying to do. Interestingly, I have been able to compact and repair a database through a user prompt on a form, so it is possible, but there are obviously limitations.
NeoPa 32,556
Expert Mod 16PB
OK.
Did you read points B & C from post #13.
I would guess this is where you're coming unstuck. Until you understand this then your attempts at controlling a Compact/Repair or Backup of the current database, whether or not via a separate database (and regardless of other instances of the application), are doomed to failure.
The ideas in the earlier posts were trying to find ways around this fundamental (O/S level) restriction.
Any process that needs an exclusive lock on a file (I believe that is required for both Compact/Repair & Backup) cannot possibly succeed if the file is currently running code.
OK.
Did you read points B & C from post #13.
I would guess this is where you're coming unstuck. Until you understand this then your attempts at controlling a Compact/Repair or Backup of the current database, whether or not via a separate database (and regardless of other instances of the application), are doomed to failure.
The ideas in the earlier posts were trying to find ways around this fundamental (O/S level) restriction.
Any process that needs an exclusive lock on a file (I believe that is required for both Compact/Repair & Backup) cannot possibly succeed if the file is currently running code.
I did, but I'm obviously not understanding the points that are being made, unfortunately. I don't believe I am attempting a compact/repair or backup of the current database, however, simply trying to open a form in it...unless that has a similar affect to a compact/repair process. My apologies if I seem to be being a bit difficult with this issue...I'm clearly not getting something...
NeoPa 32,556
Expert Mod 16PB
I expect it's actually me Bill. I find it very difficult to follow a thread of this length without the communication being clear and concise. I find myself having to reread things many times to understand what's being said and that's really quite impractical as there are so many threads I'm trying to deal with.
Your error messages seem to imply a locking problem of some sort. I was trying to follow through with that idea but as we are both misunderstanding each other, I'm finding it harder to know where you're at and what needs solving.
I don't want to give up on this but with the current information available in the thread I'm stuck.
Guys to go back to basics for a minute.
Bill
In Access go to the Tools - Options - Advanced tab. Assuming the Default Open Mode is currently set to exclusive try changing it to Shared. Its possible that the Jet engine is determining that trying to access database1 from database2 is another user and if its set to exclusive then it is locked.
Mary
Mary, thank you for your suggestion. I tried both shared and exclusive options, but it didn't change the way my code works/doesn't work. I managed to get a working solution (with the code below, in database2), but only conditionally. -
'=== This code in database2 used to open Form2 in database1
-
'=== This works if the only other database open when running this is database1
-
-
Dim dbObject As Object
-
-
Set dbObject = GetObject(, "Access.Application")
-
dbObject.DoCmd.OpenForm "Form2"
-
-
Set dbObject = Nothing
-
Application.Quit
-
I managed to get database2 to open Form2 in database1, but only if database1 was initially the only database open. If any other database was open before opening database1 (which then opens database2) the dbObject does not reference database1.
Now, I'm pretty sure the reason dbObject does not reference database1 is because the path argument was not supplied for the GetObject function. GetObject presumably defaults to one of the Access applications open, but never the one I want! However, the twist is that if I do include the path argument, a new instance of database1 is created, rather than pointing to the existing instance.
The following code shows the case for which the code should work, but does not. -
'=== This code in database2 used to open Form2 in database1
-
'=== This does not work, regardless of whether or not database1 is the only other open database.
-
-
Dim dbObject As Object
-
Dim strDB As String
-
-
strDB = "database1.mdb"
-
Set dbObject = GetObject(strDB, "Access.Application")
-
dbObject.DoCmd.OpenForm "Form2"
-
-
Set dbObject = Nothing
-
Application.Quit
-
So, in conclusion, it seems that the problem boils down to: - 100% of the time - how to reference an existing database without opening a new instance of that database?
- Understanding why GetObject creates a new instance of a database when the path object is supplied. I'm pretty sure, from the documentation, that this really shouldn't happen. See: (http://support.microsoft.com/kb/147816) under the 'Method1' heading.
I have to go out for a few hours.
However, I'll do some research when I get back and we'll see if we can find a solution.
Mary
OK, my research has led me to this conclusion.
If you start with Database1 and open Database2 you cannot then open Database 1 from Database2 because Database1 is already open.
You cannot close Database1 while running the code to open Database2 because Database2 has the focus.
This code will open Database2 from Database1 but unless Database1 is somehow closed (which cannot be done from Database2) you can't reference it with Application object. -
Dim strDB As String
-
-
' Initialize string to database path.
-
strDB = "C:\Documents and Settings\Mary McCarthy\My Documents\TSDN\dbs1.mdb"
-
-
' Create new instance of Microsoft Access.
-
Set appAccess = CreateObject("Access.Application")
-
-
appAccess.OpenCurrentDatabase strDB
-
appAccess.Visible = True
-
appAccess.DoCmd.OpenForm "frmTest1"
-
I'm not sure how you are going to get around this. The only solution I can see is to use one master database to open and close all instances of databases.
Mary
But I have been able to open a form in an already open database from a second database, as I described in my last post, so it is possible. The only difficulty is that I do not know how to reliably reference a particular database.
Whether it is worth all the effort is another matter! :o)
NeoPa 32,556
Expert Mod 16PB
But was the second database itself opened by the first (the one whose form you opened)?
But was the second database itself opened by the first (the one whose form you opened)?
Yes...The first opened the second, the second opened a form in the first.
Yes...The first opened the second, the second opened a form in the first.
Then if you got that to work you must have referenced the first database from the second. I seem to be confused about what your problem is here.
Prehaps you could clarify
Then if you got that to work you must have referenced the first database from the second. I seem to be confused about what your problem is here.
Prehaps you could clarify
Exactly - I did. However, because I referenced the database using:
GetObject(, "Access.Application")
I cannot be certain that the database that is being referenced is the database I want referenced. I tried to explain this in post 20...
Exactly - I did. However, because I referenced the database using:
GetObject(, "Access.Application")
I cannot be certain that the database that is being referenced is the database I want referenced. I tried to explain this in post 20...
And what I was trying to say was that you can't reference it directly in GetObject because its already in use by the code as it was used to open the current database. So all you are referencing is an instance of Access not of the database.
And what I was trying to say was that you can't reference it directly in GetObject because its already in use by the code as it was used to open the current database. So all you are referencing is an instance of Access not of the database.
Ah, I see. So I guess it is just fortunate (if that is the right phrase) that by referencing the instance of Access I can control some objects within the database I am interested in.
Thanks for all your help.
Hi, billelev.
I've just tested a code that seems to fit your needs.
The idea is quite simple: to let called application callback calling application, calling application has to pass reference to itself to called application. Main.mdb
Form: frm1 (has a button to open Child.mdb and open [frm1] within it) -
Private Sub Command0_Click()
-
-
Dim appChild As Access.Application
-
-
Set appChild = CreateObject("Access.Application")
-
With appChild
-
.Visible = True
-
.OpenCurrentDatabase "X:\Child.mdb"
-
.DoCmd.OpenForm "frm1"
-
' pass reference to Application object via custom property defined in
-
' [Child.mdb] frm1_Form module
-
Set .Forms!frm1.ParentApp = CurrentProject.Application
-
End With
-
-
Set appChild = Nothing
-
-
End Sub
-
Child.mdb
Form: frm1 (when closed, close [frm1] in calling application) -
Option Compare Database
-
Dim appParentApp As Access.Application
-
-
Public Property Get ParentApp() As Access.Application
-
Set ParentApp = appParentApp
-
End Property
-
-
Public Property Set ParentApp(ByRef appNewValue As Access.Application)
-
Set appParentApp = appNewValue
-
End Property
-
-
Private Sub Form_Close()
-
appParentApp.DoCmd.Close acForm, "frm1"
-
End Sub
-
Regards,
Fish
Thats a very nice piece of code FishVal. I would never have thought of it.
Do you fancy doing an article on this for the articles section?
Mary
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tom Vogel |
last post by:
This problem happens like every half our.
I can build or rebuild a project and start the debugger just fine. But every
now and then, a Confgiration Error, "Access is denied: 'myproject' is shown,...
|
by: Thomas Ruf |
last post by:
Hello!
Since days I'm looking for a solution for the following problem
for Access 2000:
Can I starting by Code a database B.mdb (protected only with a
simple database-passwort) from a...
|
by: Rachdi Anouar |
last post by:
Hello ,
when i start my database i have a window containing all tables form
macros,.. in the database
so how to disable this window ( I have a starting form wich i want to be
the only thing...
|
by: Adam |
last post by:
Eh, probably not the most specific subject line...
But, I've got a number of fields in which I need to do things like remove
the first 4 characters, or remove the last 3.
Such as:
...
|
by: karl |
last post by:
I have a windows service that kicks off a 'monitor' thread which in
turn kicks off 4 additional threads. These 4 threads basically are
listen on a designated socket and report back any errors...
|
by: Razzie |
last post by:
Hey all,
I wrote a Windows Service. When I test it on my developement machine (winXP)
it works fine. It starts ok, never crashes, etc.
When I install the service on another machine (win2000) it...
|
by: tshad |
last post by:
I have a Windows Service I created that just sets a timer and writes to
EventLog every 10 seconds.
It installed fine and it actually starts. But it says it doesn't. The
progress bar shows the...
|
by: Brent Borovan |
last post by:
Good day,
We are at the beginning stages of developing a new product which is
essentially a cutomized web application, where users will purchase access and
log into our site to use the...
|
by: Mark Henry |
last post by:
I wrote a Desktop class for manipulating desktops and window stations.
The class is being used by a service for starting applications on the
interactive desktop (the service cannot run as Local...
|
by: Okonita |
last post by:
Hi all,
I have gone through the process of installing DB2 UDBv9 Express-C in
my local linux environment. I am used to having icon placed on my
Desktop or taskbar for easy access to (db2cc)...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |