MS SQL Express Resources

Posted on 2013-06-01
Last Modified: 2013-06-02
I'm looking to migrate a bunch of Access (.accdb) back ends up to MS SQL Express. I am sure there is some book out there that includes a disk with the basic MS SQL Express software including whatever would stand in for all of the other database tools I am used to having in Access - the ability to view the tables, create queries or "stored proceedures" yada, yada, yada.

I tried to download this stuff from the Microsoft site and stuck with the 2008 version as I am not sure all of my client PCs are capable of handling 2012. But I ended up with a bunch of stuff that did not make much sense. I'd like something vanilla that I can work through at a comfortable pace.

Any suggestions will be appreciated.

Question by:Buck_Beasom
  • 3
  • 2
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39213792
SSMS SQL Server Management Studio is the tool provided by Microsoft.  But SQL Server is not nearly as friendly as Access.  It doesn't "just work", it has to be set up, privileges granted, and access setup in both the server and the SQL Native Client.

On the other hand, it is a server and is much more powerful and multi-user than Access even can be.  You will probably only need one server that you can connect the Access front-ends to.  SQL Native Client will be needed on all clients in order to communicate with the server.  That way the users will still see Access and only you will have to deal with the server.

Author Comment

ID: 39214776
Thank you for the reply. I went to the site and downloaded the application, twice, actually, as I am on Windows 7 and had to go get the service pack.

Here is my issue. (And you already have the 500 points, so don't worry.) I need to learn how to use this thing with basic functionality on my own time and on my own computer. So I need to setup something where I can mimic a Client/Server architecture locally.

My needs are relatively simple: Forms with a maybe a dozen basic controls, the ability to pull record sets and create database and Excel objects and manipulate them. That's about it. My problem is that the amount of data I am handling is so large that I need something with more horsepower than the Access .accdb format provides.

I once taught myself VB 2010 with a book, a disk and a few weeks getting questions answered from EE Experts. Maybe I am naive, but I'd like to think I can get there with at least the Express version of MS SQL. Any resources you can direct me to will be appreciated. I'll accept your next answer for full points.

Thanks again.
LVL 83

Accepted Solution

Dave Baldwin earned 500 total points
ID: 39214790
The Express version is what is used and/or provided with Visual Studio Express.   Between VS2012 and SSMS, you can get it done.  But a lot of things you learned in Access will have to be relearned, at least in the details, with SQL Server.  While they both are database programs, they are quite different in how they go about things.  Access is a desktop Office program mostly intended for a single user.  SQL Server is for multi-user thru front-ends from Access or another program, either like SSMS or something that you write.
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Closing Comment

ID: 39214970
Thank you for your kind responses. I've been designing multi-user Access and VB 2010 applications to talk to back end .accdb or .mdb databases for most of the last 10 years, but so far my attempts to pull down the MS SQL Express system (2008 version) have been a frustration clinic. I go through all of the processes and everything comes back "success" but when I actually try to launch I get all kinds of errors.

I can keep a tourniquet around things with regular .accdb back end files for a while. Meanwhile, I am going to take a run at MYSQL as an alternative. I would prefer to use a Microsoft product - as linking a Microsoft Front End with a Microsoft Back End makes the most intuitive sense as opposed to using an ODBC link. But I don't have the time, resources or support network to get into something that has "multiple registry hives" and all that stuff. My user population is never going to go above about a dozen. I just need something that can handle, say, 5 GB rather than 1.5 GB on the database end. I don't have the resources to have a dedicated Database Administrator, Object Librarian or all of that stuff.

Anyway, thanks for your insights. If you care to communicate off line, feel free to contact me at buckbeasom at aol. You seem to know your stuff. I'm just an ex CFO, middle school teacher, author and a bunch of other stuff who has learned how to survive in resource-poor environments.

Thanks again!
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39214991
SQL Server Express appears to be limited to 4GB (per database?), regular SQL Server may be 10GB.  I don't know what you mean by "multiple registry hives".  I have SQL Server Express running on 4 computers here, 3 copies of SQL Express 2005 and 1 of SQL Express 2008, and I never had to deal with anything like that.  I use ASP and PHP to talk to them.  I can even connect to them from Linux though that is an old and somewhat crippled interface.

I find MySQL slightly easier than MS SQL but they are essentially the same.  MS SQL requires more detail to get it running but both are going to require you to set up users and user privileges to connect and access the databases.  There is no generic desktop access to them at any time.  All the SQL servers I know of are like that.

If you have further questions, please ask them here on Experts Exchange.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39215120
SQL Server Express appears to be limited to 4GB (per database?), regular SQL Server may be 10GB.
The maximum size for a database was increased from 4GB to 10GB with SQL Server 2008-R2 Express Edition.

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

770 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