0

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 !

Fionnuala
  • 90,370
  • 7
  • 114
  • 152

8 Answers8

5

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

Community
  • 1
  • 1
Seth Spearman
  • 6,710
  • 16
  • 60
  • 105
2

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?

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
2

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).

Alistair Knock
  • 1,806
  • 2
  • 16
  • 25
1

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.

pjp
  • 17,039
  • 6
  • 33
  • 58
  • What is advantage for link tables over sql server tables ?? –  Jul 29 '09 at 09:32
  • The advantage over Link tables is that you don't need to change your current Access Database to an Access Database Project (essentially a different type of Access database). If you've got the time then create an Access Database Project as this will be a lot cleaner than connecting to your tables using Link Tables and ODBC. – pjp Jul 29 '09 at 10:03
  • Where i needd to specify the network location of the backend database ??? i havent give it anywhere but the front end is pushing data to the BE. What i have to do to change the network shared BE path? –  Jul 29 '09 at 10:55
  • When using SQL Server the data is not accessed by the clients looking at a shared file. Instead the data resides inside the database server and all access is through this database server. Internally the database server will write the files out to disk but this is not what you application connects to. If you're taking the linked table route take a look at the New Link Table, ODBC option. You will need to know how to setup an ODBC connection to your database. If you're using an access project then there is a connection option on the file menu. – pjp Jul 29 '09 at 11:27
  • This is not at all the best answer to your question. The best is Seth Spearman's, because it explains what you asked, i.e., how to share the data with Access, rather than by upsizing to a completely different database back end. – David-W-Fenton Jul 30 '09 at 01:46
0

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

Galwegian
  • 41,475
  • 16
  • 112
  • 158
  • What are problem that may be faced?? –  Jul 29 '09 at 09:30
  • And I have clients with 25 users on an Access backend for the last ten years with virtually not problems. And SQL Server is more work setting up and administering. – Tony Toews Jul 29 '09 at 19:29
  • I've seen someone get behind the wheel of a car who'd never driven before, and what do you know, they crashed the car! Judging by comments I often read from people like @Galwegian when Access/Jet is involved, by analogy I'd expect them to blame the accident on the car, instead of on the inexperienced driver. – David-W-Fenton Jul 30 '09 at 01:41
  • 1
    Ooh, I love car analogies! No, it's more like MS pretended to sell you a car suitable for a learner but sold you something that required it to be driven in a non-intuitive way and you drove it like a regular car and the wheels fell off and then some smart alec comes along and makes you feel stupid because you should have known to leave the handbrake on at all times... – onedaywhen Aug 02 '09 at 14:55
0

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.

apenwarr
  • 10,838
  • 6
  • 47
  • 58
  • And I have clients with 25 users all day long with 800K records in tables. With next to no problems. – Tony Toews Jul 29 '09 at 19:33
  • 1
    The EQL data plugin, assuming you mean EQL Access Geosync, is an expensive option. 30 users synching once a day means $900 per month. Yowzer. That buys a nice Terminal Server system in a short period of time. – Tony Toews Jul 29 '09 at 19:35
  • 1
    Splitting is REQUIRED in this scenario, unless you like corruption. Indeed, the only scenario where NOT splitting is OK is for a single-user app where the user does all the programming. – David-W-Fenton Jul 30 '09 at 01:43
  • @Tony Toews: "I have clients with 25 users all day long with 800K records in tables" -- that's the one you recommended should be upsized to SQL Server, right? – onedaywhen Aug 02 '09 at 14:11
-2

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

luc
  • 41,928
  • 25
  • 127
  • 172
-2

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.

Community
  • 1
  • 1
Treb
  • 19,903
  • 7
  • 54
  • 87
  • 1
    "always loads the complete tab.mdb from the network" is utterly wrong and one of the many misconceptions folks have about Access. – Tony Toews Jul 29 '09 at 19:30
  • 1
    What really happens is that Access downloads only the index and data pages that are required by the query criteria or sequencing. Not as good as SQL Server but definitely not the entire table. – Tony Toews Jul 29 '09 at 19:31
  • I stand corrected and edited my answer. That should teach me to believe everything I'm told without researching it myself ;-) – Treb Jul 29 '09 at 20:22
  • I didn't see your answer before the edit, but it appears to stikll include exactly the same erroneous comment that Tony criticized. – David-W-Fenton Jul 30 '09 at 01:45
  • I don't think so. I originally said that Access was always loading the complete *tab.mdb*, which was wrong, as Tony pointed out. Which part in the last paragraph of my revised answer is wrong? – Treb Jul 30 '09 at 08:20