MS access table as centralised location for storin

Question

Is it possible to make my MS access tables as a centralised location for storing data

I have an mdb access file to store data into a table using a form.

Is it possible to enter data to a centralised location?

this mdb file copies are used by five user at same time

Please help !

Answer 1

I have seen access choking many times in multi-user setups.

Don't do it - use something like SQL Server Express instead and save yourself a lot of hassle

Answer 2

I'm with Galwegian on this one.

Install SQL Server Express or better. You will need to make sure that it allows remote connections see (http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx).

Then you can either:

• Create a new Access Project that uses the SQL Server database as it's backend

or

• Use Link tables in your current Access database to link to the equivalent tables in SQL Server

Once this is up and running you can start to think about creating Queries/Stored Procedures on SQL Server instead of having this functionality in Access.

Answer 3

Access is not the best database for sharing data but it can be shared between several users. See here

Answer 4

Jaison,

You CAN use Access as your centralized data store. SQL Server is the OPTIMAL choice if you are just starting off.

But it is simply NOT TRUE that access will choke in multi-user scenarios.

It IS true that you need a good backup strategy with the Access data file. But last I checked you need a good backup strategy with SQL Server, too. (With the very important caveat that SQL Server can do "hot" backups but not Access.)

So my answer is different...you CAN do this so that by the end of the day today you can be deployed and multi-user. Then perhaps you should begin moving toward upfitting your current application to use SQL Server.

I recently answered another question on how to split your database into two files. Here is the link.
Creating the Front End MDE

This should get you started.

Seth

Answer 5

You can divide your Access application into two files, one with the user interface (ui.mdb) and the other one with the actual tables (tab.mdb). The code in ui.mdb needs to reference the tables in tab.mdb. That way, you can store your tab.mdb on a network share, where all users (each with a seperate ui.mdb on their local drive) can use it.

That being said, I fully agree with Galwegian: Don't do it.

One of the problems with your approach is, the query is performed on the client. A select foo from bar where fizz = buzz query needs to load all fizz entries in bar to check if the where clause is true. His approach replaces the tab.mdb with a small database server. That way you can send a query to the server, which returns only the requested data sets, with much less network activity.

Answer 6

I ran a 10 user split front/back end application for several years without any real performance problems, though it obviously depends on the size of your data and I agree that the optimal solution would be to use a proper database server.

Crucially though, SQL Server Express requires installation on a server, whereas an Access .mdb back end can sit on a network drive. If you are in a low-resource environment where all you have is a network drive, then an Access set up is a good solution.

We did in the end migrate the data to SQL Server and redirected the front end, but more for security and backup purposes (centrally managed).