Prev: help transition to access from excel
Next: Adding a second employee to an event
From: Peter on 8 Mar 2010 08:50
Hello. I have an Accounting Access DB that I intend at some point to split
into front-end (tables, forms, reports) and back-end (data), then I hope to
deploy on the web. I will eventually have around 30 distributed users (only
about 5 concurrent) – each inputs the same type of financial data but does
business independent of the others and so must NEVER see the data entered by
the other users. To make future releases and bug fixes as simple as
possible, I intend to have a single instance of the front-end that each of
the Users will access.

However, as each User must NEVER see data entered by other Users, is there
any way of achieving 'privacy' for each User so they only ever see their own
set of data? My first reaction is that I must (in some way?) have multiple
instances of the back-end data base (remembering there is a common single
front end instance) and somehow 'map' each user to only ever open their own
back-end. However, I'm hoping there might be a more simple elegant way of
ensuring privacy perhaps by somehow (cleverly) making each set of User data
private to that User on a single instance back-end data base.

In a nutshell I'm asking how I'd configure Access to allow many users to
enter their own private data through a shared common single instance
front-end and keep their data private from other users.

I should finally add that I have just got to grips with Access and while
there may well be better more appropriate alternatives such as SQL or MySQL,
I'd much rather stick with Access – the user population won't grow any more
than anticipated.

--
Peter
From: Doctor on 8 Mar 2010 11:14
I've done this exact logic before. My way may not work for you nor may it be
the absolute best way to accomplish your goals. I have one backend database
that everyone shares. My method does require using VB code. Here is what I
did:
1. You must have a way for users to sign on to your database so that each
user has a unique ID. If they have a valid username and password when the
sign on, I store their user ID in a TempVar (available ony in Access 2007 or
above). The code is
**Code**
TempVars.Add "gUser", ID
**end code**
gUser is whatever you want to call the string and ID is the replaced by the
ID of the User.

2. Then I have added four fields to every table: CreatedBy, CreatedStamp,
UpdatedBy, and UpdatedStamp. Whenever a user creates or edits a record in any
table, I store that users ID with the record. For CreatedStamp and
UpdatedStamp fields, I have set their default values to =Now().

3. Next, in every Form I place the below code in the forms Before Update
event:
**Code**
If IsNull(Me!CreatedBy) Then Me!CreatedBy = TempVars!gUser
Me!UpdatedBy = TempVars!gUser
Me!UpdatedStamp = Now
**End Code**

4. Finally, to acheive your desired result of users only seeing their own
entries you need to change the Record Source of the form. Here is an example:

SELECT tblClients.* FROM tblClients WHERE tblClients.CreatedBy =
[TempVars]![gUser]

Doing this will only show records to the person who created the record and
allow you to store all of the records in the same table. I use this method
because it is simple and I've applied it in many different ways. Using this
method, you could also create a different Record Source for the form for your
users who have permissions to view all of the records regardless of who
created it. This method should also make reporting easier too.

"Peter" wrote:

> Hello. I have an Accounting Access DB that I intend at some point to split
> into front-end (tables, forms, reports) and back-end (data), then I hope to
> deploy on the web. I will eventually have around 30 distributed users (only
> about 5 concurrent) – each inputs the same type of financial data but does
> business independent of the others and so must NEVER see the data entered by
> the other users. To make future releases and bug fixes as simple as
> possible, I intend to have a single instance of the front-end that each of
> the Users will access.
>
> However, as each User must NEVER see data entered by other Users, is there
> any way of achieving 'privacy' for each User so they only ever see their own
> set of data? My first reaction is that I must (in some way?) have multiple
> instances of the back-end data base (remembering there is a common single
> front end instance) and somehow 'map' each user to only ever open their own
> back-end. However, I'm hoping there might be a more simple elegant way of
> ensuring privacy perhaps by somehow (cleverly) making each set of User data
> private to that User on a single instance back-end data base.
>
> In a nutshell I'm asking how I'd configure Access to allow many users to
> enter their own private data through a shared common single instance
> front-end and keep their data private from other users.
>
> I should finally add that I have just got to grips with Access and while
> there may well be better more appropriate alternatives such as SQL or MySQL,
> I'd much rather stick with Access – the user population won't grow any more
> than anticipated.
>
> --
> Peter
From: Peter on 9 Mar 2010 13:31
Wow! that is so interesting. I had come across some code on YouTube Access
Tutorial that took me through the steps of User validation and setting a
password for each User so, I'm relatively comfortable with that. I'll have
a go at implementing your solution – it is definitely in the right area for
me. How might I be able to keep you advised on my progress?
Very best wishes

--
Peter


"Doctor" wrote:

> I've done this exact logic before. My way may not work for you nor may it be
> the absolute best way to accomplish your goals. I have one backend database
> that everyone shares. My method does require using VB code. Here is what I
> did:
> 1. You must have a way for users to sign on to your database so that each
> user has a unique ID. If they have a valid username and password when the
> sign on, I store their user ID in a TempVar (available ony in Access 2007 or
> above). The code is
> **Code**
> TempVars.Add "gUser", ID
> **end code**
> gUser is whatever you want to call the string and ID is the replaced by the
> ID of the User.
>
> 2. Then I have added four fields to every table: CreatedBy, CreatedStamp,
> UpdatedBy, and UpdatedStamp. Whenever a user creates or edits a record in any
> table, I store that users ID with the record. For CreatedStamp and
> UpdatedStamp fields, I have set their default values to =Now().
>
> 3. Next, in every Form I place the below code in the forms Before Update
> event:
> **Code**
> If IsNull(Me!CreatedBy) Then Me!CreatedBy = TempVars!gUser
> Me!UpdatedBy = TempVars!gUser
> Me!UpdatedStamp = Now
> **End Code**
>
> 4. Finally, to acheive your desired result of users only seeing their own
> entries you need to change the Record Source of the form. Here is an example:
>
> SELECT tblClients.* FROM tblClients WHERE tblClients.CreatedBy =
> [TempVars]![gUser]
>
> Doing this will only show records to the person who created the record and
> allow you to store all of the records in the same table. I use this method
> because it is simple and I've applied it in many different ways. Using this
> method, you could also create a different Record Source for the form for your
> users who have permissions to view all of the records regardless of who
> created it. This method should also make reporting easier too.
>
> "Peter" wrote:
>
> > Hello. I have an Accounting Access DB that I intend at some point to split
> > into front-end (tables, forms, reports) and back-end (data), then I hope to
> > deploy on the web. I will eventually have around 30 distributed users (only
> > about 5 concurrent) – each inputs the same type of financial data but does
> > business independent of the others and so must NEVER see the data entered by
> > the other users. To make future releases and bug fixes as simple as
> > possible, I intend to have a single instance of the front-end that each of
> > the Users will access.
> >
> > However, as each User must NEVER see data entered by other Users, is there
> > any way of achieving 'privacy' for each User so they only ever see their own
> > set of data? My first reaction is that I must (in some way?) have multiple
> > instances of the back-end data base (remembering there is a common single
> > front end instance) and somehow 'map' each user to only ever open their own
> > back-end. However, I'm hoping there might be a more simple elegant way of
> > ensuring privacy perhaps by somehow (cleverly) making each set of User data
> > private to that User on a single instance back-end data base.
> >
> > In a nutshell I'm asking how I'd configure Access to allow many users to
> > enter their own private data through a shared common single instance
> > front-end and keep their data private from other users.
> >
> > I should finally add that I have just got to grips with Access and while
> > there may well be better more appropriate alternatives such as SQL or MySQL,
> > I'd much rather stick with Access – the user population won't grow any more
> > than anticipated.
> >
> > --
> > Peter
From: Doctor on 10 Mar 2010 12:32
Yes. I'll be glad to help. Just post back here with your progress/questions.


 | 
Pages: 1
Prev: help transition to access from excel
Next: Adding a second employee to an event