Switch to SQLServer from Access

I have written a program that has been used for some time by about 14 users. These users are scattered over a large geographic area in Western Australia and will not have Internet access. The current program uses MS Access as its database. As the size of the database is increasing and added to the fact I would like to take advantage of Entities Framework in Framework 3.5 SP1 it is possible for me to install my program along with SqlServer or something that I can use to all the current users of the program.
I have a working version of the new database on my development machine that has SQLServer 2005. None of the current users will have SQL server.

It is possible?
Can I install SQLexpress and how do I distribute the database. Each user will be using a standalone version of the database.
milkwoodAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
The Entity Framework

http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx#

is not bound to a specific database engine.

> Each user will be using a standalone version of the database.

If so, the SQL Server 2005 Compact Edition (CE) might be fine - much more light weight:

http://www.microsoft.com/sql/editions/compact/default.mspx

It is very easy to distribute with your application and to deploy.

That said, an Access/JET database can easily work even sized at many hundreds of MB in a stand-alone setup.

/gustav
0
 
Davejk88Commented:
SQL Server Express can be freely installed on each user's machine, and is fully compatible with SQL Server 2005.

To copy the database, you can export it using the Database Publishing Wizard from SQL Server Management Studio on the system you're copying from, then run the generated SQL files in SQL Server Management Studio (or using your application, if that's more appropriate for your users) on each target system.
0
 
netsysllcCommented:
To move the Access database to SQL you can use the built in upconvert wizard in Access or you can use the SQL Migration  tool for Access which is much more powerful http://www.microsoft.com/sql/solutions/migration/access/default.mspx
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
milkwoodAuthor Commented:
Will Entity Framework work with Access/Jet?
0
 
Gustav BrockCIOCommented:
Yes, with any data source that can be hooked into VS - Oracle, MySQL, etc. including XML.

/gustav
0
 
milkwoodAuthor Commented:
Perhaps I am straying from my original question but this is why I went down the path of SQLServer,

 When I try to build the entity model  
- Add new item - ADO.NET Entity DataModel - Generate from database - Choose your date connection - Data source - Connection Properties - Change Data source

it will only allow me to select from SQL Server ther isn't a choice of Access/Jet

What am I missing?

0
 
Gustav BrockCIOCommented:
Sorry, I was mixing it up with something else:

http://forums.asp.net/t/1316099.aspx

I tried myself and also only found out of the box the SQL Server version including the Compact Edition which I still think could be fine for your purpose.


Third-party items are found. Here is one - at a cost though - for MySQL:

http://www.devart.com/mysqlnet/

and SQL Anywhere:

http://www.reuters.com/article/pressRelease/idUS87501+02-Jun-2008+BW20080602

But don't expect too much for using it with Access:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1849284&SiteID=1

/gustav
0
 
Gustav BrockCIOCommented:
Just noticed this which you could find useful:

---
Live Webinar: Using Microsoft ADO.NET Entity Framework with MySQL
Tuesday, November 4, 2008 - 10:00am PST

In this new web seminar you'll learn about the new Entity Framework and how you can access these and other benefits with MySQL:

- Develop your application against SQL Server and then switch it to MySQL with zero code changes
- Optimize your database schema without requiring any code changes in your application
- Use LinQ syntax for type safety in your applications

Register for this Webinar:
  http://www.mysql.com/news-and-events/web-seminars/display-204.html 
---

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.