Results 1 to 14 of 14
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Command() Not Working if Another Database is Open

    So I have a database (Access 2016) whose sole purpose is to see if the database I want to open has a newer version available, called "UpdateAndRunDB.accdb". I call it from a shortcut with file path to itself with a command argument such as "/Cmd XXX" where XXX is a code so it knows which database to run.

    The Main code uses Command() function to get the real value for the "XXX" argument, finds it in a table, which also contains the path to the database represented by the "XXX" and runs that database. So far so good...



    If I happen to have another database open at the time, the code errors out because the value returned by the Command() function is null or empty. If I close the other database and re-run this one, all works fine.

    Can anyone suggest why the Command() function doesn't work if another database is open?

    Thanks...

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    without seeing the code and the parameters being passed, I can only guess why it fails with another db open except to speculate that there is a lock on a record or table that prevents a value from being returned.

    Watch out for trailing spaces being passed to the Command function. I have experienced a specific (at least a minimum) string of characters may be passed and can exceed your cmd string. I passed "Otto Mayshun" (my attempt at some programming humor) and the function received "Otto Mayshun " thus the test for "Otto Mayshun" failed until I used the Trim function.

    Maybe there is an easier way to do what you're doing, which appears to be forcing FE updates. Shouldn't each user have 1 shortcut for each FE application which checks for a version number in the BE tables and let that be the determining factor?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks for the quick answer Micron.

    Here is a code snippet:

    Code:
        Dim pgm         As String
        Dim msgFlags  As Long
        
        pgm = Trim$(Command())
        If Nz(pgm, "") <> "" Then
            'do stuff here
        Else
            msg = "Program wasn't called with proper argument" & vbCrLf _
                & "Ex: ""/cmd FC5"" or ""/cmd FWS"""
            msgFlags = vbOKOnly + vbExclamation
            MsgBox msg, msgFlags, "Error"
        End If
        Quit

    I'm leaving out all of the "do stuff here" code, because it's not pertinent to the problem. But basically, it uses the 3-digit (or so) command line argument to read a table to get the path to the DB I'm trying to run, and also the path to the "distribution" DB (where an updated version would be if there was one).

    The issue doesn't appear to be related to a record lock or similar database thing, because the issue happens well before it tries to read any tables. The command line argument is simply null or "", because I'm getting the exact message shown in the code. Plus, I put a Stop command before the If command shown in the code above, and the Command() value is indeed "" or Nothing when I tried to print it in the Immediate window.

    I don't think there is a problem with leading or trailing spaces, because it works properly if no other database is open at the time. I think it is actually an Access bug or issue that prevents the command line argument from being passed if Access is already open.

    As far as using a different shortcut for different applications, that would force me to duplicate the code I'm using now for each and every new database I need to deploy with the customers, right? This seems to be more efficient, just write the maintenance code once and let it work for every database. I realize this is a design decision, and there can be as many of those as there are programmers. But either way, the issue is happening before I get to that place in the code. Additionally, if there is really an Access bug, it would happen whenever I opened a DB that used the Command() function if another DB was already open, right?

    BTW, the test of version numbers doesn't actually use table data. I create a custom property for the database called "DBVersion" and use code to poll that without explicitly opening a table (although, custom properties may in fact be stored in tables under the hood, so that comment may apply).

    Anyway, thanks again for the reply, hopefully we can figure this out.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't really understand how you're deploying, or what it means to 'deploy every new database to customers' so I can't offer any specifics. However, there's lots of forum stuff out there on the subject, and likely some are better methods than others or just better for your situation.

    If you develop a new FE for a db, the be is not affected, right? If you place a version number in a be table and upon opening, the fe compares that value to itself and they are different, the fe opening is halted and the user must download the new fe version. For that they have a utility shortcut on their desktop since the fe can't really replace itself while it is open. That shortcut knows what db to get. I don't understand why you are using a 'third party' db to manage this decision.

    Regardless, wrt your situation, I also don't understand what is calling/opening what or when.
    If I happen to have another database open at the time
    So at this point I figure you're saying user1 launches from shortcut, this "version" db opens and Command has a value, but if user2 "opens" version db while it is already open, Command gets no value because version db is already open. I suppose assigning Command value to a module level variable or TempVar won't help because user1 passes A and user2 passes B but the value of A will remain in effect. You cannot share access to data or procedures in this way (or at least as I'm envisioning your design) without one person upsetting things for another. You might get away with calling a function in this version db from user fe's and pass a value to to that function to determine what to do. There must be some fe db property value you could pass, or maybe move the Command decision to the fe. Again, not understanding deployment and what opens what means I'm just stabbing at this.
    Last edited by Micron; 07-01-2019 at 08:31 PM. Reason: clarification

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Hi Micron,

    I guess I didn't explain properly. Your last paragraph describing your understanding of my problem is incorrect. I will try to clarify.


    • BE database is up on the network somewhere, or in one case is on an Amazon cloud server. This isn't really pertinent.
    • FE database (and the UpdateAndRunDB database) is on each user's desktop. The shortcut points to the UpdateAndRunDB database and starts it with a short command-line option describing the actual database to be run.
    • The "Distribution" database (latest version of the FE database) is on a common network path.
    • When the UpdateAndRunDB database starts, its Main method gets the code which was sent in the command line switch, and uses that to read a record in its one table using that code as a key. In that table are the path-name of the FE database and the path-name of the "Distribution" (latest version) database.
    • The code retrieves the version number of the FE database and the version number of the "Distribution" database.
    • If the "Distribution" database has a later version number than the FE database, the user is offered the chance to upgrade to latest version. He/she can choose to update, or run his/her current version, or simply exit.
    • The FE database is replaced by the "Distribution" database if the user chose to do so.
    • Either way, the FE database is now started in a new Shell command, and the UpdateAndRunDB datase simply exits.


    Since I'm using a different DB to do the updating, there is no problems with the FE database trying to update itself.

    The issue is not because somebody else opened their FE or the BE database. It's related to each individual user.

    NOTE: You can reproduce this issue yourself as follows:


    • Create a test DB, tell it to run a Main method at startup
    • Put a message box in that main method (or a Debug.Print command), and display/query the Command() function's result.
    • Make a shortcut to start this DB, and put " /cmd Testing" after the path name in the shortcut.
    • Start the test DB from the shortcut. You should see the word "Testing" in your message box or immediate window.
    • Now quit this DB, then start any other database you want, then run this DB again from the shortcut. If you have the same issue, you'll see an empty or null Command() value.


    I hope I've clarified the problem, and you are able to reproduce it. Thanks again...

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I need to read your post 1 or 2 times more to better understand the process and why your way might be better than one shortcut opens one fe and the version number is compared to the be version number. So far, I think having a db to decide what db to open adds an unnecessary layer of complexity when the shortcut can handle this. In the meantime, this might take a different turn because my effort to replicate your issue failed. I created a form with a label to set the caption to command value because a message box is modal, thus arranging db windows is not possible. The tabcontrol db was opened first, then database1 from the shortcut. As you can see, the command value was passed to the label. Did I do this incorrectly?

    Click image for larger version. 

Name:	commandIssue.jpg 
Views:	18 
Size:	18.0 KB 
ID:	38982
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Hi Micron,
    Thanks for taking the trouble to test this. I'm not sure why you're not getting the same problem I am. I will continue testing. I would like to reiterate: The UpdateAndRunDB does much more than decide which FE database to run. It has code in it which locates the "distribution" copy of the particular FE database you want, gets its version, gets the version of the current FE database on the local PC, compares the versions, and if there is a newer one available, it offers to backup the local FE database and copy the distribution database to the local PC. If the local FE database doesn't exist yet, the code creates the default folder if it doesn't exist and then copies the distribution version down to local PC.

    There's a lot of stuff going on there, and if I simply put it in my FE database Main method, I would be duplicating it for every database I need this feature on. Also, it would be somewhat more complex to update the database, because I would be running code in it at the same time. In that case, I'd need a separate script or something to run to accomplish the update (you can't update a DB that you're running right?).

    Thanks again for your help. I will try to dig deeper on why I'm getting that issue and you are not.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you remain stuck, then maybe post a zipped db copy. If data is sensitive, only include what is necessary to replicate the issue or post entire procedure(s) that are involved, along with enough of the shortcut to see the command switch so as to be able to install it at my end.
    Good luck with your digging!

  9. #9
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Hi Micron,

    I created a very simple database which displays the string "TEST" on a form label. It is called from a shortcut I created by copying the database on the desktop, then right-clicking the desktop and choosing "Paste Shortcut". Then I edited the command of the shortcut by adding " /cmd TEST" at the end and saved the shortcut.

    When I run it, its AuoExec macro runs the Main() method which opens the form with OpenArgs equal to the Command() text. It displays the word TEST, but if I have another database open at the time and run the shortcut, it displays "No command string", which is what my code tells it to display if the Command() method returns empty string.

    I have attached the database, but the shortcut won't seem to upload. Once you save it, please create your own shortcut as above.
    Attached Files Attached Files

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Pretty sure it's your shortcut syntax. I'm rusty on this but here's what I think: you have to use the syntax of

    "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\Micron\Access\database1.accdb" /cmd testing

    Following your method, or creating a shortcut on the desktop and navigating to the file produces the same resulting syntax - just the file path, which is what I think you're doing. That reproduces the problem for me but using my syntax does not.

    In the midst of all this my pc crashed (low battery - no warning, I think) and I was lucky to be able to restore this post. However, acting poorly right now so I will leave it at this because I don't want to lose what I have so far, then I'll reboot. Test my theory and hopefully that will be the solution for you too.
    Last edited by Micron; 07-10-2019 at 01:19 PM. Reason: spelin and gramur

  11. #11
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks Micron, that solved the issue. It's curious that calling with file path only and letting file extension associations bring up the right program causes that problem.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Great that we solved it. Probably could've been sooner if i saw the shortcut syntax but we'll take satisfaction in having fixed it. At least I presume as much. I don't have a situation where multiple users are involved.

  13. #13
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    It's all you buddy. That's not an easy call to make, that bit about how the shortcut target is worded...

    At the risk of dead-horse-kicking, the purpose of this isn't necessarily to handle multiple users. Even if you have only 1 user at a company, but you have multiple databases they use, this will work to arbitrate whether they need to update a given database to a newer version or not. It is also an easy installer, because all you need is the UpdateAndRunDB database (with its tables loaded with the correct path information) and the shortcut. It will distribute the newest (or only) version of the FE database to the user's file system.

    Thanks again for the help...

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    U R welcome

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookUp command stoped working
    By WAVP375 in forum Access
    Replies: 5
    Last Post: 06-25-2019, 01:26 PM
  2. CarryOver Command is not working
    By Jchal3 in forum Programming
    Replies: 3
    Last Post: 05-10-2017, 03:24 PM
  3. Replies: 13
    Last Post: 01-22-2015, 05:27 PM
  4. IIf command not working
    By Lifendason in forum Reports
    Replies: 5
    Last Post: 02-10-2014, 03:03 PM
  5. Replies: 6
    Last Post: 09-14-2012, 11:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums