SQL vs. Access/ Cost of Conversion

Posted on 2007-10-12
Medium Priority
Last Modified: 2010-04-15
This question has been  asked before however could you explain the difference between SQL and Access?  My background is in Networking.  I know SQL is better but I need to convince upper management.  We are a HIPPA compliance organization.

Also, can you convert an Access Database to SQL.  We are a  non profit organization and really like the set up of an Access Database?  Besides the cost of SQL how much would it cost to convert an empty database  from Access to SQL?  What kind of hardware would you suggest for 30 simotaneous connections and a 2GB database?
Question by:lynndaly2000
LVL 16

Accepted Solution

SQL_SERVER_DBA earned 400 total points
ID: 20070120
How is it your HIPPA compliant, are you refering to your Access mdb? I would definetly suggest getting something more secure like sql sever 2005, implement roles, schemas, etc...

move on up
LVL 14

Assisted Solution

steveberzins earned 400 total points
ID: 20070132
Access is a desktop database, SQL server is a server database.

There is a free version of SQL server available, but it won't support 30 simultaneous connections.

If, you are thinking because you may have 30 users using your app simultaneously, it is highly unlikely, given a properly designed data access architecture, that you would really have 30 connections open at one time.

There are tools available to do the upgrade from access to SQL server.

And, for hardware, it depends on more than connections and database size, it depends on what you're doing with it, how complex and large are the SQL statements that might be running against it, how much data, how many rows/columns might you be dealing with per statement, are there a large number of complex calculations going on...

If it were me, and I didn't know what the app was going to look like, perform like, etc. and this will be a standalone database server, I'd look at the recommended hardware requirements for SQL Server, buy a box that meets that, and make sure that it has a little room to grow if you find out it needs more horsepower, especially related to memory, if you can have more memory than the size of your entire dataset, it is pretty unlikely any decently modern CPU etc. is going to have any trouble serving your needs.

Assisted Solution

SKTRN earned 400 total points
ID: 20070135
Migrating Access to Sql is pretty straight-forward.  Below are some links:

As for hardware for that small of an implementation any basic server should work. I would put in 4GB of RAM for future expansion, plus SQL loves RAM.  If possible buy a server with a RAID controller so you have redundancy and I would put the db and logs on seperate disks (spindles) to increase performance.
LVL 16

Assisted Solution

gnoon earned 400 total points
ID: 20070261
Access 2003 supports 2GB database size and 255 concurrent user.
Best for small web and show report only (should be a bit write).

>how much would it cost to convert an empty database  from Access to SQL?
Since you have bought SQL, it free :) SQL has a module of import/export data between microsoft products easily.

If it were me, I will go with free databases e.g. MySql .. or more at http://www.freebyte.com/programming/database/
which quite gain the same performance of SQL, but need more a bit techniq knownledge.
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 20070636
<<This question has been  asked before however could you explain the difference between SQL and Access?>>

  JET is a department level database.  It's designed to be run over a LAN only.  All database processing is carried out on the client side.  The server only acts as a file sharing device.  There is no central DBMS process running things on the server.   As a result, JET lacks several features that most want; roll backs, online backups, and roll forward journaling for recovery.  For a typical read/write app, 30-40 concurrent users is about it.  JET is not a secure database and has limited capabilities for user security.

  SQL Server is server based and is true client/server.  There is one central process that controls access to the databases and processing of databases occurs on the server side (there is an expection to this with Access apps, which I will explain in a minute). It's a lot more scaleable, secure, and has a lot more features.

  So I agree with all the others that SQL Server (or some other client/server RDBMS) is the way to go.  However, while that may be true, the app is another thing.  Yes, you can take data easily and upsize to another RDBMS.  Access even has a built-in wizard for upsizing to SQL Server.

  But the application logic and design needs to be looked at.  Developing for a client/server backend requires a different mindset and approach as most of the time, your working with disconnected data.  Yes, you can move the data to SQL server (or something else) and use ODBC connections to the database and pretty much use the app as is with little effort, but to truely gain all the benefits of a client/server backend, the application will most likely need to be totaly re-written.

  For example, in a JET based query, if anything in the query is JET specific, JET will execute the query on the client side and make individual calls to the ODBC BE for each record.  What needs to be done to take full advantage of the backend is moving to pass-through queries and creation and calling of stored procedures.  By doing that, everything then executes on the server.  You also need to actually change the way you go about doing things.  For example, it's not uncommon in an Access app to bind a form directly to an entire table.  With a client/server backend, this would be a major no-no.  Instead, you'd provide some search function on the form and only fetch the data required by the user.  This would be important for an app that needs to run over a WAN.

  Though this is not overly critical to do if the number of users is small.  Moving to SQL Server would still be a smart move.  You'll gain many features and capabilities, but keep in the back of your mind that if you need to scale up or run over a WAN, the app will need to be re-written.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question