473,326 Members | 2,125 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Starting MS Access from vba

119 100+
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
30 8283
FishVal
2,653 Expert 2GB
Hi, there.

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

Regards,
Fish
Oct 23 '07 #2
ADezii
8,834 Expert 8TB
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
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.
Oct 24 '07 #4
billelev
119 100+
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
8,834 Expert 8TB
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
billelev
119 100+
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
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?
Oct 26 '07 #8
billelev
119 100+
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
8,834 Expert 8TB
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
billelev
119 100+
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
8,834 Expert 8TB
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
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 :
  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
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.
Oct 27 '07 #14
billelev
119 100+
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
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.
Oct 29 '07 #16
billelev
119 100+
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
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.
Oct 29 '07 #18
MMcCarthy
14,534 Expert Mod 8TB
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
billelev
119 100+
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
14,534 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
billelev
119 100+
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
32,556 Expert Mod 16PB
But was the second database itself opened by the first (the one whose form you opened)?
Oct 31 '07 #24
billelev
119 100+
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
14,534 Expert Mod 8TB
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
billelev
119 100+
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
14,534 Expert Mod 8TB
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
billelev
119 100+
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
2,653 Expert 2GB
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
14,534 Expert Mod 8TB
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

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

Similar topics

0
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,...
1
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...
3
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...
4
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: ...
2
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...
2
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...
0
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...
2
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...
0
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...
1
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)...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.