Unanswered: Can Access handle huge amounts of data?
My question is that can Access be used as a back end for an enterprise application? What I mean is that if I want to use a database that will contain hundreds of thousands of records and eventually go on increasing, can Access be a solution for that? Can Access handle such huge amounts of data fairly efficient without crashing or affecting the performance? I work in an engineering firm and they want to shift their database, currently on foxpro, to some other database. They have given me an option to use Access, but I just want to make sure if Access will be able to handle huge amounts of data or not? Thank you all in advance for your time and effort in replying to this query.
Access can handle 2GB in Access 2000. You can also access more data using linked databases. If you application is a single-user analysis with maybe some reporting out to aditional users is can work. I have used access for fairly serios production work ion the financial sector and is is viable (if not ideal) with over a Gig of data.
What it will not work well for is multi-user access with lots of people making chnages to records at the same time and multiple users throwing big queries at the same db. For that sort of thing you need a proper sever based database.
Give us a little better idea of what "Enterprise Level" mean in your context and we could manage a better answer.
First thank you for the time to answer my query. Well, by "Enterprise Level" I meant that, multiple users are accessing the database, say around 50-70 people. Basically ours is an Engineering firm, and what here people are doing is that they have a template database. As soon as a new project is started, a copy is made of the template database and once the project is finished, the database is archived. So each new project means a totally new database, meaning it is not a good solution at all with lots of repitition of data. Currently, on each project, simultaneously around 5-7 people work, so Access works fairly fine and efficiently. But what I want to do, and what my bosses want here is to have a centralised DBMS going with other certain databases that they are having to be integrated into one single system. So I just want to make sure if Access would be a good option or not, considering the scenario of 50-70 multiple users and each database, on completion, amounting to 30-40 MB, not forgetting to move the past data into the new system? (I presume the past data alone would make for more than 1 gig, and this database will constantly increase.) If not, what other option can be viable?
Originally posted by Prathmesh
My question is that can Access be used as a back end for an enterprise application? What I mean is that if I want to use a database that will contain hundreds of thousands of records and eventually go on increasing, can Access be a solution for that? ...
Microsoft Access is not designed for that, and only grief can come from trying to make a product do what it was not designed for.
What it is best at, I believe, is the purpose implied by the product's name: Access. The product is superb for working with data on SQL-based servers (not just Microsoft's!). It provides a very complete SQL implementation of its own which allows the local microcomputer to be "much more than just a dumb terminal." The workstation and the remote server really can "divide and conquer" with both parties being well equipped to do real work.
I would encourage you to plan from the outset to use some SQL-based server as your primary data store. But do not limit your review only to Microsoft's offering! There are half-a-dozen strong contenders and you should systematically look at all of them. These tools are designed to handle the data and transaction volumes you envision and can work very well with Access.
Originally posted by Prathmesh
First thank you for the time to answer my query. Well, by "Enterprise Level" I meant that, multiple users are accessing the database, say around 50-70 people. Basically ours is an Engineering firm, and what here people are doing is that they have a template database. As soon as a new project is started, a copy is made of the template database and once the project is finished, the database is archived. So each new project means a totally new database, meaning it is not a good solution at all with lots of repitition of data...
A good strong database would keep the data for all projects in one repository and would not require "a new database for each project." Each new project-ID uncovers a new set of records for that project, possibly through the use of "views."
Bottom line is: an SQL-based database is needed; Access is not "it"; plenty of good contenders (some of them even free-of-charge) are.
You definitely have a requirement that deserves a comparative trial with exactly the same rigor and discipline that this engineering-firm applies to its own physical projects. This is mission-critical to the firm. Take the time to do it right.
(n.b. by saying this I am not implying or suggesting that you would do or sanction anything less! Absolutely no affront is intended.)
Another thing to consider is the network setup. a database with 5 or fewer people accessing the front end in Access is fine in a client server setup, but over 10 users is slow. I would suggest a strong terminal server to minimize the movement of data over the network. If you use 2 servers, one for the database (RDBMS, like Oracle, DB2, MS-SQL, PostgreSQL, MySQL, etc.), and the other for Terminal server (Unix or Microsoft based -- as long as it can run Access), you can save a lot of slow downs. Of course, there are other programs in which you can create the front end other than Access, but go with what you know. If you already know access, then stick with it and use ODBC to the RDBMS of your choice.
So jmr, what you mean is that for front end I should use Access and in the backend, any other relational database for the actual heavy data store. Seems to be a plausible solution to me, because my company already has the database ready, except that it is structured really bad ( and I mean REALLY REALLY bad!!!), in terms of the relational structure. I need to restructure the whole thing, but if I can keep the front end as it is with all the necessary reports and forms, then I just have to deal with the backend and saves me time to create all those forms and reports all over again. Other options is also web based interface since we do have a LAN here, but I need to investigate the speed factor over there. I need to do a bit of research before I can start forming a solution. Oh, one last thing, can you elaborate a bit more on that odbc thing?
that odbc thing is not that big a deal. it is open database connectivity which means it lets programs connect to a database. It is how you would have to get access to connect to the backend database. This is a whole other topic, but it may be easier to let a wizard set much of this up for you. I have used Oracle's migration wizard and MS-SQL's upsizing tools. The crux of it is you have to set up an odbc data source. THis is usually found in Control Panel ODBC data sources. It is a fairly simple concept that Microsoft seems to have made confusing to implement. I never remember what the difference is between user, system and file, but either the wizard does this for me or I just try each until I get it. I have no examples here, so I cannot just look it up for you.
Neither do I know what the N stands for in Microsoft's DSN.
Please note, that compared to one person on the system, using an RDBMS may slow down your system. Of course extra tweaking is available in the larger systems, but they also require much more RAM to run well.
well jmr, I have worked with odbc before and I perfectly understand ur "confusion"!! submoronic fruitcake microsoft!! By the way, what I meant by asking you to elaborate on odbc, was that do I need to create an odbc for Access as well as odbc for backend database as well? or just for Access? If for both, then do I need to bring the two odbc data sources together or if it is only for Access, I guess I will need to specify it at some place in the backend database. I just wanted to get the concept cleared and that if I m thinking on the right track or not? Cheers.
p.s. by the way "N" stands for "Name" in DSN (DATA SOURCE NAME). :-)
Well here my comment to the posts. Access can handle more then 1 Mio records... I have a database that has 10Mio records in Access. It just gets slow.
My experience is that Access is fairly fast when you need to do extensive calculations - apparently the jet engine has a pretty good memory handling, and is able to move the right data that is needed into memory. But this is for extensive calculations - which is probably not the case in your application.
Now as the data grows, and you get into a typical enterprise level, I would switch to a real database.
My preference here would be postgresql. Its free, you dont get all the oddities you will discover over time in SQL-Server, and it has a very powerfull language. If you need to run it on windows, there is a good install from dbexpert.