Access vs MySQL database

[swagtails]

Hi Folks,

Could someone please explain at what point should I stop using an Access database & convert to MySQL or later versions of SQL.
I have a small project to develop but am concerned about using Access incase the project outgrows it...
If someone could just spell out the constraints of Access I'd be very grateful

Cheers
:p

[lmf232s]

how small is the project and how large can it get?

i believe that access can not go past 2 gigs of data,
if you are planning on more data then that in the future
use mysql,

also their is an issue with concurrent user access.

I dont know the exact number but well say access can only
have 100 concurrent users where as mysql can have tons more than that. Those are not the exact numbers as i do not know exaclty
but if you are planning on haveing alot of data and say more
than 100 usese hitting the db at one time you may want to choose
mysql else access is just fine.

[chrismartz]

I use access and haven't had any problems with it

[smarties]

The problem with access is not the amount of data you can store but the amount of simultaneous connections.

Microsoft claims that access can handle up to 255 simultaneous connections, but, based on my personal experience, I can say that this is too much. I used Access on several ecommerce and when the traffic is too high, first the db slows down, then crashes.

MySQL is a faster and more robust solution. But if you have the option, you may also go with MS-SQL wich will make your migration easier.

[chrismartz]

What is MS-SQL, and how hard is it to transfer from access?

[BuezaWebDev]

Is MySQL fully relational compared to Oracle being relational?

[zingmatter]

What is MS-SQL, and how hard is it to transfer from access?

That would be SQL Server, which is a very powerful db that isn't particularly easy to use compared to Access. However, migration isn't all that difficult, you just change your connection string and your average SQL command will work fine on it. I suspect ADO will not have any issues either. You do get the option of 'Stored Procedures' (amongst loads of other features that Access doesn't have) that allow to run more complex/mulitple queries very efficiently. SQL Server is 'grown-ups' DBMS, while Access really is a desk-top application.

Is MySQL fully relational compared to Oracle being relational?

Dunno anything about Oracle, but MySQL, SQL Server and Access (and probably all databases) are relational.

As for the original question, MySQL is definitly better than Access. The problem is that ASP doesn't connect all that well to MySQL, and PHP would be you choice of scripting language for MySQL. MySQL is also open source whereas SQL Server will cost you mega-bucks.


Hope this helps

[russell]

In practice, MS Access can handle ONE connection at a time. ADO will marshall multiple connections, so that you can have several before you notice any degradation in performance. If you are going to have many concurrent connections, Access isn't going to be able to handle it. Access is relatively unstable and is not suitable for a web back-end if the data is modified regularly -- unless you have a very small audience.

ASP should have no problems connecting to MySQL, but MS SQL is the best choice for connectivity and performance when communicating between windows servers via asp.

MySQL, SQL Server and Access (and probably all databases) are relational
By the way, not all databases are relational. In general, when talking about databases, we mean relational databases, but there are other databases too.

[zingmatter]

By the way, not all databases are relational. In general, when talking about databases, we mean relational databases, but there are other databases too.

Oh. I didn't know that, how would you use such a database?

(I'm not a big expert on db's, so excuse my ignorance :rolleyes: )

[swagtails]

;) Thanks to everyone who has replied to my post.

It has certainly cleared up the basic questions that I had & based on this I can now go ahead and make an informed decision.

Thank Goodness for forums like this

Cheers !

[BuezaWebDev]

Originally posted by zingmatter
That would be SQL Server, which is a very powerful db that isn't particularly easy to use compared to Access. However, migration isn't all that difficult, you just change your connection string and your average SQL command will work fine on it. I suspect ADO will not have any issues either. You do get the option of 'Stored Procedures' (amongst loads of other features that Access doesn't have) that allow to run more complex/mulitple queries very efficiently. SQL Server is 'grown-ups' DBMS, while Access really is a desk-top application.



Dunno anything about Oracle, but MySQL, SQL Server and Access (and probably all databases) are relational.

As for the original question, MySQL is definitly better than Access. The problem is that ASP doesn't connect all that well to MySQL, and PHP would be you choice of scripting language for MySQL. MySQL is also open source whereas SQL Server will cost you mega-bucks.


Hope this helps

What are the features of MySQL that make it relational?

[zingmatter]

My understanding of a relational db is that if you have 2 tables, say Books and Authors it might look like this:


Books
---------------------------------
BookID title AuthorID
---------------------------------
1 The Woodlanders 1
2 The Trumpet Major 1
3 Tale of Two Cities 2

__________________________________

Authors
---------------------------------
AuthorID Name
---------------------------------
1 Thomas Hardy
2 Charles Dickens

__________________________________


Then you have a relationship between the 2 tables, so using SQL you can do:
SELECT Authors.Name AS auName, Books.Title AS boTitle
FROM Authors, Books
WHERE Authors.AuthorID = Books.AuthorID
AND Books.BookID = 1

Which would give you:
'The Woodlanders' and 'Thomas Hardy'
This is an inner join that uses the unique key field AuthorID (primary key in Authors, and forgein key in Books)

That's what I presume 'relational' means when talking about a db but prehaps a db pro might know better.

Hope this helps

[russell]

A hierarchical (not relational) database might look like this:
+ HKEY_CLASSES_ROOT
- .exe
- PersistHandler
- default
+ HKEY_CURRENT_USER
+ AppEvents
+ EventLabels
- .Default
- ActivatingDocument
- AppGPFault
- Console
+ ControlPanel

This, you will recognize, is the windows registry.

"Relational" databases are those that can be stored in a collection of tables, as opposed to a "flat file" format, where all the data is in one file. A "hierarchical" database is stored with parent/child information.