Performance of MS Access database

[Guest]

Hi,
I am very new in Microsoft Access and I am going create a database on
Access.
I will be highly pleased if any one can provide me some information
according to the questions bellow:

1. What is the system of user authorization in MS Access (I mean by login
and password or also host name)? Is there any option to secure connection?
2. What are access privileges (level)?
3. Is there option to grant group privileges?
4. What are the maximum numbers of row and column in one table?
5. Is it possible to use data from different instances in one session?
6. Which applications does Access support?
7. Does it support automatic crash recovery?
8. Does it support triggers?
9. How many users can simultaneously work on one database?
Best regards,

Rana

 

[Joseph Meehan]

Hi,
I am very new in Microsoft Access and I am going create a database on
Access.
I will be highly pleased if any one can provide me some information
according to the questions bellow:

1. What is the system of user authorization in MS Access (I mean by
login and password or also host name)? Is there any option to secure
connection?
2. What are access privileges (level)?
3. Is there option to grant group privileges?

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

4. What are the maximum numbers of row and column in one table?

Someone will correct me if I am wrong. Fields (columns) is 254
There is no limit to records (rows) but the total size of the database
is limited to 2 gig (1 gig on older versions)

5. Is it possible to use data from different instances in one session?

Yes, if I understand your question correctly.

6. Which applications does Access support?

Lots. What do you want to do with these "applications" ?

7. Does it support automatic crash recovery?

I would call it semi.

8. Does it support triggers?

Yes, if I understand your question correctly.

9. How many users can simultaneously work on one database?
Best regards,

Using a split system the usual number considered good is about 20-30
with a good LAN. I believe the limit is 255. I have used many more than 30
with no problems as most were not active all the time.

 

[Douglas J. Steele]

Someone will correct me if I am wrong. Fields (columns) is 254
There is no limit to records (rows) but the total size of the database
is limited to 2 gig (1 gig on older versions)

You're wrong. <g>

The limit is 255 fields. Of course, that's about 10 times what it needs to
be...

 

[Larry Linson]

1. What is the system of user authori-

zation in MS Access (I mean by login
and password or also host name)? Is
there any option to secure
connection?
2. What are access privileges (level)?
3. Is there option to grant group privileges?

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security, not infallible, is also not trivially simple. Read that
Security FAQ _carefully_, study it, and study it some more.

Yes, if I understand your question
correctly.

I don't understand what the original poster means by 'data from different
instances' and would appreciate clarification.

I also don't understand what the original poster means by this question.
Strictly speaking, in my view, Access does not "suport applications". It is
a good end-user database and a good database development tool that can be
used to create database applications of many kinds, if that was the
question.

I would call it semi.

You are kinder in your evaluation than I would be: Access, and the default
Jet database engine, do not include automatic crash recovery. It is up to
the user to create backups and logs and to restore and update at appropriate
times, manually.

Yes, if I understand your question correctly.

Access itself is not a "server database", but provides access to databases.
The default database is the Jet database engine which does not support
triggers. Access may be used with server databases such as Microsoft SQL
Server which do support triggers.

From none to over 100, practically speaking. There is an absolute limit of
255 with the Jet database engine, but that limit does not apply if you are
using Access as a client to server databases.

Many factors affect how many users can be supported with acceptable
performance. The number of users itself is not a primary factor in execution
without corruption, but the way the database is set up and implemented are
very important in this regard.

The best collection of performance and corruption-avoidance information and
links that I know about are at MVP Tony Toew's site,
http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

 

[Joseph Meehan]

1. What is the system of user authori-
zation in MS Access (I mean by login
and password or also host name)? Is
there any option to secure
connection?
2. What are access privileges (level)?
3. Is there option to grant group privileges?

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security, not infallible, is also not trivially simple. Read
that Security FAQ _carefully_, study it, and study it some more.

I agree. I was only posting the reference to answer the questions.
SRR, if you consider using user level security, I recommend taking Larry's
advice very seriously and always make a copy before applying it. I like it
and find it very useful.

 

[Paul Overway]

I'd quibble on support for triggers....Jet doesn't support them...so, if
using Jet for backend...no. But if using another database engine, i.e., SQL
Server, Oracle, then yes.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Hi,
I am very new in Microsoft Access and I am going create a database on
Access.
I will be highly pleased if any one can provide me some information
according to the questions bellow:

1. What is the system of user authorization in MS Access (I mean by
login and password or also host name)? Is there any option to secure
connection?
2. What are access privileges (level)?
3. Is there option to grant group privileges?

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

4. What are the maximum numbers of row and column in one table?

Someone will correct me if I am wrong. Fields (columns) is 254
There is no limit to records (rows) but the total size of the database
is limited to 2 gig (1 gig on older versions)

5. Is it possible to use data from different instances in one session?

Yes, if I understand your question correctly.

6. Which applications does Access support?

Lots. What do you want to do with these "applications" ?

7. Does it support automatic crash recovery?

I would call it semi.

8. Does it support triggers?

Yes, if I understand your question correctly.

9. How many users can simultaneously work on one database?
Best regards,

Using a split system the usual number considered good is about 20-30
with a good LAN. I believe the limit is 255. I have used many more than
30 with no problems as most were not active all the time.

Rana

 

[Tony Toews]

You're wrong. <g>

The limit is 255 fields.

Of course, that's about 10 times what it needs to be...

Well, in one table I have about 150 fields. And it can't be
normalized. These are all the various settings for a particular job.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Joan Wild]

Well, in one table I have about 150 fields. And it can't be
normalized. These are all the various settings for a particular job.

Not that I doubt your capabilities, but the tenses you use in that last
sentence suggests that the settings could be a separate table.

(settings - many) to (job - one)

 

[Larry Daugherty]

Hi Tony,

Curious minds are intrigued!

My reaction is as Joan's, including respect for your capabilities.
But, you say that the ~150 field table "can't" be normalized. Why?
Could you discuss that situation for us so that if we face a similar
situation we're aware?

FWIW when I have a lot of unrelated information to handle I use
exactly the method Joan laid out. tblUserOption; OptionID,
OptionName, OptionArgument, OptionNote.

 

[Tony Toews]

Not that I doubt your capabilities, but the tenses you use in that last
sentence suggests that the settings could be a separate table.

(settings - many) to (job - one)

Yes, you're correct. I could've used a table with the settings going
down. But there are problems with this approach.

One minor problem is that I'd have to be doing many reads from this
table as I'm doing things. These would be relatively minor in the
grand scheme of database reads though. Currently these are all held
on a hidden form so I can reference them in code using
forms!GlobalOptionsHidden!FieldName

Another problem is that there are many different types of fields.
Sometimes a check box. Sometimes an integer, single, currency or
other. Many times a drop down combo box with limited values.
1-this,2-that, 3-for a third option. Other times a combo box to other
tables. Yet other times are text, some of which contain paths to
import or export folders.

All in all it would be a reasonable amount of work to set up a
programmatic way of conveniently updating these values.

Truthfully though this is the way I started to work on that table and
it kept on growing and growing and growing. <smile> It's a lot
easier to add a field to this table and throw it on two forms. One
form being the multi tabbed edit form and the other the previously
mentioned hidden GlobalOptionsHidden form.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

I seriously miscounted. There are only 75 fields in that table.

The program options table has 25 fields.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Joan Wild]

Yes, you're correct. I could've used a table with the settings going
down. But there are problems with this approach.

Another problem is that there are many different types of fields.
Sometimes a check box. Sometimes an integer, single, currency or
other. Many times a drop down combo box with limited values.
1-this,2-that, 3-for a third option. Other times a combo box to other
tables. Yet other times are text, some of which contain paths to
import or export folders.

Interesting. Yes I faced a similar dilemma. These were periodic
assessments on trees. It felt odd to break it out so finely, but I did end
up with a separate table. The field has to be text (even though some are
codes (1,2,3), some are measurements in cm) and then any compilation on the
measurements have to be converted to numbers.

Truthfully though this is the way I started to work on that table and
it kept on growing and growing and growing. <smile> It's a lot
easier to add a field to this table and throw it on two forms.

I'm sure it was you who used to say 'if in doubt, add another table' <g> but
I understand the compromise you made here.

 

[Tony Toews]

Interesting. Yes I faced a similar dilemma. These were periodic
assessments on trees. It felt odd to break it out so finely, but I did end
up with a separate table. The field has to be text (even though some are
codes (1,2,3), some are measurements in cm) and then any compilation on the
measurements have to be converted to numbers.

But how do you handle the combo box issue against other tables?

I'm sure it was you who used to say 'if in doubt, add another table' <g> but
I understand the compromise you made here.

Yup, I sure did and still do make that one of my rules.

To me though it wasn't a compromise. These fields are all the
settings for a job. It works and works well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Joan Wild]

But how do you handle the combo box issue against other tables?

This particular application doesn't have any. There is no data entry
directly to the database. Assessments are done using data loggers
http://www.daptech.com/ , and the data is imported into Access. The program
on the datalogger handles the combos/picklists. The data comes in with a
single record for each tree and it's measurements for that assessment.

I suppose that if they were entering data via the database, I'd limit the
combo based on what type of measurement they were entering (chosen in a
previous field).

To me though it wasn't a compromise. These fields are all the
settings for a job. It works and works well.

That's great, but I still think it's a compromise. Anytime you find
yourself adding fields throws up a red flag for me (I don't mean unforseen
requirements that are added later).

I could have gone the same route as you; afterall they are all measurements
on a single tree.

 

[Tony Toews]

That's great, but I still think it's a compromise. Anytime you find
yourself adding fields throws up a red flag for me (I don't mean unforseen
requirements that are added later).

Ahh, but that system was built over about four years. So as I was
adding logic to the system I was adding the fields to the Job table.

For example when it came to invoicing I had to know what price book
would be used for this job. That was a long integer field with a
combo box based on the price book table.

Another example was the end cap item to be used for invoicing.
(These are the pieces of plastic to keep debris out of the pipe.) The
invoicing system would automatically add one end cap to each spool.
Then the person doing the invoicing would change the one to match what
was on the drawing. If it had four ends then it would be four end
caps.

I could have gone the same route as you; afterall they are all measurements
on a single tree.

No, in your situation I completely agree that each measurement should
be a separate record.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm