Access vs. Sql Server Express

Question

What would be the reasons for choosing (or not choosing) SSEE over say Microsoft Access in a Desktop application?

Answer 1

Hello,

 

Interesting and wide question

 

- Microsoft ( since 2000 if my memory is good ) announced the death of Access ( but Access 2007 exists )

- Access is mainly a database engine which is not done for remote access or multiple concurrent connections

- SSEE is free . It's not the case for Access

-  i've used a Sql Server Express database with a data file of 3.8 GB. It's not the case of Access

- the integration Excel-SSEE is very good ( with Sql Server 6.5 and 7.0, it was a plea )

- backup/restore are not easy ( except you may have difficulties to backup directly towards a CD/DVD )

- you have a partial replication for SSEE ( as subscripter not replicated )

- with the toolkit, you have access to Businness Intelligence

- if you need more features you may "upgrade" to Workgroup/Standard/Entreprise Edition

- the integration with Visual Studio is very good

- the SSE is robust . I've never seen a SSE database corrupted and useless, it's not the same for ACCESS

 

I'm sure that great specialists will find other avantages

 

If you want other explanations, don't hesitate to repost

 

Have a nice day

Answer 2

Thanks Papy,

I am not that privy with SSEE that is what prompted the question.

 

My experience is that creating tables and a schema can literally take seconds using Microsoft Access, and it seems a bit more labor intensive (might just be my ignorance) using SSEE.

 

• Can you do remote connections with SSEE or have concurrency? I thought you could not unless you got the full blown version. 
• You mention SSEE is free, however if I distribute a program that interfaces with Access, that is free. (Granted if the customer wants to update the database manually, they have to have Access).
• I would have no need to integrate Excel, so that isn't really a factor.
• What do you mean the integration with VS is good? Can you elaborate? Is there something you can do with SSEE that i cannot do with Access with VS?
• Opening SSEE is slow, while Access opens in a second. I like the GUI in Access, while it seems (again perhaps my ignorance) that eveything is much slower. Opening tables, slower, creating tables, slower. Using the tool in general, slower.

I suppose I will get privy with it because folks such as yourself recommend it, I just don't see for what I am currently doing where it is any better. (Except concurrency, but does SSEE have that?)

 

 

Answer 3

Hello,

 

 

First, as english is not my mother tongue, i don't understand "privy". Please, could you explain me ? Thank you

 

An advice : download Sql Server Express 2005 with advanced services

http://www.microsoft.com/downloads/details.aspx?FamilyID=5b5528b9-13e1-4db9-a3fc-82116d598c3d&DisplayLang=en

the GUI of Sql Server Management Server Express Edition ( SSMEE ) is similar to Access with more possibilities ( backup/restore, total control of the access that an user may have on databases,tables...)

 

SSEE is free and you may distribute it without no problem

 

I don't think that Access has stored procedures or triggers

 

You said :

"Opening SSEE is slow, while Access opens in a second. I like the GUI in Access, while it seems (again perhaps my ignorance) that eveything is much slower. Opening tables, slower, creating tables, slower. Using the tool in general, slower" 

I've used Access around 1999, compared to Sql Server 6.5, it was heavy,heavy,slow

 

Remote connections : yes with no problem

 

IDon't forget, that Microsoft has announced the death of Access and 2007 is the last version . It may be now the best time ( and the last chance ) to change Access to Sql Server

 

"I suppose I will get privy with it because folks such as yourself recommend it, I just don't see for what I am currently doing where it is any better. (Except concurrency, but does SSEE have that?)"

 

I don't think you are really interested by new technologies

 

Well, excuse me, it's 02:27 ( time to sleep )

 

Have a nice day

 

 

 

 

Answer 4

I am interested in new technologies. But I am not interested in something just because it is "new". I am curious by nature and want to know my benefits and why I should learn it. You named a few good ones, so please don't misrepresent me.

 

I am here in a forum asking the question, seeking the why. That should intimate that I am interested. So, I hope that is clear. I don't appreciate being told I am not interested in new technologies (I won't hold that against you, I think it may just be the language barrier)

 

Privy is in any English dictionary:

http://dictionary.reference.com/browse/privy

 

Answer 5

Hi John,

 

First off - Pappy must have mis-heard that Access 2007 is the last verson, that is not correct. I was over talking to the Access team a couple weeks ago - they're still going strong and doing great work.

 

In considering the choice between Access and SQL Server it's important to understand a few basics first...

• Access is not a database, it is a development environment for creating data centric applications. Access is tightly bound to an embedded database, either Jet (Access 2003 and earlier) or ACE (starting with Access 2007). The forms and reports are not database technologies, they are development tools, as such, Access is more appropriately compared to Visual Studio, which is also a development environment.
• SQL Server is primarily a core database engine - it doesn't have a development environment wrapped around it. The common usage for SQL Server is to put it on a big server and have it server out data to thousands of users. It's very good at that. To compare apples to apples, you need to compare Jet to SQL and not focus on the development environment.
• You can use many different development environments to develop applications for SQL Server, including Access. Access can work directly against a SQL Server using Access projects or by creating Linked Tables within a normal Access database applciation.

So let's focus on the database engine and ask the question, why would I use SQL Server over Jet/ACE. (Since we know now that you can actually develop SQL Server applicatioins using Access as a front end.) There are three primary reasons that you would move from Jet/ACE to SQL Server:

1. Scale - SQL Server can hold more data and will handle far more concurent users that Jet/ACE can. Exactly how many will depend on the workload and edition of SQL Server you use. SQL Express specifically has limitations in memory usage, CPU usage and database file size - but it's 100% compatible with the larger editions of SQL Server, so you can easily start small with the free product, and when your application out grows SQL Express, you simply move your database to a larger edition such as SQL Workgroup or Standard and everything else stays exactly the same.
2. Stability/Reliability - SQL Server is a service based database engine that is designed for multi-user access. This is important because all users are connected to the same database engine service that mediates access to the data, thus helping to keep the database healthy. Because all connections come to the server, and the server controls access to the data, there is less likely to be issues with access conflicts and corruption. SQL Server also has the ability to perform maintenance of the database automatically on a schedule because it runs as a Windows service. Jet/ACE is a file based engine and each user connects directly to the file using a private copy of the database engine running on their local machine. Jet/ACE actually does a great job of managing multi-user access to the same file, this access method is more prone to contention issues and is not very resilient to network problems because all data changes are made on local computers and sent across the network to be stored in the central file. This is a characteristic of all file based database engines, not just Jet/ACE.
3. Security - For multi-user database SQL Server offers a higher level of security that Jet/ACE do. In addition having layers of security that separate connection to the server from actual access to the data, SQL Server also offers the ability to integrate with the existing Windows security model so that you're existing Windows user account can also be used to grant you access to the SQL Server. You can not so this with Jet/ACE. SQL Server also gives you finer grain control over the security settings that Jet/ACE do.

There are many thousands of applications that work just fine wth Access/Jet/ACE and you should evaluate what you're application is doing and whether you've reached a point where one of the points above has expanded to the point where you would consider moving to SQL. I won't kid you, SQL Server is more work that Access/Jet/ACE. The Access team has done an amazing job of hiding the "database goo" from you so you don't have to think about it. SQL Server doesn't hide the database goo on purpose - you get a lot of power but the trade-off is some complexity. One thing to be aware of - SQL and Jet work in completely different ways, and as a result, applications need to be written differently for each engine.

 

One of the most dangerous myths out there is that if you move your data to SQL Server your application will be faster. This is often not the case because the data access model used by most Access based applications do not take advantage of the core benefits of SQL Server. Moving any Access application from Jet/ACE to SQL almost always entails some re-writing of the application, sometime significant re-writing. There are many papers and web sites that document ways to make Access applciations work well against SQL Server, you can find them using your favorite search engine.

 

Hope this helps,

Mike

 

Answer 6

Thank you for the in-depth answer.

 

Answer 7

Hi Mike,

 

Thanks for the great answer on Access vs. Sql Server Express. I've used Access a lot and wondered about SQL Express and Compact Edition and how they compared. Most of the developent work I do is for small businesses that really don't need to get into full-scale SQL Server.

 

I do have one question about SQL Express limitations that you mentioned - 1 cpu and 1 GB RAM. Does this mean that the computer it's running on can only have these as a maximum or that SQL Express will simply use the limited resources even if there are multiple cpu's and more than 1 GB RAM?

 

Thanks in advance!

 

Ben

Answer 8

One last question, along with Ben's good question. Is distributing a desktop application just as easy with SSEE then with Access?

 

Thanks in advance

 

Answer 9

Hi Ben,

 

SQL Express will simply use what it can and ignore everything else - there are no problems if you have more memory or CPUs.

 

Mike

 

Answer 10

Hello,

 

For Mike Wachal,

 

Sorry for my errors about the differences between Access and Sql Server.

 

I've given up Access when MSDE 2000 appeared. The first version was not fine. The 2nd one was better and in comparaison, ACCESS was less interesting.

 

With the arrival of Sql Server Express, the deal changed.Especially, at this time, some persons called the" Access killer" and said that Access will be surrendered by Microsoft. Afterwards,I've not followed this story.

I suppose that if the Access Team is preparing a new version , that's they have faith to their product. They are courageous , they are right and i say them Good luck and good success. And for you, thanks for this information.

 

I think that Sql Server Express is unable to use more than 1 GB of memory ,

When i'm using SSMSE, right-click on an instance,properties,memory. It's possible to define the minimum memory and maximum memory which may be used by the instance ( by default the max value is 500KB).

I think with 2 GB of total memory on the computer ( for XP Pro and 3 GB for Vista i think ), it's possible to do many things

( maybe the max memory of 500 MB should be modified )

 

I appreciate all your posts because you have the way to be clear and precise.

 

For the_grove_man

 

Sorry if i vexed you and i would appreciate if you accept my apologizes.

When i sent my last post, it was after the big blockade of the forums( 2 up to 3 hours ). It seems that was not for everybody. But from this time, to try to send a post is a plea ( for a send post , i'm logged out 3 times and at 2:00 AM i think i was tired and very angry, not concerning you but the forums as i'd to type the post three times before it can be sent.

 

In any case, i have not found privy in my dictionnary and i was fearing an ironical meaning ( now i've dictionary.com is in my favorites thanks)

 

Have a nice day

 

 

 

Answer 11

Thank you for that Papy, and thank you all your good replies.

 

Answer 12

I disagree with the above post on one point Access IS a database.  It also provides app development capabilities, but it is a database nonetheless.  Access is well suited for single user applicatins and multiuser applications that dont require stored procedures.  I still use Access for prototying my databases because it is easier for my users to undertand what they are seeing.  This helps me make sure that the final product meets my user's needs.  When I'm done then I upsize it to SQL Server using the upsize wizard.