By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

How advanced is Access?

P: 11
Dear all,

I have the following questions, and I need you opinion on them, so that I understand if Access is good enough for my needs:

1. Can I create using Access and Visual Basic different levels of access to my database? For example, when it opens, the Start Form should have an input field for username and password, and according to the info given, then allow access to different levels (egg. one user can only view the tables, anothe once can modify them, etc). I hope you will not find this as a stupid question.

2. Are there limits to Access, considering one can program whatever one needs in Visual Basic? probably there are other easier to use databases, but do they have extra features that Access does not?

Thank you so much!

Cheers!
Anca
Feb 16 '07 #1
Share this Question
Share on Google+
15 Replies


ADezii
Expert 5K+
P: 8,599
Dear all,

I have the following questions, and I need you opinion on them, so that I understand if Access is good enough for my needs:

1. Can I create using Access and Visual Basic different levels of access to my database? For example, when it opens, the Start Form should have an input field for username and password, and according to the info given, then allow access to different levels (egg. one user can only view the tables, anothe once can modify them, etc). I hope you will not find this as a stupid question.

2. Are there limits to Access, considering one can program whatever one needs in Visual Basic? probably there are other easier to use databases, but do they have extra features that Access does not?

Thank you so much!

Cheers!
Anca
__1 Yes. You would not establish Access Levels via a Start Form with UserName and Password fields. but you would utilize the built in Security System within Access.
__2 Microsoft Access Specifications:
File size - 2 gigabytes minus the space needed for system objects

Number of objects in a database - 32,768

Modules (including forms and reports with the HasModule property set to True) - 1,000
Number of characters in an object name - 64
Number of characters in a password - 14
Number of characters in a user name or group name - 20
Number of concurrent users - 255

Table
Number of characters in a table name
- 64
Number of characters in a field name - 64
Number of fields in a table - 255
Number of open tables - 2048; the actual number may be less because of tables opened internally by Microsoft Access
Table size - 2 gigabyte minus the space needed for the system objects
Number of characters in a Text field - 255
Number of characters in a Memo field - 65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically
Size of an OLE Object field - 1 gigabyte
Number of indexes in a table - 32
Number of fields in an index - 10
Number of characters in a validation message - 255
Number of characters in a validation rule - 2,048
Number of characters in a table or field description - 255
Number of characters in a record (excluding Memo and OLE Object fields) when the UnicodeCompression property of the fields is set to Yes - 4,000
Number of characters in a field property setting - 255

Query
Number of enforced relationships - 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query - 32
Number of fields in a recordset - 255
Recordset size -1 gigabyte
Sort limit - 255 characters in one or more fields
Number of levels of nested queries - 50
Number of characters in a cell in the query design grid - 1,024
Number of characters for a parameter in a parameter query - 255
Number of ANDs in a WHERE or HAVING clause - 99
Number of characters in an SQL statement approximately - 64,000

Form and report
Number of characters in a label - 2,048
Number of characters in a text box - 65,535
Form or report width - 22 in. (55.87 cm)
Section height - 22 in. (55.87 cm)
Height of all sections plus section headers - 200 in. (508 cm)
Number of levels of nested forms or reports - 7
Number of fields or expressions you can sort or group on in a report - 10
Number of headers and footers in a report - 1 report header/footer; 1 page header/footer; 10 group headers/footers
Number of printed pages in a report - 65,536
Number of controls and sections you can add over the lifetime of the form or report - 754
Number of characters in an SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control (both .mdb and .adp) - 32,750

Macro
Number of actions in a macro
- 999
Number of characters in a condition - 255
Number of characters in a comment - 255
Number of characters in an action argument - 255
Feb 16 '07 #2

P: 11
Thank you so much!

I really appreciate your thorough response! Have a nice weekend!

Anca
Feb 16 '07 #3

ADezii
Expert 5K+
P: 8,599
Thank you so much!

I really appreciate your thorough response! Have a nice weekend!

Anca
Glad to be of assistance.
Feb 16 '07 #4

P: 11
One more question:

I found and created access levels in Access Security - thanks again! But can I see anywhere if something was modified by one of the users? Also, if there can be maximum 255 users working on the data at the same time, can they see what the others are updating? Thanks!
Feb 16 '07 #5

ADezii
Expert 5K+
P: 8,599
One more question:

I found and created access levels in Access Security - thanks again! But can I see anywhere if something was modified by one of the users? Also, if there can be maximum 255 users working on the data at the same time, can they see what the others are updating? Thanks!
For Fields that you deem critical and wish to monitor for modifications, you can write code in the Dirty() Event of the Field that will write to a Log File. This process can be transparent to the average User.
Feb 16 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
If you have many users and want to apply permissions to a number of objects then I'd certainly consider setting up and using the Groups feature rather than doing it all via the individual users.
Feb 17 '07 #7

P: 11
Thank you for the Groups idea! Still, what I do not know is this: if there are 2 or more users suing at the same time the database from different computers, can they see what the others are changing in the database? Can I display a message saying "sorry, the object you are trying to edit is currently in use by a different user"? Thank you so much!
Feb 19 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
They will see changes if they Refresh their forms or table data.
They will see Additions & Deletions if they Requery them.
This will not happen automatically.
The error you refer to will be triggered automatically. You can handle the error and display your own message, but I think the default runs along similar lines anyway.
It is worth designing the database with multi-user access in mind.
Feb 19 '07 #9

P: 11
Thanks a million! Have a nice week!
Feb 21 '07 #10

NeoPa
Expert Mod 15k+
P: 31,186
Not a problem.
Good luck with your project.
Feb 21 '07 #11

missinglinq
Expert 2.5K+
P: 3,532
Be aware that although MS lists 255 concurrent users as the limit the practical limit is much lower than this, between 20-50 users going by the posts I've seen over the past few years.
Feb 21 '07 #12

P: 6
__1 Yes. You would not establish Access Levels via a Start Form with UserName and Password fields. but you would utilize the built in Security System within Access.
__2 Microsoft Access Specifications:
File size - 2 gigabytes minus the space needed for system objects

Number of objects in a database - 32,768

Modules (including forms and reports with the HasModule property set to True) - 1,000
Number of characters in an object name - 64
Number of characters in a password - 14
Number of characters in a user name or group name - 20
Number of concurrent users - 255

Table
Number of characters in a table name
- 64
Number of characters in a field name - 64
Number of fields in a table - 255
Number of open tables - 2048; the actual number may be less because of tables opened internally by Microsoft Access
Table size - 2 gigabyte minus the space needed for the system objects
Number of characters in a Text field - 255
Number of characters in a Memo field - 65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically
Size of an OLE Object field - 1 gigabyte
Number of indexes in a table - 32
Number of fields in an index - 10
Number of characters in a validation message - 255
Number of characters in a validation rule - 2,048
Number of characters in a table or field description - 255
Number of characters in a record (excluding Memo and OLE Object fields) when the UnicodeCompression property of the fields is set to Yes - 4,000
Number of characters in a field property setting - 255

Query
Number of enforced relationships - 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query - 32
Number of fields in a recordset - 255
Recordset size -1 gigabyte
Sort limit - 255 characters in one or more fields
Number of levels of nested queries - 50
Number of characters in a cell in the query design grid - 1,024
Number of characters for a parameter in a parameter query - 255
Number of ANDs in a WHERE or HAVING clause - 99
Number of characters in an SQL statement approximately - 64,000

Form and report
Number of characters in a label - 2,048
Number of characters in a text box - 65,535
Form or report width - 22 in. (55.87 cm)
Section height - 22 in. (55.87 cm)
Height of all sections plus section headers - 200 in. (508 cm)
Number of levels of nested forms or reports - 7
Number of fields or expressions you can sort or group on in a report - 10
Number of headers and footers in a report - 1 report header/footer; 1 page header/footer; 10 group headers/footers
Number of printed pages in a report - 65,536
Number of controls and sections you can add over the lifetime of the form or report - 754
Number of characters in an SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control (both .mdb and .adp) - 32,750

Macro
Number of actions in a macro
- 999
Number of characters in a condition - 255
Number of characters in a comment - 255
Number of characters in an action argument - 255
As I can see you are aware of the limitaions i Access. Is my problem in that limitations? I make a simple query to the db in order to get a specific record using the "Record-ID" I have about 25 000 records. I can query for all up to number 1852 but then with 1853 I get no result.. I do it via Data Access Page and my where look like this: WHERE (((Id) Like [Ange ID-nr] & "%"));
When I run the query direct in Access I must use this where:
WHERE (((Id) Like [Ange ID-nr] & "*")); and I recieve all records starting with 1852 or 1853 Can You help me?
Thanks Sören
May 28 '07 #13

ADezii
Expert 5K+
P: 8,599
As I can see you are aware of the limitaions i Access. Is my problem in that limitations? I make a simple query to the db in order to get a specific record using the "Record-ID" I have about 25 000 records. I can query for all up to number 1852 but then with 1853 I get no result.. I do it via Data Access Page and my where look like this: WHERE (((Id) Like [Ange ID-nr] & "%"));
When I run the query direct in Access I must use this where:
WHERE (((Id) Like [Ange ID-nr] & "*")); and I recieve all records starting with 1852 or 1853 Can You help me?
Thanks Sören
Try using the same syntax, sice the data is being pulled from an Acces Database.
May 28 '07 #14

P: 6
Try using the same syntax, sice the data is being pulled from an Acces Database.
I tried to use WHERE (((Id) Like [Ange ID-nr] & "*")); in DAP but it won´t work. Do you have any other proposal?
Thanks Sören in Sweden
May 28 '07 #15

ADezii
Expert 5K+
P: 8,599
I tried to use WHERE (((Id) Like [Ange ID-nr] & "*")); in DAP but it won´t work. Do you have any other proposal?
Thanks Sören in Sweden
I rarely use DAP, but you are 100% correct. For some strange reason, it doesn't like the Wildcard Characters (* and %). Oddly enough, it will accept AND, OR, IN, etc. if they are contained in the RecordSource for the Page. I'll look into it further when I gate a chance.
May 28 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.