473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How advanced is Access?

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
15 2898
ADezii
8,834 Expert 8TB
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
Thank you so much!

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

Anca
Feb 16 '07 #3
ADezii
8,834 Expert 8TB
Thank you so much!

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

Anca
Glad to be of assistance.
Feb 16 '07 #4
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
Thanks a million! Have a nice week!
Feb 21 '07 #10
NeoPa
32,556 Expert Mod 16PB
Not a problem.
Good luck with your project.
Feb 21 '07 #11
missinglinq
3,532 Expert 2GB
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
Kallbo
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
8,834 Expert 8TB
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
Kallbo
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
8,834 Expert 8TB
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

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

Similar topics

4
by: Nhmiller | last post by:
This is directly from Access' Help: "About designing a query When you open a query in Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window (Advanced...
3
by: TechBoy | last post by:
I had a useracct called xxx123 I had set up. Now there is a problem in that **every single** .mdb file I open now on my PC (regardless of age of the file, location of the file, etc) now asks for...
0
by: Frank S. Romano | last post by:
Hello, Does anyone know how to bind the image property of a picture control to an Access 2000 database OLE object column that contains a MS Photo Editor 3.0 photo? When I try binding the picture...
18
by: dlesandrini | last post by:
Has anyone noticed that the Google Groups Advanced Search no longer works correctly? Is it something I'm doing or have others noticed the change? Used to be, if I put *access* in the Groups...
3
by: | last post by:
I'm planning to transport a desktop application to the web. A spin-off of this application has already been put on the web by another programmer. He used ColdFusion with MS SQL, Access, VC, and...
0
by: Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+ | last post by:
I am running Access 2007. I have a report that I want to filter. I can go into Advanced...Advanced Filter/Sort... and setup a filter that works fine on the report when I apply it. When in this...
0
by: Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+ | last post by:
I am running Access 2007. I have a report that I want to filter. I can go into Advanced...Advanced Filter/Sort... and setup a filter that works fine on the report when I apply it. When in this...
2
by: Fred | last post by:
I know the basics of Access 2003 to the point I can made simple queries and very simple reports. I would like to know a bit more. Can anyone point me in the way of tutorials or literature so I...
11
by: sunnyalways4u2000 | last post by:
hello sir, Sir will please tell me the exact difference between C and advanced C...what are the extra features or funcions...etc added in this advanced one.
5
by: makaman | last post by:
Heys guys, i really need help bad. i'm making a program, and i only want registered ppl to use it, but i'm having lots of problems. i want to make a advanced login system, so that the username and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.