running Access function/code from external application

[jan.simpson]

Colleagues

I have a complex Access F/E with SQLServer B/E.
This ACCDB can send, via shell, commands to our GIS (Geographic Information System) activating search/find/display functions within the GIS. Data/parameters are sent via Access VBA and runs code within the GIS.


I now want to do the reverse. Select map objects and send those details to the ACCDB to processing.

I have created functions/code to process the data, but this requires a 'click' wtihin the ACCDB.


In the past I have used the command line "MSACCESS.EXE dbname /x macroname" option to run code at start-up or for automation.
Using the same technique for this application, a 'new' instance of ACCESS opens. This technique creates multiple instances of the F/End.

Is there a way to send a command to ACCDB from an external source that will use the 'existing or currently running instance of ACCESS' if one exists, or if not, load a new instance? In the 'old days' with previous versions of ACCESS I thought there were some DDE type settings or commands.

Any help and or snippets of code would be greatly appreciated!

Thanks !

Jan.Simpson

[JamesDeckert]

>>This technique creates multiple instances of the F/End.
Is there a problem with this? You could have Access close when done processing.

You may be able to put a timer in a form to look to see if processing needs to be done.

I don't know of a way to use an existing instance.

[jan.simpson]

thanks James. The problem is multiple instances of Access will be loaded and thus, confuse users.
This ACCDB is used by 150+ users concurrently.
Technically, the impact of multiple instances of ACCDB is minimal on SQLServer, but the user could have many instances of the same form open, leading to untidy UI.

I suppose I could have a 'special form' to display data from the 'external data' link, but the preference would be for one instance of Access with one copy of the form opened. I've also toyed with Modality, but that makes things worse.

The form must remain open while the user inspects the data/results of the data/query sent from the GIS, so a OnTimer closing would not work.

I could have a difference FE exclusively for reading the data from the GIS and when the user closes the form, close ACCESS, but again, that's a bit 'tacky'.

Thanks for your suggestion

[June7]

With a split db, each user should run their own copy of the frontend, not the same file, so there are not multiple instances of the same file. User runs the copy from a location on their local workstation.

[ssanfu]

Select map objects and send those details to the ACCDB to processing.

Not sure of your process.... does the GIS save the data in a CSV file?

I have a complex Access F/E with SQLServer B/E.

So why bother with Access? Select any language (VB, C, C++, C#, ...) that can connect to SQL Server (SQLS), open the CSV file, process that raw data and insert the processed data into SQLS in the proper table(s) in the proper sequence.

In the past I have used the command line "MSACCESS.EXE dbname /x macroname" option to run code at start-up or for automation.

I know VB can be compiled into an executable which could be executed as above........

[jan.simpson]

Thanks SSANFU
I have restrictions on what tools can be used, and besides, THIS is an ACCESS Forum.

I have resolved the issue using DDE. I initiate a DDE connection to the ACCDB and invoke objects from within the ACCDB

here is a sample

sMdb = "C:\SharedData\DATABASE\PLI_STAS_SqlServer.ACC DB"
OnError Goto WakeUpAccess
Start:
hChan = DDEInitiate ("MSAccess", sMdb)
DDEExecute hChan, "mi_lotplan" 'run this macro
DDETerminate hChan
Exit Sub

WakeUpAccess:
If Err()=697 Then
nResult = ShellExecute (0, "open", sMdb, "", "", SHOWMINIMIZED)
Resume Start 'try again
End If

[ssanfu]

I have restrictions on what tools can be used, and besides, THIS is an ACCESS Forum.

I am well aware that this is an access forum. (I have over 4,000 posts here)

I suggested a work-around since you didn't like the multiple instances of access being opened.

Glad you were able to solve this.

And thanks for posting your solution.

[June7]

Did you see post 4?

[jan.simpson]

Yes, thanks June7. Each user has their own F/E.
The issue was I was trying to use a shell with a command line to open Access and execute an macro (.. /X macroxxx) and thats what was causing a NEW copy of Access to load. The .. /X macroxxx function actually did the job correctly, but I 'thought' the OS and Access would recognise an existing instance was running.

The DDE option (post #6) works perfectly, I just needed to find it!

Thanks!


JS