Newbie ? using multiple users in one database at same time

[countrymac]

I am very much a newbie when it comes to databases but I have been given
Microsoft Access 2003 and/or Microsoft SQL 2000 server to use to accomplish
my task. With Access I could probably limp my way along but SQL Server I
wouldn't even know where to begin.

GOAL:
20 users travel from store to store taking inventory of cash in safe. Each
user has there own laptop all connecting to one server/laptop that gets
backed up constantly.

1 user is the admin that will run reports throughout the day on what the 20
users have entered.

I am trying to find a way that multiple users can log into a database and
add entries (not delete them) all at a central point. Access appears to have
issues with multiple users connecting at the same time trying to change data.

Database should be pretty simple:
When ever a bag of cash is brought into the secure room and given to one of
the users they must
1. Log into the database
2. Select Site/Store
3. Select Section the bag of cash is from
4. (somehow automatically grab date and time record is beginning)
5. Begin entering
How many 1's, 5's, 10's, 20's, 50's, 100's, coupons?
6. Click submit (which should close that record and is now ready for the
next bag of cash)

At periodic times of the day one of the two admins would need to log into
the database (giving them report access) and run reports to see what the
status is of cash flow (per site and section and maybe even time range) and
make it printable.

Any idea's if this is possible and is it fairly easy in either Access or
SQL? Can someone point me in the direction of how to begin this task? Any
help is most appreciated.

 

[Klatuu]

Your assertion that Access doesn't workin a multi user environment is not
correct. It is a matter of setting it up correctly and coding to deal with
it. I sugguest you visit the Multiuser newsgroup in Access and do a little
research.

 

[countrymac]

I will do that. I have read that the MDE file (people recommend using the
MDE file for the front end due to you can lock it down so users can't alter
your data) will get corrupt. That is what I was basing my information from.

I will investigate further and hopefully find a solution sooner then later.

 

[Klatuu]

That is not true. The MDE is the preferred format.
If you are experiencing corruption, there is another cause.

 

[George Nicholson]

I have read that the MDE file (people recommend using the

MDE file for the front end due to you can lock it down so users can't
alter
your data) will get corrupt. That is what I was basing my information
from.

I think you misunderstood what you read or you read something really, really
bad.

MDE front ends (generally) don't contain data. Data is in the back end.
MDE's can't be modified, so they are extremely unlikely to become corrupted
(afaik). Corruption problems, if they occur, are most likely to occur in the
back end data file.

The MDE format does allow you to lock down the forms, queries, reports,
macros and code in the front end file, preventing users from making changes
to those key elements.

Also, from what you state, you won't have multiple users changing data. You
will have multiple users adding data. Access can handle either but additions
are less likely to cause problems (if properly handled).

BTW, Even if you decide to store your data in SQL, you'd need to create a
front end in order for users to access that data, and SQL does *not* provide
tools for front end development. SQL front ends are commonly created with
..Net or Access. Access is a lot more user friendly, especially for database
newbies.

HTH,

 

[John W. Vinson]

I am trying to find a way that multiple users can log into a database and
add entries (not delete them) all at a central point. Access appears to have
issues with multiple users connecting at the same time trying to change data.

Access can handle multiple users (nominally 255, in practice 50-100) with
aplomb.

You should use a "split" database architecture though - a backend (in either a
mdb file or in SQL/Server) containing the tables, and a .mde file containing
links to the backend along with forms, reports, code, etc. Each user would get
their own copy of the .mde.

See http://www.granite.ab.ca/access/splitapp for a discussion of this
technique.

John W. Vinson [MVP]

 

[countrymac]

I appreciate everyone's response but I could use some help (being a newbie)
in reference on how to get started. I did review the link that John has
supplied in his response and it was informative but is still advanced.

There has to be a starting point for someone who isn't very familiar with
access databases on creating a split database that is controlled via login
permissions for multiple users.

Could you capture in the database you logged in and populate a field within
the database? Say I want to capture who is entering data in a record
(basically who is responsible for entering the numbers for the last drop).
Is this possible?

 

[M Skabialka]

Since you are worried about multiple people opening the same table at the
same time, create a form that isn't tied to a table, but the controls all
have the same names. Have a user create all of the information on this
form, then when it is all correct (you could have a "Check my data" button
to make sure all values fit established criteria - e.g. all the bills add up
to the total the person entered), then the use can Click submit which
gathers the info into a SQL statement and performs an insert into the table.
That way they only access the table for a fraction of a second, and no-one
should be tying up others users' access to that table.

Mich

 

[John W. Vinson]

I appreciate everyone's response but I could use some help (being a newbie)
in reference on how to get started. I did review the link that John has
supplied in his response and it was informative but is still advanced.

There has to be a starting point for someone who isn't very familiar with
access databases on creating a split database that is controlled via login
permissions for multiple users.

These are two completely different issues! You can have a split database with
no security at all, or you can have a unitary database with Access security -
or you can have both features.

To split a unitary database open the .mdb file in Access; from the menu choose
Tools... Database Utilities... Database Splitter Wizard. This will take you
through a dialog and do the following:

- create a new yourdatabase_BE.mdb
- remove all the tables from yourdatabase.mdb
- create links to yourdatabase_BE.mdb
- compact the database

You can then (if you wish) use Tools... Database utilites... Create MDE on the
frontend (yourdatabase.mdb). *BE SURE* to keep a copy of the .mdb in a safe
place - you cannot go back from the .mde to a .mdb.

To implement database security, download the Microsoft Security Whitepaper:
Microsoft Access 2000 Security FAQ:

http://support.microsoft.com/kb/207793/en-us

Print it out. Read it cover to cover, CAREFULLY. Get a good night's sleep.
Read it cover to cover, carefully - *again*. Follow the instructions
scrupulously (I've done it many times and I still print a fresh copy and use
it as a checklist). It's *very* easy to do security wrong!

Could you capture in the database you logged in and populate a field within
the database? Say I want to capture who is entering data in a record
(basically who is responsible for entering the numbers for the last drop).
Is this possible?

Yes; you can put a Text field into your table, UpdatingUser say, bound to a
textbox txtUpdatingUser on the form, and use VBA code in the Form's
BeforeUpdate event. You may also want to put a WhenUpdated field, Date/Time
datatype; put a textbox txtWhenUpdated on the form

Private Sub Form_BeforeUpdate(Cancel as Integer)
<put any validation code here>
<if the record will indeed be saved...>
Me!txtUpdatingUser = CurrentUser()
Me!txtWhenUpdated = Now
End Sub


John W. Vinson [MVP]

 

[David W. Fenton]

You should use a "split" database architecture though - a backend
(in either a mdb file or in SQL/Server) containing the tables, and
a .mde file containing links to the backend along with forms,
reports, code, etc. Each user would get their own copy of the
.mde.

And what that means is that only the back end data tables are
shared, which is very reliable as long as the network is reliable
and the workstations have all the proper patcheds for Access and
Jet.

The front ends MDEs or MDBs should *never* be shared -- that's where
you'll run into problems.

The back end will *always* be shared, and you will likely run into
problems there only if something outside of Access is messed up.