I know I'll probably need to split the database and maybe use SQL server for the backend but new to all that sort of thing.

Access Security Seminar and Simple Security
are good places to start.

Splitting your database is an absolute must.

Thanks Sami. Should I bother converting all embedded macros to VBA code?

Yes, you should.
For several reasons:
- You will gain far more control
- Actually, it is easier to use and to understand
- The whole purpose of using macros is now defunct, which was Access running on the cloud that was cancelled way back.

Thanks Sami I'll start with that -- my database may only be a demonstration of what we need the IT guys to make for us but from what I've read there's no reason it can't be made as secure as anything else in here but it's not my call but least they can see what's required to make better use of our time.