How to setup an access database to a business server?

Question

I'm an intermediate user in access. I've designed and created a database using ms access 2007. This databse was created for the staff within a small company. Everyone has the access to this database with a windows password. The majority can only view the data (from forms) and print reports, while only a few people can insert/delete/update the data within the database. My questions are:
- how do i connect my database1.accdb to the company server so that everyone can use it?
- what is the purpose of splitting the database? Should i split mine?
- where should i go from here?

Thank you.

Answer 1

You need to save the Access database on the server in a folder where everyone who needs to access it, can. This is done by setting permissions on the folder where the database is. You will need to allow update permissions for those people who need to update the database.



The best way to implement this is to split the database so that the client (PC) computers only have the minimum in forms, code modules etc. that they need.



Each user will need a copy of Access on their PC to use the server database. The tables (data source) on these PC's should be "Linked" tables (goto tables, right click on the table list and select "Linked Table". With client PCs using links to the real tables on the server, data is only held in one place.



Users who don't update the database don't need the forms that update so you will probably need an Update client database and a read-only client database. You distribute the correct version to the people that need them.



So.....each client PC runs your database but with links (Linked Tables) to the tables held in a central access database where the data is stored. The forms, reports and queries are stored in the client Access database. Only the data is stored centrally.



Hope that helps.

Answer 2

Access databases really have no business being in a business environment. If you're considering splitting your database, you should consider moving up to the next level. Splitting your database means that you are splitting the application piece from the database piece. This is usually preparatory for migrating to database engine.



If your application is well liked and fits in your professional environment, then you may want to consider splitting your database from your application, converting the db to mysql or some other database, and using an odbc connector to connect your application to the new database.



Have fun! There are lots of tutorials online that you will be able to use as a reference for doing this.

Answer 3

If you want to stick with Microsoft, the ms Access help pages seem to adress your situation:





About upsizing a Microsoft Access database



Upsizing is the process of migrating some or all database objects from a Microsoft Access database (.mdb) to a new or existing Microsoft SQL Server database or new Microsoft Access project (.adp).



When to upsize an Access database to SQL Server



Over time, most database applications grow, become more complex, and need to support more users. At some point in the life of your Access database application, consider upsizing to SQL Server to optimize database and application performance, scalability, availability, security, reliability, and recoverability.