Access limitations

[webmarkart]

I did a search so I'm pretty sure I'm not repeating an earlier thead... I've heard something like access can only hold 10,000 records, or something about its data capacity. I'm just trying to find out if there is any truth to it or if I can expand it indefinitely with the understanding that the weight (size) of the db will become huge and cumbersome. I need to find out before I start my next project because it will require connecting several tables within several databases. Any body know or heard anything like that?

[firepages]

there is a limit to table size in MSAccess though I thought it was a little higher than that, but I would suggest that on that scale MS access will fall apart under the pressure long before you run out of record space
If you are tied to MS then SQL server is proably your next best bet, with SQL server or even MySQL the only limit is disk space + they are much more able to handle the queries you would be throwing at them

[bcarl314]

I've heard the Access can only handle about 10 connections at once before it crashes. Anyone know if that's right? If so, how many simultaneous connections can mySQL handle?

[oracleguy]

http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Table_size

When they Refer to 'Linux-Alpha' by the way, they mean if you have a Alpha Processor based server.

From MySQL documentation:
Handles large databases. We are using MySQL with some databases that contain 50,000,000 records and we know of users that uses MySQL with 60,000 tables and about 5,000,000,000 rows


I believe with MySQL the number of connections ultimately is a matter of how many simotanious connections the OS can handle.

[webmarkart]

I am familiar with SQL Server/Oracle 8, how much different is My SQL. I already checked with my hosting company and they support MySQL. Would there be any additional cost to use MySQL? Not to be a pain, but if someone could give me a brief explanation on how access, sql, and mysql are different, I would reall appreciate it.

[oracleguy]

Well the differences... hmmm... Well Access is designed for smaller scale uses. And isn't very efficent nor fast compared to MySQL or SQL. MySQL is a very scalable program, and it is covered under the GNU public license, so that if I'm not mistake allows you to alter the source of the softare to suit you if you need to. Plus MySQL is free while Access isn't that expensive. SQL can be... very expensive especially for web servers that are multi-processor. SQL has a lot of good features and capabilites, for example being able to publish data from one table in an SQL server to another SQL server automatically.

As for system requirements, I know from personal experience SQL can be a memory hog. I had a server that kept crashing because it only had 512MB of RAM and with SQL recieving constant data is would lock up about every 25-26 hours. Doubling the ram seemed to have solved that problem. MySQL on the other hand is pretty light from what I've seen. I haven't used a MySQL database for heavy querying yet or 100,000s records either.

Hope that helps.

[firepages]

Also whilst MySQL uses pretty much standard SQL it also has a few non ANSI commands as well (thats neither good nor bad just a statement !)

MySQL also lacks some of the functionality of 'true RDBMS's' - it does have transactional capabilities but no foreign keys nor triggers nor stored procedures to name the major complaints) of the above you are likely only to have come up against foreign keys (in general web-usage) which most PHP'ers have got used to coding around.

[webmarkart]

First I just want to say thanks for everyone who has replied, you've all been helpful so far...

Okay, I went to mysql.com and downloaded MySQL 3.23 (mysql-3.23.51.zip). I ran the setup and installed it... It created a mysql folder on my c drive but nothing I click on in the folder opens anything. I want to be able to create mysql databases, did I download the right thing? How do I transfer contents from an access db to mysql?

[firepages]

MySQL has a command line interface or you can access it via 3rd party GUI's or via a web-based interface such as phpmyadmin.

there are a couple of GUI's on the mysql site for download but my favourite (especially for import from MSaccess etc) is MysqlFront
which you can grab from

http://www.mysqlfront.de

, another useful download would be MyODBC from mysql.com which lets other ODBC based apps talk to MySQL

[webmarkart]

Okay, I downloaded MysqlFront and I was attempting to set up a connection, but obviously since i am posting again it did not work! I tried entering the domain with the ftp id and passsword but I get an error so I tried entering the ip address... nope! What am I doing wrong now? Am I just being a spaz or what?