EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Access Alternative

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Access Alternative

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

s_sykes
03-29-2006 @ 3:06 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 46
Joined: Mar 2004

All,

I've built quite a few apps with CF and Access, but the one I'm working on now is a problem.  My users (approx 10) want to load a list of about 7 million records into a DB they can easily search and make minor changes to.  From my calculations that will make an Access DB of at least 1.5 GB if I'm lucky.

I'm thinking this is more than Access can handle, but I don't really know what my other options are.  As always, I need to get this going quickly, but have limited experience with other DB's.  I thought about SQL server, but I don't own a copy and wonder if any ISP would let me load that many records anyway.

Any suggestions would be appreciated.

Thanks!

maquiladoras
03-29-2006 @ 3:09 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2073
Joined: Dec 2002

Go for something free

Such as MySQL or PostGreSQL
your hosts most likely have mySQL anyway
(its free and powerful and catching up)

Also the big hitters such as Orcale, and MSSQL have free versions too

Best thing to do is set up a dev server on your machine, play about with mysql, or whatever db you go with and determine the space it then creates and then either ask for a MySQL DB from your hosts (you may have to pay for more if they dont automatically include it) or go about finding another host (as this is really a basic function of an ISP hosting your website)

-
Any code printed thus far is likely to be stolen and used by Ray Horn, well done...ray
-

s_sykes
03-29-2006 @ 3:22 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 46
Joined: Mar 2004

Does MySQL come in multiple flavors like Linix or is there just one place to go and download?  Do you think importing one table from Access would be quick and painless?

Also - any tips on setting up a datasource connection to MySQL in CFAdmin? I don't think that's one of the standard drivers you can pick from.  It's probably simple so I apoligize for my ignorance!



maquiladoras
03-29-2006 @ 3:25 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2073
Joined: Dec 2002

MySQL is multiplatform (linux, windows, bsd, unix, OSX) etc

Migrating from Access to MySQL can be a little testing, you can either do it manually or you can buy a program to copy everything, there are also tools such as MySQL Front that can do Access and ODBC imports and exports

MySQL 3.x has a native drive from CFMX6.1/7
for MySQL4 and MySQL5 however, you will have to download a JDBC connector from the MySQL website and then use the "Other" DB connector and provide a jdbc:// connection string.

Its all very well documented, and pretty easy to set up.

-
Any code printed thus far is likely to be stolen and used by Ray Horn, well done...ray
-

s_sykes
03-29-2006 @ 3:31 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 46
Joined: Mar 2004

Would you recommend one version of MySQL over another or just go with the latest?

maquiladoras
03-29-2006 @ 3:33 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2073
Joined: Dec 2002

New version is probably the best to go for since it includes features that have long been needed, such as Stored Procedures (this was always one argument that made people claim that MySQL was not enterprise ready)

-
Any code printed thus far is likely to be stolen and used by Ray Horn, well done...ray
-

mquack
03-29-2006 @ 3:35 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

I would go with the latest version of MySQL.  Also, Navicat is an excellent program for working with MySQL, and you can even use the fully functional demo without purchasing if you just want to use it temporarily to get your db migrated over.

http://www.rachelqueensg.com

s_sykes
03-29-2006 @ 3:35 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 46
Joined: Mar 2004

Thanks so much!  I'll give MySQL a go.

johk
03-30-2006 @ 6:11 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 8
Joined: Aug 2004

Hi,
Is anyone using CfMyAdmin with MySQL??
I have also always been using only Access in my application but from my understanding using something like MySQL is more powerful and stable than Access.
How much load/traffic can a access db handle??

s_sykes:
I hope you don't mind me posting this in your thread but I thought it is sort of along the same subject

Regards
Jonas

mquack
03-30-2006 @ 11:37 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

johk-

Just my opinion, but cfMyAdmin is pretty basic and is therefore only really useful in the most basic of situations.

As to Access, if you intend on your development project EVER seeing ANY serious amount of traffic, do yourself a HUGE favor and ditch Access.  While it is a great tool to learn the basics of database design/development, it is terribly slow (because it's file-based instead of server-based) and is also quite limited in size.  And, the bigger the db grows, the slower it becomes.

http://www.rachelqueensg.com


Website Designed and Developed by Pablo Varando.