New Claims Management Project

[D.C]

Hello all,

I am an intermediate Access user, about to start a project for my business. The idea is to build a secure Claims Management System to track and manage financial customer claims in the banking industry.

The system will be used by up to 100 people in multiple locations, so will need separate user-level read and edit restrictions based on seniority (Project manager, team leader, claim handler etc.). Claim data will be imported from an excel spreadsheet, exported by the client. Customer engagement i.e. outgoing/incoming contact will also need to be logged.

I'm after a few suggestions on best ways to design, build and manage the project:

- Is Access suitable for the task, and what other facilities would be useful?
- Is it possible to create a user list with secure log-ins?
- Can I distribute copies of the final system and manage from an external location?
- Where to start...

I have a general idea of what stages I want to track for each claim;

Initial Import --> Initial Customer Contact --> Customer Response --> Review Stage (Eligible/Not Eligible for compensation) --> (If Eligible) Compensation Calculation --> Offer Submission --> Payment

I know what I've mentioned is very vague, but any help or guidance would be greatly appreciated.

Thanks all,

Dale

[ranman256]

I use a tUser table:
userID
userName
Dept
Level

This does not store passwords. Users already have this using the Windows Login, to authenticate.
The tUsers table just validates that they can USE this database and their dept, and/or level (manager, etc)
you can further distribute the user's frontEnd using their network folder. So now you have permissions on this folder.

you make a form with text boxes for UserID, Password, Domain.
It checks this and lets them in the db.

1.check if user is in the tUser table
2.check with windows authentication. If they pass both, show the menu, else quit.

you would also build Level access in the forms (M=manager, A=admin):
btnFinance.enabled = goUser.Level = "M" or goUser.Level = "A"
btnAdmin.enabled = goUser.Level = "A"


CODE FOR USER AUTHENTICATION:

   'form code
'-------------
Private Sub btnLogin_Click()
'-------------
Dim sUser As String, sPass As String, sDom As String
dim vID, vDbID


sUser = txtUser
sPass = txtPass
sDom = txtDom


vID= Environ("Username")
vDbID = Dlookup("[userId]","tUsers","[UserID]='" & vID & "'"


If WindowsLogin(sUser, sPass, sDom) and vID = vDbID Then
   mbSafe = True
   DoCmd.OpenForm "frmMainMenu"
   DoCmd.OpenForm "frmLogin"
   DoCmd.Close
Else
   MsgBox "LOGIN INCORRECT", vbCritical, "Bad userid or password"
End If
End Sub




'-------------
Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
'-------------
        'Authenticates user and password entered with Active Directory.


        On Error GoTo IncorrectPassword
        
        Dim oADsObject, oADsNamespace As Object
        Dim strADsPath As String
        
        strADsPath = "WinNT://" & strDomain
        Set oADsObject = GetObject(strADsPath)
        Set oADsNamespace = GetObject("WinNT:")
        Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "\" & strUserName, strpassword, 0)
        
        WindowsLogin = True    'ACCESS GRANTED
        
ExitSub:
        Exit Function
        
IncorrectPassword:
        WindowsLogin = False   'ACCESS DENIED
        Resume ExitSub
End Function

Public Function GetUserName()
Dim vName, vUserName, vUserDomain
Dim i As Integer


    Set WSHnet = CreateObject("WScript.Network")
    vUserName = WSHnet.UserName
    vUserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & vUserDomain & "/" & vUserName & ",user")
    
    i = InStr(objUser.FullName, "(")
    If i = 0 Then
       vName = objUser.FullName
    Else
       vName = Left(objUser.FullName, i - 1)
    End If
    
    GetUserName= vName
End Function

[isladogs]

Hi & welcome to the forum

As this is your first post, I obviously know nothing about you other than your own words....
Sorry if this comes across as negative.

First of all you describe yourself as

an intermediate Access user

.
Most of us describe ourselves as intermediate users after MANY years.
That's because the more you know, the more you realise you don't know.

However, if you really mean your knowledge of Access is limited then I'd suggest caution.

Your project:

is to build a secure Claims Management System to track and manage financial customer claims in the banking industry.

Unless this is absolutely secure & totally reliable, it will be worse than useless.
If it goes wrong it could result in costs in the multi-millions ..

If you aren't absolutely sure of what you are doing, as your post implies, I STRONGLY recommend you abandon your project now.
Instead purchase a ready made package designed for the job & built by skilled developers.

[D.C]

Hi & welcome to the forum

As this is your first post, I obviously know nothing about you other than your own words....
Sorry if this comes across as negative.

First of all you describe yourself as .
Most of us describe ourselves as intermediate users after MANY years.
That's because the more you know, the more you realise you don't know.

However, if you really mean your knowledge of Access is limited then I'd suggest caution.

Your project:
Unless this is absolutely secure & totally reliable, it will be worse than useless.
If it goes wrong it could result in costs in the multi-millions ..

If you aren't absolutely sure of what you are doing, as your post implies, I STRONGLY recommend you abandon your project now.
Instead purchase a ready made package designed for the job & built by skilled developers.

Hi Ridders,

I thank you for the advice, however I took on the project knowing I could make it happen.

What I'm trying to say is, I can easily build the bulk of the database & have experience developing similar systems for a third party, I just want to know options on how I can step it up with multi-user restrictions throughout, and whether there's a lot more to creating that than i initially thought.

Turns out with a bit of help as given by the first reply on this post, it's doable.

[isladogs]

OK now I've got my initial concerns out of the way ...

- Is Access suitable for the task, and what other facilities would be useful?
Yes it could be suitable if it can be scaled up sufficiently ....but use a SQL server BE.
However no Access database can ever be totally secure.
Are you ABSOLUTELY SURE your clients are happy with the level of security achievable with Access?
- Is it possible to create a user list with secure log-ins?
Yes - lots of examples online and Ranman has given you one such example
- Can I distribute copies of the final system and manage from an external location?
Yes - many of us do that (including me) - I never visit the clients' workplaces once the project is in place ... but I do provide online support where needed
However, would bank security regulations allow that to happen?
- Where to start...
Start with Ranman's ideas for a login form though I think you will need far more than 2 user levels
Suggest you post again if needed when you have a starting design for your table structures & relationships

[D.C]

Thanks Ridders, that's very helpful & I will do so once I've got somewhere with it. Would you use SQL Server on a SharePoint site, or doesn't it matter?

I need to clear up that this project will not be for use directly at client sites, but external sites with sub-contractors under our business.

Essentially, as long as we can put a strong proposal in front of new clients to display we have a secure managed system to manage workflows, it'll be enough and we will manage from there.

[Gicu]

Hi there,

Your project is definitively doable in Access, I would use a split design with a SQL Server back-end and Access front-end. For the multiuser restrictions you can check out my site (forestbyte.com) and download the FBA Custom Access Levels utility. It allows you to split your users in groups (levels) and control what each one can see and do on any of your forms.

Cheers,
Vlad

[isladogs]

Would you use SQL Server on a SharePoint site, or doesn't it matter

I know its possible but I've no experience of SharePoint so can't advise