How can I get a database that everyone can collaborate on?

[alexandervj]

We need an inventory database that everyone can collaborate on from their own workstations. Right now we have an Access database on SharePoint, but we dont like this as the SharePoint web app is very limited in features and we want to have it on a server with a MySQL backend to expand Access' storage limitation. How can we do this? Can several people collaborate on the database at once if its not on sharepoint? Does it update the database for them automatically if someone else changes data in the database?

[ItsMe]

Not sure what you mean by, collaborate.

Only one person can open the DB in exclusive mode to make design changes and such. Many people can connect to the DB and update its tables. Up to 25 users can open a file and use its forms and queries. It is recommended that you split your DB to avoid multiple users opening the same file to access queries and forms. You can give users their own front end file that is linked to a single back end file. The BE file will hold the tables that get updated by the FE files as the users interface with their FE files.

In short, Access is a great tool to manage data and provide user interfaces for multiple users.

[alexandervj]

Thanks! I was actually just looking at splitting the database. So the users are distributed the FE of the database while the BE is in the network folder on the database server, which is linked to MySQL to expand its storage limit. Sound right? Now if the FE needs changes then the DBA would make the changes then redistribute the FE to the users?

[ItsMe]

You can link your Access FE directly to an SQL server BE as an option. There are special things you need to do to the file though.

[NTC]

A sharepoint web app is significantly different than a classic Access (non web) app. If all users are on the same LAN infrastructure - and have the Access license then you can move to a classic Access implementation and have a very rich design. If they don't all have an Access license you can move to a runtime implementation and also have a very rich design.

A potential move to another technology because of size limitation however is a different issue. In a classic multi user implementation (split) it is very rare that the common back end file would exceed the 2G file size limit. If it is then one would move up to SQL Express which has a 10G limit. It is a common misunderstanding that the move to SQL Express will improve speed - it will not. One must implement SQL server side view features - which requires an understanding of SQL Server management/design before realizing any speed improvement - not simply move tables to it. Speed issues are not typically caused by back end file size ability but rather by the user's or server's RAM and networking issues.

[alexandervj]

Thanks guys! SO what I'm thinking of doing is splitting the database and keeping the BE on my network folder and distributing the FE among users, for now. This option will last a couple weeks while I am setting up an internal server which will host the MySQL database on a Windows Server OS (thanks for that windows server speed info NTC). Once the dedicated database server is in place I will no longer "split" the database but will link tables to MySQL and give users access to the database through their installed Access programs. Does this sound like a good plan?

[June7]

That is still a 'split' design, just that the BE will be MySQL tables instead of Access file.

I use code in the FE that will automatically copy the FE when I make an update available. I have my 'master' development file in a restricted folder and I put copy for deployment in another folder that the FE code can copy down. The code checks a value in a table against a label caption. If different then there is a new version to copy down. http://forums.aspfree.com/microsoft-...ue-323364.html