Web Development

General discussion


Database Solutions

By jgreer ·
We currently maintain several MS Access databases for our product quality and production information. In the past years we have noticed an increase in issues with these databases and feel that we are outgrowing them and the demands are becoming too high. What is a low cost solution we might be able to migrate these database towards?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by carlos.nino In reply to Database Solutions

Well one of the best databases you can find is actually free: MySQL (www.mysql.com). At least you can try it.
You can find excellent database software like Oracle or DB2 but the cost is outrageous.

Collapse -

by TonyMeacham In reply to Database Solutions

Considering these are production databases, your best bet would be MS SQL Server. SQL Server is the natural progression for solutions based on MS Access that have outgrown the desktop RDBMS. There are migration wizards to help along the way. I've recently "upsized" from MS Access to SQL Server and now enjoy a whole new world of possibilities including a web based interface to the data and real time eCommerce (via Versign). Although not as affordable as Access, SQL Server is still more affordable than DB2 and Oracle. The Enterprise Manager interface will be very intuitive for the Windows user (vs. tools that accompany DB2 or Oracle). As for MySQL, I would use it for R&amp and some departmental solutions but not for my "bread N butter" production.

Collapse -

by jgullickson In reply to Database Solutions

Microsoft SQL Server is going to provide you with the smoothest migration, including the ability to "upsize" your current Access databases with very little work and in some cases with no work at all (and the ability to keep your Access forms, reports, etc.).

While SQL Server is not exactly inexpensive, you can get many of the advantages by using MSDE; which is a version of SQL Server that Microsoft distributes for free (you can download it here: http://www.microsoft.com/sql/msde/downloads/download.asp ). There are limits to how you can use MSDE (within the constraints of the license agreement) but it is definately worth looking into if only as a "stepping-stone" to a full-featured SQL Server.

You may want to involve a SQL Server expert in the migration process, especially if you choose MSDE as it comes with little documentation and a limited set of tools (compared to regular SQL Server), although most of this information is avaliable online ( http://www.microsoft.com/sql/ ). I would be happy to answer any general questions you have on the migration process.

Collapse -

by WhereIsWaldo In reply to Database Solutions

Actually, Oracle is not that expensive if you know what license you need to buy. If you are using MS Access now I can't imagine that you would need anything more powerful than Standard Edition One.

Collapse -

by jgreer In reply to

Poster rated this answer.

Collapse -

by gsquared In reply to Database Solutions

What I did when moving the business I work for from MS Access to MS SQL was hook up to a remotely hosted SQL database. There are several companies that provide this kind of service. With that, you don't have the costs of buying hardware, software, etc., and you don't have the effort and cost of learning to administer a SQL server yourself.

On the other hand, you also don't have the control over the database you would have if you put in your own SQL server. And you end up with monthly costs based on the service contract with the server provider. Plus, there are some latency issues with any remote hosting solution.

Moving from Access to SQL has been relatively easy. There were a few hitches, such as having to unlearn using "views" as if they were Access Queries (they sure feel like them, but they have different performance issues).

What you could try is setting up an MSDE version of your Access database. This is easy enough to do. (One of the answers above has the download site to get MSDE from.) MSDE only works well if less than five people are using the database at once, but for development and testing purposes, it works well enough. Convert to that, see if it does what you need, then make the decision of whether you expect the database to grow enough in the immediate future to justify getting SQL server set up in-house, or contact some other hosting services and see if they have a price plan that works for you.

(The host I used was HostDepot, based out of Florida. They did well enough till our use of the database grew enough to justify putting in our own servers.)

Collapse -

by txsteveh In reply to Database Solutions

mysql is free as stated above and can be ran on Windows or Linus offering you the ability to use what you already have in place.

Also, by using the myodbc connect which is free also you can actually export your entire Access DB into mysql without any issues.

You then are free to use any language you wish to access the information stored in the mysql DB such as PHP(free) and works on windows and linux, Perl(free) works on windows and linux, ASP you will need a windows server with IIS, or any about any other language.

I just converted a Access DB to mysql the other day and converted the Access form into PHP. The entire process took me an hour and it was FREE and fast as can be.

MS-sql and all the rest should never be mentioned when "low-cost" is a prerequisite :)

Collapse -

by Jose Mir In reply to Database Solutions

MySQL can run in Windows, Linux and NetWare.

Price is a big motivator. But openness, performance, stability, migrations and upgrades costs, among other issues are not to be underestimated.

Regardless if you are a Microsoft shop you should find out more about MySQL and NetWare. Those products could give you a good helping hand, while keeping your cost low and stability high.

If you have less than 100 users you could benefit from using Novell Small Business Suite (J2EE application server, excellent remote access options, apache, tomcat, MySQL, PHP, etc., etc., etc.).

NDS (Novell metadirectory) could manage your mixed environment (NetWare, Windows, Linux, etc.) simplifying administration and increasing security.

I know I'm out of topic, sorry. Take a look at MySQL.



Collapse -

by mvegas In reply to Database Solutions

Because you're "outgrowing" Access, it would be logical to move up to the next level and look at SQL Server. While MySQL is free, there are huge advantages to using SQL Server. Some of the advantages include the most popular features in SQL Server, such as Triggers, Stored Procedures, and Views - all of which are NOT supported in MySQL.

In fact, these features alone justify the expense of purchasing SQL Server - they are that awesome. Thus, over a period of time when you look at the return on your investment, SQL Server will actually save you money. Yes MySQL is free but the man hours needed to program some of the functions that are automated within SQL Server - over a period of time, would prove to be more of an expense.

Answer No. 5 had an excellent idea, if it is even an option - to get a remote database. You can purchase a Developer Edition of SQL Server, much cheaper than the Enterprise Edition and use it to manage your database remotely. You would still have the ability through Enterprise Manager to log into the database and create/edit/delete your tables and/or data. Thus you save the expense of maintaining the server (they'll do that) and you can concentrate on the important stuff - your data.

Collapse -

by DeskTroll In reply to Database Solutions

As previously stated, SQL server is the logical upgrade path for a MS Access database considering you can port the backend and maintain the same interface with minimal redevelopment.

My question is are you sure that you have outgrown the MS Access solution? It may be more economical to examine mechanisms to restore the trouble free solution you had previously than redevelop or migrate your systems.

Related Discussions

Related Forums