MS SQL Server Express on network share drive

I'm thinking about migrating a MS Access database currently used on a Network Share Drive to a MS SQL Express. How can I deploy a MS SQL Express database on a share drive when you compare it to Access we simply place the .MDB file on the network and everyone can access that database. I am new to MS SQL Express I'm just starting to learn more about this software.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SQLExpress (and it parent product SQL Server) work differently than  Access.   Access is a "flat" file database in which all of the data is a contained a single file and you access the file directly.   SQLExpress also contains your data in files, but you access the data via client/server configuration.  

An Access DB is portable (i.e. it is easily moved).

SQLExpress requires server software to run and is not "portable".  

You can connect your users easily through ODBC to the SQLExpress database, but it may require changes to the front end and your SQL queries.

Also, SQLExpress does not provide a front end for the data....   A number of folks use Access to connect to SQLExpress.

Here is some general information that may be helpful:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joeserroneAuthor Commented:
Thanks great info! so if I used MS Access as the front-end and SQL Server Express 2008 as the back-end what process would I need to perform in order to load that back-end on a Network Share Drive?
The back-end files probably could be placed a shared drive, but that would hurt performance.   Also, there is no need to do this as clients do not connect to the file directly, but rather to the server software itself.  

You would install SQLExpress on a server or workstation (with the files on that computer) and clients would connect via ODBC over TCP/IP.

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

joeserroneAuthor Commented:
ok thanks! much clearer now.... how about performance? for example we have people located in various states on the opposite side of the USA, if I place an Access Database on a NC Share Drive people in California have to wait 10 minutes before the tool actually loads on their screens, vice versa if I loaded the tool on a CA Share Drive and people in NC tried accessing it. By using MS SQL Server 2005 as my back-end can I eliminate such issue?
Oh, yeah!  Tons of difference. Both sites could have a copy of the front end since the data would be housed on the SQL server.
Anthony PerkinsCommented:
>>ok thanks! much clearer now.... how about performance? <<
It really depends.  If you continue to use the same logic as with MS Access, I suspect you will see zero difference and in fact it may actually be slower.  If on the other hand, you take advantage of SQL Server functionality then you should get better performance.  But for that you will need to have gained or acquired some experience using SQL Server.

I am not trying to throw cold water on your hopes, you just need to set your expectations based on reality.
Acperkins is correct....   You would not see much of a performance difference - except that you are not having to pull the entire database file from across the county!   If nothing else, you should not see the huge delay on startup!
joeserroneAuthor Commented:
what are you suggestions about increasing performance using MS Access as the Front-end and SQL Server Express as the Back-end? Is having the tables in SQL Server Express and keep the Queries, forms and Reports in MS Access sufficient or is there a better way you suggest?
Anthony PerkinsCommented:
Unfortunately, if your goal is improving performance, the only way you are going to see that by using MS Access as a front end and SQL Server for your back-end is by re-designing/re-writing your app to take advantage of SQL Server.  That includes not using JET in any shape way or form, but rather converting to using ADO and Stored Procedures for all access to the database.  

I realize most developers are not prepared to make that investment in time or acquiring the technical expertise in order to accomplish that.  The problem is there are many people who think naively that all they have to do is run the "Upgrade" Wizard or whatever it is called and voilá everything will be peachy.  This cannot be further from the truth:  You should not think of MS SQL Server as an upgrade from MS Access.  It is a totally different platform that may or may not suit your puposes.  Study up and make your decision carefully, because if you don't than you risk many months of frustration and disappointment.
To the extent that you can, having your queries as views or stored procedures in SQLExpress will give you the best performance.    Either way, the change will most likely require that some of your queries from Access be reconfigured as the syntax between Access and SQL is not the exactly the same.    

However, if you simply move the data to SQL, I contend that even if your performance while accessing the data is not any faster (which it might be), you will at least see an improvement in start time.

Also, don't forget about backups!   You will need a script or procedure for this.

joeserroneAuthor Commented:
Thank you Acperkins, I appreciate your input... what do you suggest is the best tool to create an efficient front-end and reporting tool with a SQL Server Express as the back-end
Anthony PerkinsCommented:
>>what do you suggest is the best tool to create an efficient front-end and reporting tool with a SQL Server Express as the back-end <<
I am biased to ASP.NET using either VB.NET or C#.
Olaf DoschkeSoftware DeveloperCommented:
What I'd suggest is having a database server both in NC and CA and make the databases replicate eg over night or all the time. Then data access is local in both places. You can't overcome the lower bandwidth of internet vs LAN via one central SQL Server.

Also you can link MS Access to an SQL database as external database, link to the sql server tables, then use internal queries just like you're used to and work with the tables, as if they're part of an access mdb. Should be easier to make that transfer than reimplementing the whole application.

The only thing making an ASP.NET application better is, that it will put both data AND the application to a central server. An ASP.NET application is hosted on an IIS (Internet Information Server) most probably run on the same physical server than the SQL Server database.

This means taking a desktop appication to an internet application, but this is a major rewrite and a lot of costs coming from an access database. Take one step after the other.

Bye, Olaf.
joeserroneAuthor Commented:
All great suggestions!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.