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 !
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:
or
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.
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
Five is a very small number of users and Access works very well for small offices, if it is set up properly. The database must be split, with each user having a copy of the front-end. This does not mean that each user needs a full copy of Access, the runtime version is sufficient. 2007 runtime is free (http://www.microsoft.com/downloads/details.aspx?familyid=d9ae78d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en)
You should read this thread that deals with many misconceptions: Is MS Access (JET) suitable for multiuser access?
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).
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
If you've only got 5 users and your tables are pretty small, simply store the .mdb file on a file server and you should be fine. (Splitting the database into frontend and backend is probably not a bad idea.)
If you have a lot of users or a shared fileserver isn't an option, you might be able to use the EQL Data plugin to replicate the data up to a central server, without having to go so far as setting up MS SQL.
Access is not the best database for sharing data but it can be shared between several users. See here
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.
