MS Access Notification in Windows 10 Action centre

[Accessanitor]

Hi all

As you cannot run threads in VBA, I was thinking of using a notification in the action centre to update the user with the status of the report. The report code runs for about an hour, so I want to update now and then just to indicate what the status is.

(I can roughly calculate the time when the report will complete, so I can say "x minutes before completion" or something like that, and then update this based on performance to date.)

I've tried a few examples but most I've seen were based on Excel. I got it to work in Excel but for some reason MS Access won't work.

So, a few questions:
1. Have you got sample code to run system tray notifications via VBA in MS access?

2. Is there a way to reset the action centre to allow all applications to send a notification? I think MS Access was turned off as I got it working at some point, but I might have sent too many notifications within a short time frame (Testing...duh) and the operating system intervened by blocking the app. How can I reset the app to send if it doesn't show up under the normal apps in the notification settings area?

3. Alternatively, is there a way keep a status bar running when code is performing? I've done this using threads in VB.NET but I want to run this code inside the VBA MS Access report without the need for an external application to run. (The end user should only have to click one button.)

4. Can you assign notifications to the action center of another computer/user on your network? In other words, when code is running on another machine and it has completed, can I inform a user on a different machine?

 

[jleach]

Hello - I'm not sure offhand about action center information from Access (my guess would be that this is possible via API calls, but I haven't looked yet...). Can you offer any direction on what you've tried so we have some sort of starting point?

Regarding "background processing" - have you considered firing up a background copy of Access to run the heavy lifting on the report? You can link up the two applications via automation so you can send notifications between them and thus implement a status bar in the UI process that can be triggered via the background process. I have an example here: https://dymeng.com/async-processing-in-access/

 

[isladogs]

Hi all

As you cannot run threads in VBA, I was thinking of using a notification in the action centre to update the user with the status of the report. The report code runs for about an hour, so I want to update now and then just to indicate what the status is.

(I can roughly calculate the time when the report will complete, so I can say "x minutes before completion" or something like that, and then update this based on performance to date.)

I've tried a few examples but most I've seen were based on Excel. I got it to work in Excel but for some reason MS Access won't work.

So, a few questions:
1. Have you got sample code to run system tray notifications via VBA in MS access?

2. Is there a way to reset the action centre to allow all applications to send a notification? I think MS Access was turned off as I got it working at some point, but I might have sent too many notifications within a short time frame (Testing...duh) and the operating system intervened by blocking the app. How can I reset the app to send if it doesn't show up under the normal apps in the notification settings area?

3. Alternatively, is there a way keep a status bar running when code is performing? I've done this using threads in VB.NET but I want to run this code inside the VBA MS Access report without the need for an external application to run. (The end user should only have to click one button.)

4. Can you assign notifications to the action center of another computer/user on your network? In other words, when code is running on another machine and it has completed, can I inform a user on a different machine?

In answer to some of your questions
1. See this post for code to create system tray messages from Access
https://www.access-programmers.co.uk/forums/showthread.php?t=295062
NOTE - it only works in 32 bit Access

2. No idea as its not happened to me

3. You can have status bar running or use a progress bar. However during that hour or so (!) I assume Access is locked up and unusable for other tasks

4. Apparently you can do this but I've not tried it. This smple database includes code for sending messages to other computers
https://www.access-programmers.co.uk/forums/showthread.php?t=295342
That also includes the system tray code

 

[Pat Hartman]

What are you doing that is taking an hour to run? Is it VBA? Has someone looked at it for efficiency?

 

[Accessanitor]

I'm reading through folders that contain data in the data storage area. The code reads the contents of specified text files in almost 20,000 folders on a remote machine. The information in these files are not saved in the SQL database, but we need to report their contents and link them to the MS access database that also talks to the SQL db. So I'm adding external data to the tables in my database. Reading every file is what takes so much time. If I only read a few hundred it doesn't take that long...

It looks like the link provided by ridders might do the trick, I'm testing now. I'll post the solution here once I'm done.

 

[Accessanitor]

This code works. However, I have a few more questions as I don't have a great understanding of the balloon popup code which I bastardized from another project.

1. I am unable to pass through another icon value, so the default displays MS Access icon. How do you assign a value to a variable of the type btIcon ?

2. What parameters do I change to make the notification stay in the action centre? This one appears at the bottom of the screen and then disappears again, which is fine for my current application but I'm curious as I might have other uses for this too.

3. How do set up an icon in the taskbar that can display tooltips and respond to a request?

 

[isladogs]

This code works. However, I have a few more questions as I don't have a great understanding of the balloon popup code which I bastardized from another project.

1. I am unable to pass through another icon value, so the default displays MS Access icon. How do you assign a value to a variable of the type btIcon ?

2. What parameters do I change to make the notification stay in the action centre? This one appears at the bottom of the screen and then disappears again, which is fine for my current application but I'm curious as I might have other uses for this too.

3. How do set up an icon in the taskbar that can display tooltips and respond to a request?

1. Which icon do you mean - icon in message or in system tray?
My demo has both of these - I used an exclamation for both.

If you read the code it should be obvious how its done

2. AFAIK you can't. This is by design - the message automatically disappears to make space for further messages. Of course you could run the code repeatedly at intervals of your choice so it keeps popping up

3. The tooltip code available using VBA is not as fully featured as that created by other languages. As a result, this means:
a) clicking on the system tray icon hides the tooltip message
b) the message is not interactive in recent versions of Windows (7/8/10) so AFAIK you can't click on it to perform an action (though in XP it was possible).
For example in the tooltip shown above I'd like users to be able to click the message to go to the website & download the new version ... but it can't be done
This is a major limitation which at some point I'm going to look into again

Coming back to your monster task of 20000 files to be reviewed:
a) could this be done automatically overnight when no one is around by running the database as a scheduled task
b) if you know the number of files (even approximately), you could easily use a progress bar to indicate how far the task has got

 

[Accessanitor]

If you read the code it should be obvious how its done

I've gone through the code and it's using btWarning as the variable to pass through to ShowBalloonTooltip

  ShowBalloonTooltip "INFO - New version available", GetProgramName() & " version " & intWebVer & _
             " is available for download from the " & GetCompanyName() & " website", btWarning

I have tried to find out where btWarning was defined elsewhere in the project but this is where I got stuck.

Can you please elaborate on how this variable is assigned to an icon?

Thanks

 

[Accessanitor]

Coming back to your monster task of 20000 files to be reviewed:
a) could this be done automatically overnight when no one is around by running the database as a scheduled task
b) if you know the number of files (even approximately), you could easily use a progress bar to indicate how far the task has got

1. Yes, that would probably help. How would you schedule a task like this, do you need to leave the MS Access application open or can you invoke this from outside MSA? However, manually updating the report should still be available to the end user. It is also dependent on another external process completing, so running this automatically while the other sync process (run by separate application outside of access) has not completed might introduce data errors.

2. In my previous experiments with this, the statusbar would not update while the code was running. I originally wrote the code in VB.NET and used threading to overcome this problem. As the form won't even update text while the code is running, I'm not sure a progress bar would perform any better though?

 

[jleach]

For #1, many people opt to use the Windows Task Scheduler and fire off a bat/cmd file which in turn starts up Access with an /x switch to run a specific macro which in turn calls your entry function. Myself, I've found this to be very finicky, but it can usually be bludgeoned into place if need be.

For #2, did you try putting DoEvents in your code? Include that in the file loop and that pauses the VBA code long enough to let the system catch up with its queue: most notably that includes repainting the Access UI. This is the most common use case for DoEvents. Look into it if you haven't already.

 

[sonic8]

Hello - I'm not sure offhand about action center information from Access (my guess would be that this is possible via API calls, but I haven't looked yet...).

That's going to be difficult. Action Center Notifications need to implement a COM Server to handle interaction with the toast messages in Action Center.
I haven't looked into it that deeply, but I guess the only option is to create some sort of proxy component written in .Net or C++ to handle the communication between your Access application and the Action Center.

 

[isladogs]

I've gone through the code and it's using btWarning as the variable to pass through to ShowBalloonTooltip

  ShowBalloonTooltip "INFO - New version available", GetProgramName() & " version " & intWebVer & _
             " is available for download from the " & GetCompanyName() & " website", btWarning

I have tried to find out where btWarning was defined elsewhere in the project but this is where I got stuck.

Can you please elaborate on how this variable is assigned to an icon?

Thanks

As you've realised, the code above adds the warning icon to the system alert.
The tray icon is added from Access Options - I chose the same icon but you can use whatever you like

1. Yes, that would probably help. How would you schedule a task like this, do you need to leave the MS Access application open or can you invoke this from outside MSA? However, manually updating the report should still be available to the end user. It is also dependent on another external process completing, so running this automatically while the other sync process (run by separate application outside of access) has not completed might introduce data errors.

2. In my previous experiments with this, the statusbar would not update while the code was running. I originally wrote the code in VB.NET and used threading to overcome this problem. As the form won't even update text while the code is running, I'm not sure a progress bar would perform any better though?

1. See reply from Jack Leach which was pretty much what I was going to write. However, I've used Task Scheduler for several years for exactly this purpose with several clients (schools) and its worked absolutely fine providing the computer is always on & the network connection is reliable

2. Again see Jack's reply.
I use a standard approach which is
a) set the maximum number of steps in the procedure e.g. total loop count
b) increment the counter after each step & use DoEvents
c) update the progress bar and / or the status bar
The only downside is a slight reduction in the speed of processing but I doubt it will matter in your case

See this post for an example progress bar of mine.
https://www.access-programmers.co.uk/forums/showpost.php?p=1556947&postcount=11

There are many others available on this site & elsewhere

 

[arnelgp]

found a way to change the TrayIcon
separate to the Message Icon.

on the sample two icons, red and green.
it is extracted by autoexec macro (so you must
allow it to run).

you should run both sample forms.

you are not limited to just one Tray Icon.
you can have as many and you must now pass
the Form's hwnd to ShowBaloonToolTip sub.

modified the code to also run in x64.
added some more variables and sub and properties.
tested on Access 2016 x64.

see change in BalloonTooltip Class.
replaced one function there that
keeps on hanging on x64 Access.

 

[isladogs]

Arnel

My last answer had already showed how to use different icons in the system tray alert and the taskbar with no additional code

Also your code assumed the folder Documents exists in the user profile area.
If that's not the case (as on my PC), the autoexec macro triggers this error

Easily fixed by saving icons to the same folder as the database i.e. replacing the path with CurrentProject.Path (see attached)

However, I'll try adding your 64-bit changes to my original example & see whether that now works in 64-bit. Will let you know

 

[arnelgp]

You dont gavr MyDocuments? So ill guess next time on my future sample extract it to tmp or desktop.

 

[isladogs]

You dont gavr MyDocuments? So ill guess next time on my future sample extract it to tmp or desktop.

Of course I do have My Documents, but I always move it to a separate drive.
This means I never lose files if for any reason drive c fails or needs reformatting (as happened to me recently).

You could of course have used code to check for the user's My Documents folder.... but using CurrentProject.Path is much simpler.

Just about to check your example in 64-bit Access

 

[Gasman]

Arnelgp,

All the

Private Declare PtrSafe

in Class BalloonToolTip will not compile and are in red, despite win64 or not.?

Do I need an extra reference?

TIA

found a way to change the TrayIcon
separate to the Message Icon.

on the sample two icons, red and green.
it is extracted by autoexec macro (so you must
allow it to run).

you should run both sample forms.

you are not limited to just one Tray Icon.
you can have as many and you must now pass
the Form's hwnd to ShowBaloonToolTip sub.

modified the code to also run in x64.
added some more variables and sub and properties.
tested on Access 2016 x64.

see change in BalloonTooltip Class.
replaced one function there that
keeps on hanging on x64 Access.

 

[isladogs]

Arnel
I've just checked my modified version of your example db in Access 2016 64-bit.
Very happy to say it compiles & runs correctly,

However, I then tried replacing the class module BalloonTooltip & standard module modBalloonTooltip from my example database with your 2 modules.

I got compile errors which made it unusable.
Next I tried comparing the old modules with your new ones and modifying step by step. No luck so far in getting this to work
I see you've disabled lots of code with your own temporary code (and have commented the code where you have done so)
Unfortunately that means its no longer transferable to other databases.

Both Phil Stiefel & I have been working on generic solutions for 64-bit for some time.
All credit to you that you've got a step closer to this but I don't believe its yet working as a generic solution.

So I'm offering my original example database to you with a request that you also make this work in 64-bit.
For now I'm just using an icon in the program folder as the application icon.
I'll adopt your approach to that part later

 

[isladogs]

I was just about to reply to a post by accessanitor but its just been deleted (or I'm losing the plot)
Anyway in answer to that non -existent post ....!

Code like this (similar to that used by arnel)

Environ("USERPROFILE") & "\My Documents\"

will correctly find the My Documents folder if its in the default location:
e.g. C:\Users\cridd\My Documents\

However, it fails for many users like myself who move 'special folders' like My Documents to a different location / drive
e.g. in my case D:\Colin\My Documents\

The following code does work in ALL cases for My Documents and other special folders.
It is by oharab and taken from http://www.tek-tips.com/viewthread.cfm?qid=1039720

Save to a standard module:

Option Compare Database
Option Explicit

' Get path of Special folders
Type ShortItemId
     cb As Long
     abID As Byte
End Type

Type ITEMIDLIST
     mkid As ShortItemId
End Type

Const CSIDL_PROGRAMS = 2                  ' Program Groups Folder
Const CSIDL_PERSONAL = 5                  ' Personal Documents Folder
 Const CSIDL_FAVORITES = 6                 ' Favorites Folder
 Const CSIDL_STARTUP = 7                   ' Startup Group Folder
 Const CSIDL_RECENT = 8                    ' Recently Used Documents Folder
 Const CSIDL_SENDTO = 9                    ' Send To Folder
 Const CSIDL_STARTMENU = 11                ' Start Menu Folder
 Const CSIDL_DESKTOPDIRECTORY = 16         ' Desktop Folder
 Const CSIDL_NETHOOD = 19                  ' Network Neighborhood Folder
 Const CSIDL_TEMPLATES = 21                ' Document Templates Folder
 Const CSIDL_COMMON_STARTMENU = 22         ' Common Start Menu Folder
 Const CSIDL_COMMON_PROGRAMS = 23          ' Common Program Groups Folder
 Const CSIDL_COMMON_STARTUP = 24           ' Common Startup Group Folder
 Const CSIDL_COMMON_DESKTOPDIRECTORY = 25  ' Common Desktop Folder
 Const CSIDL_APPDATA = 26                  ' Application Data Folder
 Const CSIDL_PRINTHOOD = 27                ' Printers Folder
 Const CSIDL_COMMON_FAVORITES = 31         ' Common Favorites Folder
 Const CSIDL_INTERNET_CACHE = 32           ' Temp. Internet Files Folder
 Const CSIDL_COOKIES = 33                  ' Cookies Folder
 Const CSIDL_HISTORY = 34                  ' History Folder

 Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    (ByVal pidl As Long, _
    ByVal pszPath As String) As Long

 Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" _
    (ByVal hwndOwner As Long, _
    ByVal nFolder As Long, _
    pidl As ITEMIDLIST) As Long

Sub ShowFolder()
    Dim lngID As Long
    Dim IDL As ITEMIDLIST

    Dim strPath As String
    Dim strShortCut As String

    '[B]Fill the idl structure with the specified folder item.
    'i.e replace CSIDL_PERSONAL with another item from the above list[/B]
    lngID = SHGetSpecialFolderLocation(0, CSIDL_PERSONAL, IDL)

    ' Get the path from the idl list, and return
    ' the folder with a slash at the end.
    If lngID = 0 Then
        strPath = Space$(260)
        lngID = SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal strPath)

        If lngID Then
            strPath = Left$(strPath, InStr(strPath, Chr$(0)) - 1) & "\"
        End If

        If strPath <> "" Then
            Debug.Print "My documents = " & strPath
        Else
            Debug.Print "Unable to find My Documents path"
        End If
    End If

End Sub

 

[Accessanitor]

Yep Sorry - I replied but then wanted to do more research and put up a more accurate post so deleted mine. But looks like you nailed it mate. You guys are like machines with your quick-draw responses lol