By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,997 Members | 1,470 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,997 IT Pros & Developers. It's quick & easy.

Starting MS Access from vba

100+
P: 119
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.
Oct 23 '07 #1
Share this Question
Share on Google+
30 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Take a look at "OpenCurrentDatabase Method" Access help topic.

Regards,
Fish
Oct 23 '07 #2

ADezii
Expert 5K+
P: 8,627
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strArgumentToPass As String
  2.  
  3. strArgumentToPass = "TheScripts is great!"
  4.  
  5. Dim retVal As Variant
  6. retVal = Shell("C:\Program Files\Microsoft Office\Office\Msaccess.exe C:\Test\Test.mdb /cmd " & _
  7.                 strArgumentToPass, vbMaximizedFocus)
To return the Argument passed to Test.mdb from the calling Database, use the Command Function wherever appropriate as in:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Command
OUTPUT
Expand|Select|Wrap|Line Numbers
  1. TheScripts is great!
Oct 24 '07 #3

NeoPa
Expert Mod 15k+
P: 31,429
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.
Oct 24 '07 #4

100+
P: 119
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...
Oct 25 '07 #5

ADezii
Expert 5K+
P: 8,627
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.
Oct 25 '07 #6

100+
P: 119
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:

Expand|Select|Wrap|Line Numbers
  1. Dim dbobject As Access.Application
  2. strDB = gDatabaseFilePath & "db1.mdb"
  3. Set dbobject = GetObject(strDB, "Access.Application")
  4. With dbObject
  5.    .DoCmd OpenForm, "Main"
  6. End With
Oct 26 '07 #7

NeoPa
Expert Mod 15k+
P: 31,429
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?
Oct 26 '07 #8

100+
P: 119
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim strDB As String
  3.     strDB = gDatabaseFilePath & "database2.mdb"
  4.     Set appAccess = CreateObject("Access.Application")
  5.     appAccess.Visible = True
  6.     appAccess.OpenCurrentDatabase strDB
  7.     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:

Expand|Select|Wrap|Line Numbers
  1.         Dim dbObject As Access.Application
  2.         strDB = gDatabaseFilePath & "database1.mdb"
  3.         Set dbobject = GetObject(strDB, "Access.Application")
  4.         With dbObject 
  5.              .DoCmd OpenForm, "frmMain"  '(I'm also having problems with this, btw)
  6.         End With
  7.         Application.Quit
  8.  
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...
Oct 26 '07 #9

ADezii
Expert 5K+
P: 8,627
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:

Expand|Select|Wrap|Line Numbers
  1. Dim dbobject As Access.Application
  2. strDB = gDatabaseFilePath & "db1.mdb"
  3. Set dbobject = GetObject(strDB, "Access.Application")
  4. With dbObject
  5.    .DoCmd OpenForm, "Main"
  6. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim dbobject As Object, strDB As String
  2.  
  3. strDB = gDatabaseFilePath & "db1.mdb"
  4. Set dbobject = GetObject(strDB, "Access.Application")
  5.  
  6. dbobject.DoCmd.OpenForm "Main"
Oct 26 '07 #10

100+
P: 119
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:
Expand|Select|Wrap|Line Numbers
  1. Dim dbobject As Object, strDB As String
  2.  
  3. strDB = gDatabaseFilePath & "db1.mdb"
  4. Set dbobject = GetObject(strDB, "Access.Application")
  5.  
  6. 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...
Oct 26 '07 #11

ADezii
Expert 5K+
P: 8,627
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?
Oct 26 '07 #12

NeoPa
Expert Mod 15k+
P: 31,429
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 :
  1. 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.
  2. A database whose code is running cannot be closed without the code stopping.
  3. 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).
  4. 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 :
    1. Invoke the CMD file.
    2. Close down your current instance completely (at LEAST the database).
    3. 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.
Oct 27 '07 #13

NeoPa
Expert Mod 15k+
P: 31,429
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.
Oct 27 '07 #14

100+
P: 119
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 :
  1. 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.
  2. A database whose code is running cannot be closed without the code stopping.
  3. 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).
  4. 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 :
    1. Invoke the CMD file.
    2. Close down your current instance completely (at LEAST the database).
    3. 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.
  1. 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.
  2. 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...).
  3. 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.
Oct 29 '07 #15

NeoPa
Expert Mod 15k+
P: 31,429
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.
Oct 29 '07 #16

100+
P: 119
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...
Oct 29 '07 #17

NeoPa
Expert Mod 15k+
P: 31,429
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.
Oct 29 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 30 '07 #19

100+
P: 119
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.

Expand|Select|Wrap|Line Numbers
  1. '=== This code in database2 used to open Form2 in database1
  2. '=== This works if the only other database open when running this is database1
  3.  
  4. Dim dbObject As Object
  5.  
  6. Set dbObject = GetObject(, "Access.Application")
  7. dbObject.DoCmd.OpenForm "Form2"
  8.  
  9. Set dbObject = Nothing
  10. Application.Quit
  11.  
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.

Expand|Select|Wrap|Line Numbers
  1. '=== This code in database2 used to open Form2 in database1
  2. '=== This does not work, regardless of whether or not database1 is the only other open database.
  3.  
  4. Dim dbObject As Object
  5. Dim strDB As String
  6.  
  7. strDB = "database1.mdb"
  8. Set dbObject = GetObject(strDB, "Access.Application")
  9. dbObject.DoCmd.OpenForm "Form2"
  10.  
  11. Set dbObject = Nothing
  12. Application.Quit
  13.  
So, in conclusion, it seems that the problem boils down to:
  1. 100% of the time - how to reference an existing database without opening a new instance of that database?
  2. 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.
Oct 30 '07 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 30 '07 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strDB As String
  2.  
  3.     ' Initialize string to database path.
  4.     strDB = "C:\Documents and Settings\Mary McCarthy\My Documents\TSDN\dbs1.mdb"
  5.  
  6.     ' Create new instance of Microsoft Access.
  7.     Set appAccess = CreateObject("Access.Application")
  8.  
  9.     appAccess.OpenCurrentDatabase strDB
  10.     appAccess.Visible = True
  11.     appAccess.DoCmd.OpenForm "frmTest1"
  12.  
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
Oct 31 '07 #22

100+
P: 119
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)
Oct 31 '07 #23

NeoPa
Expert Mod 15k+
P: 31,429
But was the second database itself opened by the first (the one whose form you opened)?
Oct 31 '07 #24

100+
P: 119
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.
Oct 31 '07 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 31 '07 #26

100+
P: 119
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...
Nov 2 '07 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 2 '07 #28

100+
P: 119
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.
Nov 2 '07 #29

FishVal
Expert 2.5K+
P: 2,653
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3.     Dim appChild As Access.Application
  4.  
  5.     Set appChild = CreateObject("Access.Application")
  6.     With appChild
  7.         .Visible = True
  8.         .OpenCurrentDatabase "X:\Child.mdb"
  9.         .DoCmd.OpenForm "frm1"
  10.         ' pass reference to Application object via custom property defined in
  11.         ' [Child.mdb] frm1_Form module
  12.         Set .Forms!frm1.ParentApp = CurrentProject.Application
  13.     End With
  14.  
  15.     Set appChild = Nothing
  16.  
  17. End Sub
  18.  
Child.mdb
Form: frm1 (when closed, close [frm1] in calling application)
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim appParentApp As Access.Application
  3.  
  4. Public Property Get ParentApp() As Access.Application
  5.     Set ParentApp = appParentApp
  6. End Property
  7.  
  8. Public Property Set ParentApp(ByRef appNewValue As Access.Application)
  9.     Set appParentApp = appNewValue
  10. End Property
  11.  
  12. Private Sub Form_Close()
  13.     appParentApp.DoCmd.Close acForm, "frm1"
  14. End Sub
  15.  
Regards,
Fish
Nov 3 '07 #30

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 3 '07 #31

Post your reply

Sign in to post your reply or Sign up for a free account.