In terms of capacity and performance in manipulating large amounts of data. Which is better, ms access manipulated by vb.net or mysql manipulated by php.
A:
Without any hard evidence data to back this up, I'm sure it is hands down mysql with php where the mysql part would be the killer feature.
Lieven
2010-01-28 09:17:51
Without any hard evidence to back this up, I'm sure this answer needs to be downvoted.
JohnFx
2010-01-28 20:55:24
Without any hard evidence of what you've been drinking, lay of the coffee.
Lieven
2010-01-29 07:49:13
+11
A:
MySQL is a full blown relation database management system suited to a variety of application domains. It can performs and scale to support applications with a global audience. It has support for stored procedures and SQL, therefore a lot of the data manipulation can be done in the database system.
MS Access is suited for small office applications with limited number of users with small to medium complexity. It has a Rapid Application Development (RAD) approach to building solutions that is well suited to small "record data and display results" type applications.
Paul
2010-01-28 09:20:29
Completely wrong, as the question assumes Access = Jet/ACE, while this answer does not address that crucial distinction. Access is not an RDBMS, but an application development tool that allows you to use just about any database engine you like as your data store. Edit your question to refer to Jet/ACE and I'll back out the downvote.
David-W-Fenton
2010-01-28 19:05:45
Disagree. It is obvious to most what is being asked here. I believe Access is a great tool to access databases (pun intended). It is even a great tool for holding your dvd collection. It goes flat on its belly when you have multiple users accessing and changing data.
Lieven
2010-01-29 10:01:31
If you can't create a multi-user Access app with a Jet/ACE back end that 10-20 users can use simultaneously, then you are INCOMPETENT. For larger user populations, for more than 2GBs of data, sure, you need something different than a Jet/ACE back end. But the question doesn't define its terms, so it is just trolling, seems to me.
David-W-Fenton
2010-01-29 19:31:01
No need to shout and no need to call someone incompetent. I wholehartly agree that I am a layman when it comes to Acess. Most encounters I had with it were painfull. That being said, when it comes to programming, I'm sure I'll match your knowledge.
Lieven
2010-01-30 12:14:18
But you've made a categorically false statement: "It goes flat on its belly when you have multiple users accessing and changing data" -- that is simply not true, a complete lie. Now, it may be that *you* don't have the chops to do it right, but that doesn't mean it can't be done. Indeed, it's not even very hard if you follow best practices (which are 99% common sense). Many people screw it up, no doubt, but that doesn't change the fact that you made a statement that was FALSE.
David-W-Fenton
2010-01-30 17:23:10
"it may be that *you* don't have the chops to do it right". You should loosen up the attitude, it's offensive.
Lieven
2010-01-30 19:20:56
@Lieven: Making stupid categorical false statements about something you don't understand is offensive. If you don't want pushback on that, then stop posting that kind of tripe.
David-W-Fenton
2010-01-31 19:29:54
@David-W-Fenton - I don't mind being corrected when I'm wrong. If I'm not mistaken, that is exactly what I did with me confessing that I'm a layman when it comes to Access. What I do mind is being called incompetent, not having *the chops* to do something right, being in the 1% of people not having common sense and having to read between the lines that I'm just plain stupid.
Lieven
2010-02-01 08:20:11
@Lieven: you seconded erroneous statements about what Access can't do. It's like someone saying "that piano is not a musical instrument because I can't get any music out of it."
David-W-Fenton
2010-02-01 21:00:08
@David-W-Fenton - lol. I love the piano anology and you are right, I already agreed to that.
Lieven
2010-02-02 07:42:30
@David-W-Fenton: I do agree that msaccess often gets too much trashing, but still, implying that ms access is on par with any real client-server database (mysql, postgres, mssql, oracle, db2) is a bit misleading to someone with very little experience - difference in concurrency, availability, security, better support for the standard (triggers for example) and scalability should be emphasized between desktop database systems and real client-server systems (here's an older, but still relevant, article http://databases.aspfaq.com/database/what-are-the-limitations-of-ms-access.html)
Unreason
2010-04-19 20:08:18
@Unreason: you haven't read a single word I said (or, at least, you haven't COMPREHENDED what I wrote), otherwise, you wouldn't assert that I implied that "access is on par with any real client-server database". I haven't said any such thing, because the comparison is COMPLETELY INAPPROPRIATE for a number of reasons, all of the outlined above.
David-W-Fenton
2010-04-21 01:11:28
A:
I do not believe MS Access is not appropriate for large-scale data manipulation and storage; This is the reason that SQL Server exists.
Also, there are odbc connectors to use asp + mysql. So you could go that route if you are comfortable with ASP. However, as a personal preference, I'd go for php/python + mysql
sfrench
2010-01-28 09:20:37
A:
MS Access is not designed to handle large amounts of data and at the same time perform well (the same can probably be said about VB.NET). It is mainly a tool for desktop databases, not large data centers.
MySQL on the other hand is very scalable (some would argue not, but compared to Access...), and is optimized for great performance with large data. Again, this is relative to Access. MySQL has more competitors in its field than Access has in its own field.
I would say true competitors of Access' database features include OpenOffice Base and SQLite (though without a dedicated GUI).
Competitors of MySQL include Oracle, PostgreSQL, MS SQL Server, and tons more.
Tor Valamo
2010-01-28 09:21:26
A:
Microsoft Access is so unsuitable for concurrent access and large amounts of data that you notice it as soon as you start testing.
Álvaro G. Vicario
2010-01-28 10:46:11
A:
Access to SQL Server Upsizing (SQL as a backend)
To scale Access applications to enterprise or web solutions, a recommended technique is to migrate to Microsoft SQL Server or equivalent server database. A client-server design significantly reduces maintenance and increases security, availability, stability, and transaction logging.
Reference: WIKIPEDIA
it shows Access is not able handle large data.
neverSayNo
2010-01-28 12:29:43
A:
If its a multi-user system doing anything other than pure data-entry at a single workstation (i.e. NEVER fetching data back while data is being entered) then MS Access appears no worse for purpose than MySQL. However in my experience, copying a MDB file from one PC to an apparently identical PC then often a lot of the VBA code / forms and sometimes even the data itself is not available.
MSAccess does not handle concurrency well.
IIRC, the JET engine does implement "Rushmore" (i.e. what other vendors refer to as Skip-scan) which can sometimes use indexes more effectively. AFAIK MySQL does not. This may be a consideration if you have a very complex data schema and the database is essentially read-only.
The one thing you can always be sure of with Microsoft is that very soon they'll release a new development language which will make everything they did before now redundant. Although PHP has seen some changes over the years forward and backward compatability is in a different league from MS VB.
HTH
C. (recovering MSAccess developer)
symcbean
2010-01-28 14:13:47
+2
A:
Although others here have incorrectly claimed that Access can't handle large amounts of data, it can, you will want to go to a more heavy duty system like MySQL or SQL Server when the need for high availability, high concurrency and advanced RDBMS features justify the extra licensing and hardware costs.
JohnFx
2010-01-28 16:20:44
+1
A:
All the answers to this question are WRONG because the question itself is WRONG.
Access is not an RDBMS, but an application development tool that allows you to use just about any database engine you like as your data store. Thus, Access can handle any amount of data that the database engine you're using can handle.
Now, Access ships with a default database engine, Jet/ACE, that is not a server database engine. It's file-based and has all the limitations that other file-based database engines have. If you understand that and engineer your application to account for it, you can get quite a lot of mileage out of Jet/ACE in terms of amount of data (subject to the 2GB file-size limitation) and number of users.
The question is basically meaningless as it doesn't define "large amounts of data." OF COURSE Jet/ACE cannot handle enterprise-level data sets -- nobody would (or could) run Amazon off of a Jet/ACE data store.
But within the range of amounts of data that Jet/ACE can handle, Jet/ACE will likely perform very well in many scenarios in comparison to any other database engine, particularly if you're just analyzing and manipulating a data set as an individual user. Access + Jet/ACE can be a very useful toolset for working data sets that fit well within the 2GB limit. Sure, there are some kinds of operations taht Jet/ACE will be a dog for, but likewise for MySQL. All db engines have weaknesses where certain operations are not well optimized.
Choose your db engine according to the tasks and requirements of your project.
The other great thing about using Access + Jet/ACE is that if your dataset outgrows Jet/ACE, you can move it to SQL Server or MySQL or PostgreSQL or Oracle or whatever and continue to use the Access database where you've built all your data manipulation tools. Sure, some of them might have to be slightly re-engineered, but you don't have to start from scratch.
David-W-Fenton
2010-01-28 19:15:01
+1 for the thorough response, although I think you may be tilting at windmills with the JET/ACE lecture. Still appreciate you taking the time to make the distinction.
JohnFx
2010-01-28 20:57:07
I've been tilting at that windmill since I first registered on SO in Sept. 2008. A lot of bad answers get posted precisely because people ignore the distinction. Most of these people who are confused never use either Jet/ACE or Access (except perhaps completely casually, the way I've used PowerPoint about 10 times in the last 15 years), but they are posting seemingly authoritative content that make no sense in the real world. So, I'll continue tilting at these windmills.
David-W-Fenton
2010-01-29 19:28:16
... and as you mention it yourself, OP should choose his db engine according to the tasks and requirements of his project.
Lieven
2010-01-30 12:21:02