Passing a command to a DB

[GraeagleBill]

In the segment of code below I'm opening a different DB from within the current. All is well except I need to pass a command expression to the mde file. I'm not finding a syntax for the options of the OpenCurrentDatabase method whereby I can pass the expression /CMD "blahblah".

When kScan.mde opens, the Autoexec macro runs automatically. The Autoexec macro runs a general module within the mde and it is that module that is looking for the value that would be returned from Command().

    Dim kScanApp As Access.Application
     
    Set kScanApp= CreateObject("Access.Application")
    kScanApp.OpenCurrentDatabase "C:\kScan\kScan.mde", False
        
    Set kScanApp = Nothing

Any ideas?

[isladogs]

Try opening the mde with a command switch /cmd ... to run the necessary code at startup or incorporate the missing item into the autoexec

See https://access-programmers.co.uk/for...d.php?t=176511

[Micron]

Did you consider Shell or Automation? Methinks Shell will take command line switches in the opening statement syntax. As for Automation, you can run macros (thus I presume functions as well) or open any form, so I suspect you can pass an argument to that function as well. Have to confess I've not done that so I'm just thinking out loud.
Can I say that if it's written and not spoken?

[GraeagleBill]

Thanks Colin, I had already tried to simply add the switch to the mde but Access didn't seem to recognize the need to parse the file name. I was tying to make the inter-communication between the functionality of the two DB's as simple as possible and passing a switch would have been the simplest. I already had a single record "MessageBoard" table that was used to communicate other information between the two DB's, so I added another field and a line of code so the open'd mde will respond appropriately to the setting of the new field.

            Dim kScanApp As Access.Application

            CurrentDb.Execute "UPDATE tblMsgBoard SET LinkType = 1 WHERE BoardID = 1"
            Set kScanApp = CreateObject("Access.Application")
            kScanApp.OpenCurrentDatabase "C:\kScan\kScan.mde", False
                
            Set kScanApp = Nothing

Thanks for your thoughts.
Bill

[GraeagleBill]

Micron, see my post #4 to Colin. My initial approach was a simple Shell command but unless there's a better path to the mde file other than via Explorer I thought it cumbersome and not to my liking, keeping within Access being my desires. The solution I chose is well documented within the code so there's no ambiguity viewing the internals.
Thanks,
Bill

[Micron]

unless there's a better path to the mde file other than via Explorer

you mean Internet Explorer or Windows File Explorer? I based my suggestion on information like below (bold formatting is by me):

Shell is one of those universal commands that allows one to do so much. Once again, it can be used to launch programs and open files. Typically, you need to supply the exe file and then whatever command line switches each individual exe recognizes. However, since office is registered and part of the path variable, we don’t even need to supply the path to the msaccess.exe file and need only provide the database path/file to open.

I wouldn't have guessed that the switch goes at the beginning of the call to open an Access database as in the example of the Shell code, but that's how they have it.
https://www.devhut.net/2018/01/21/ms...ther-database/

[GraeagleBill]

I did not try the configuration you describe:

Shell "cmd /c " & Chr(34) & sDb & Chr(34), vbHide

Rather, I used the form:

ShellRet = Shell(strWinExpl,strMyDB,3)

You have my attention and curiosity. I'll play with this again in the next day or so.

[isladogs]

Bill
Using a command switch you can run a procedure which means you should be able to do what you want if you get the syntax correct.

However there may be an easier way which means there is no need to open database 2 a all.
Why not just link the relevant table(s) to database 1 and run the same code from there.
If needed you can also run queries from database 2 without opening it -just specify the source database in the query properties.

[GraeagleBill]

Colin,
I spent some time this AM playing with the syntax of a Shell invocation trying to get the command switch recognized. The reference that Micron provided with putting the switch expression ahead of the DB2 file name got me nowhere as did when is was placed after the file name. The attempts included using double-quotes as well as Char(34) in delineating the switch value but still to no avail. There may very well be some syntax that Shell will handle properly but I couldn't find it. The failures, BTW, had to do with Win Explorer not being able to find the file so simply opened a window to Documents.

The code running in DB2 is proprietary, protected and cannot be duplicated in DB1 for the sake of convenience. It won't even run unless the current user is logged into the app.

I'm envisioning the added field to the message board table will come in handy at a latter time, so I'm going to leave that solution in place and move on.

Thanks for joining me in all the fun
Bill

[Micron]

There may very well be some syntax that Shell will handle properly but I couldn't find it.

Well, you goaded me on, and I did. I could tweak the code at the link to open a file, but I couldn't get it to pass the command value. On my system, the "cmd" part opened the CLI (command line interpreter/old DOS window) but it did close when it was finished, so I don't know if the problem was that "cmd" was being interpreted as being for the CLI rather than a command line switch for the file. What follows isn't what I found but it did clue me in that the executable part has to come first.

Public Sub OpenDb()
Dim exeLoc 'application executable path
Dim fileLoc 'file path

exeLoc = "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"
fileLoc = "C:\Users\Micron\Access\AccessForum\CompareCase\TEST.accdb /cmd OttoMayshun"

Shell exeLoc & " " & fileLoc

End Sub

If I go to the 2nd and type ?command in the immediate window, I get OttoMayshun (hope the joke isn't lost on you).
Might want to use string variables instead of variants. Also, note that the code at the link opened the file hidden, which is what I thought your problem was going to be.

[GraeagleBill]

Kind of like an itch that simply must be scratched

I just had to change the source of Access from what you posted and voilà! I believe my efforts failed largely because I was attempting to get Windows Explorer to open the mde file insteat of MSACCESS.EXE.

Time for a nap

[Micron]

Re Explorer: I saw that too but only when the path(s) weren't resolving as they should. Glad you were able to figure that out because I didn't think to see if your version was evident (I see now that it is), so yeah, that would be different for sure.
Nice to solve a thread that was already solved.

[isladogs]

Hi Bill
Sorry I missed your reply earlier
Good to see Micron has given you a solution in the meantime

Here's two other ways of doing something similar in case either is useful for future reference:

a) Open a database using a shortcut with a command switch
e.g. C:\Programs\MendipDataSystems\SDALink\SDALink.accd b /cmd "AttMarks"

Then in your Access app start up form

Private Sub Form_Activate()
    Dim ComLineArgs As String
    ComLineArgs=Trim(Command()) 'EDIT added missing line 

    If ComLineArgs = "AttMarks" Then        'run some code
    End If

End Sub

b) Use Task Scheduler to do this at a specified time - see attached Word doc supplied for a client school some years ago
Running SDA Link in Task Scheduler.zip

HTH

[Micron]

Mr. ridders52: I get the shortcut idea with the command line switch as I've done it in the past. I don't get why the variable in an opening form as you have it (no reference to Command property). From anywhere, you should be able to check for the value being null or "" thus you wouldn't need a variable, but you would need to check the database Command property value somehow by referring to it.

However, I wanted to check for a specific value so I did use a variable because Windows added a trailing space when the shortcut was invoked, thus the property value would be "AttMarks ", not "AttMarks".
So I had

Dim svCmd As String
svCmd = Command
If Trim(svCmd) = "AttMarks" Then

That's why my old code used a variable, but it also retrieved the Command property value. I'm wondering why yours doesn't, and why you didn't seem to need to Trim it.

[isladogs]

Hi Micron
Just about to log off as its late here. I tried to follow that but I failed. Could you explain again for me and i'll have another read in the morning...

So I'm not sure if this helps at all, but the app I used as an example can actually be run using 3 different shortcuts / scheduled tasks, each of which runs a different procedure so the full code in the startup form is

 If ComLineArgs = "ExportOnly" Then               
                ExportFilesFromServer
            ElseIf ComLineArgs = "AttMarks" Then
                RunAttendanceMarksUpdate
            Else 'run normally
                PerformUpdate
            End If