multi-user, multi-database environment

[delikedi]

Hello.
Soon I'll be deploying a multiuser database application, with Access as FE and BE. Although I haven't actually "lived" in one, from what I read and experiment, there can be constructed such an architecture that all users can connect to a main database, and some of the users also connect to group-specific "catalog databases", and even produce user-specific "job" databases. I've experimented with Workspace.Opendatabase method, and also experimented using connection strings to add/delete linked tables. I believe such an architecture will help me reduce unnecessary network traffic, avoid some limitations of access files (combined file volume will exceed 3 gb) make the master database lighter, and the application to run smoother, with unnecessary overhead avoided. I also understand that referential integrity cannot be possible with multiple databases.

What has your experience been on this subject? Which methods have you tried and found to be true?

 

[delikedi]

Thanks for your reply, Pat.

Yes I'm curious about SQL (express) server as well. It is my understanding that SQL (express) and Access (runtime) are two free small-scale solutions with SQL express being better overall. However I won't have the resources (knowledge-wise or server-wise) to implement a SQL server in the near future. So I'll have to stick with Access at the moment.

I was led to believe that 20-25 FE users can operate a 100 mb backend without too much congestion. I plan to provide connection to several 300 mb read-only databases for about 10 of those users, though such connections will not be as frequent as access to the master database. I saw that I can easily query and copy records from those databases using the DBEngine.Workspace object. Since such querying won't happen every day (maybe twice a week) I chose not to include the auxiliary database files as linked tables for each session. Then again, I guess linking and unlinking operations can be wrapped inside functions that are called on demand... I believe that it would be unlikely at any one given time for the whole system to exceed 20 concurrent users and 1 GB of total connected files. The 3GB I mentioned would consist of many read only and archive databases that are seldomly accessed.

 

[delikedi]

In the architecture that I'm currently testing, the standard front end file which I develop is distributed to all users who have the runtime version of Access. The backend resides on my machine, with users connecting to it through the local network. Thus my machine takes on the role of a server that utilizes the ACE database to serve clients (I'm using Access2007).

I understand, with help from your replies, that this architecture can be upgraded by turning the server machine into a true server that utilizes the SQL database engine. The front ends would need some modification, but that can still be done in Access. Also, I guess a true server shuld have no other task than to serve, which means I could ask my boss for a new computer

I guess my initial question can be refined as follows: How far can one push an architecture that relies on multiple Access backends located in multiple computers that act as servers, with multiple users using Access front-ends? Would the idea of fragmenting the main backend into smaller ones and locating them to multiple machines (like a decentralized p2p network) work? or would the absence of referential integrity cause the whole system to be inconsistent?

 

[JLCantara]

First of all, you should move the DB to SQL Server Express; the advantages are huge and the work involved is minimal. Note that it is limited to 2GB but you can have many copies on the same machine.

If your application (FE) was properly made (no direct access to tables, no textual queries as data source), no change will be needed since the queries will be transfered with the data (they become views).

Since you are talking about archives, you should be able to split the DB into logical subunits...

JLCantara.

 

[ButtonMoon]

First of all, you should move the DB to SQL Server Express; the advantages are huge and the work involved is minimal. Note that it is limited to 2GB but you can have many copies on the same machine.

The limit is actually 10GB.
http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

 

[JLCantara]

Hi ButtonMoon,

Your right: I am using 2005 version because I fear compatibity problems with Access 2007: may be I am wrong...

JLCantara.