Functionality of Access Database

[JulietB]

I am working on a database in Access that has 35 tables, the size of the Database is 23.9MB / 24,484KB. What I want to know is how much data can an Access database hold in terms of size? Does different users have to be taken into consideration? My supervisor eventually wants to place this database on the Web/Internet, again how does size come into play,is it an issue?

My other question is can you query on two different databases? For example linking them to get info from both through a query?

Thank you
Juliet

 

[JoeMiller]

Access has a limit of 2GB per database. You can overcome this limit by using linked tables so that forms, queries etc are in one db and all the data is in another, or several other db's. As for users, MS will tell you 255 users is the limit for Access. A better estimate is between 10 and 50 users tops. Access will work on the web, but you need to look at the target audience and plan appropriately. The number of hits may be too much for access (the user limit and hits are somewhat related). You may want to look into a true server based database for such an endeavour.

HTH Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

While I generally agree w/ JoeMiller, I have implemented multiuser dbs using Ms. Access only with 20 to 50 concurrent users without significaant problems. You generally do need to exercise careful design approaches to minimize the record locking between users and educate the user community on 'good net manners' (e.g. DO NOT let them 'sit' on a record), but simple read access to the data was never a problem. The database added approx 1K records per month (to a "Main" table) for over a year, and edited the records an average of four times each. 90% of all activity re a record occured within 72 hours of initial record generation, so activity was highly concentrated on recent (and therefore "near" records).

I have used Ms. Access in various releases for over ten years and 'inhrited' dbs from more 'failures' than I have counted. So far, at least, I have been able to make every one of the dbs operable within multiuser environments and without restricting the number of concurrent users. The highest number of users I have ever personally seen logged on concurrently was 75. At that level of activity, there was noticable performance degradation - not just for the database app, but for the entire local area network.


In general, my experience suggests that Ms. Access gets a "bad rap" mostly because it is way to "USER FRIENDLY". A lot of 'developers' start out thinking that Ms. Access is just a consumer product. Well it isn't. It is a relational database. Excel and word users start out making a mail-merge db - sucessfully, and continue to their destruction by thinking "it is easy to ... with the db". Failure to even start to understand relational dbs and SQL doom them to the age old "G I G O" syndrome and from there, it is a short step to complaining about the product vs. their proficiency.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jfischer]

In answer to your question about query across multiple databases, yes you can. All you need to do is link the tables you need from each external database, whether they're Access/Excel/SQL Server/ODBC/dBase/Paradox/Text, etc., to the local database where your query resides. Then use the linked tables just as if they were local tables in your queries. It's no different than using a query to tie together a local table and a table linked from one external database.