I Looking at ways that I can deliver reports once a month, currently these reports are being sent out via e-mail, however, this solution is not working very well.
It was been decided that it would work better if these reports, around 1000 in PDF and Xlsx. were uploaded to a database where individual users can have access to view them. However, I only know of MS Access and don't know what else could do the job.
We need to limit access to each users to ensure that they can only see certain reports, therefore some type of limited rights or permissions would need to be set up for each user, and there would also need to be some sort of auditing tool, to see who is viewing these reports and when they are being viewed.
If you have any suggestions it would be very much appreciated, as I have little knowledge of what is available to me for this.
Thanks in advance
1. For determining who's the current user, you can simply rely on the Windows logon. You can retrieve the name of the current user in two ways:
Dim strCurrentUser As String
SrtCurrentUser = Environ("USERNAME")
2. If you fear that some users would try to hack the Windows environment table, you can use:
' Windows API declarations
Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function GetNTUser() As String
' Retrieve the ID of the currently connected user
Dim strUserName As String
Dim lngUserNameSize As Long
strUserName = String$(255, 0)
lngUserNameSize = Len(strUserName)
GetUserName strUserName, lngUserNameSize
GetNTUser = Left$(strUserName, lngUserNameSize - 1)
From there you need a table with the names of the users that are authorized to open the application. When the application starts, a procedure checks if the current user exists in that table. The table can also contain columns that indicates which startup form (or switchboard) must be open.
You can also create a second table that lists all the forms and reports users are allowed to open in the application. A junction table then creates the associations user/Form or report. The Form_Open or Report_Open event handler of each concerned objects checks if the current user is allowed to open the form or report and set the Cancel parameter to true if the current user is not allowed to go on.
Both techniques can be combined.
The same Form_Open/Report_Open procedure can also write a log (either into a table or into a log file).