QUOTE (jleach @ Apr 20 2011, 04:47 AM)
An EXE is probably the ideal way to go, but if I get this working I'd like to post a VBA example to one of our code repositories. Occasionally the question pops up in the forums and so far the answer is "no, can't really do that with Access", so I'd like to change that if possible. Concerning Access/Jet in the exe, I'm actually hoping to be able to give the option to run Functions, SQL Strings or QueryDefs from the host app. This would demonstrate the possibilities, though certainly I agree that an exe interacting with jet alone to process some SQL would be far more lightweight.
You need to take in account that VBA is not really made to be multi-threaded or work inter-process communications. Thus, there's no built-in mechanism for handling thread safety and transversing processes. I'm sure there's APIs that help you do this but it remains you'd be interacting with a piece of code that isn't totally thread-aware, making it very fragile than if you used an EXE of your own. I'm not saying it can't be done, just that it takes much more caution. A while ago, I saw someone talking about enabling asynchronous calling by providing two different DLLs; one to submit requests, other to receive events. (Why two DLLs was needed, I can't remember). The idea would be that you'd call the request, and register an event handler to be notified. That was for VB6, though.
(can you run a function based on a pointer to it's location from a separate process without having to instantiate another instance of Access?)
Yes, passing a function pointer is possible but only to API. I do this with EnumChildWindows API; here's a sample. (Sorry, this is for VBA7, but you can make it compatible with older Access by removing the "PtrSafe" and replacing all "LongPtr" with "Long"
Public Declare PtrSafe Function EnumChildWindows Lib "user32" ( _
ByVal hWndParent As LongPtr, _
ByVal lpEnumFunc As LongPtr, _
ByVal lParam As Long _
) As Long
Public Function CallEnumChildWindow(hWnd As LongPtr) As LongPtr
EnumChildWindows hWnd, AddressOf EnumChildProc, &H0
Public Function EnumChildProc(ByVal hWnd As LongPtr, ByVal lParam As Long) As Long
Here, I passed in the pointer to function EnumChildProc which is then called back by the API for each windows enumerated.
The documentation says you can't use AddressOf operator within VBA code and so far, I've never figured if there's a workaround to pass a function pointer within VBA.