Solved

Switch to SQLServer from Access

Posted on 2008-10-11
8
518 Views
Last Modified: 2013-11-08
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.
0
Comment
Question by:milkwood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 2

Assisted Solution

by:Davejk88
Davejk88 earned 100 total points
ID: 22696189
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
 

Assisted Solution

by:netsysllc
netsysllc earned 100 total points
ID: 22696385
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 300 total points
ID: 22696826
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:milkwood
ID: 22696850
Will Entity Framework work with Access/Jet?
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 22696870
Yes, with any data source that can be hooked into VS - Oracle, MySQL, etc. including XML.

/gustav
0
 

Author Comment

by:milkwood
ID: 22696957
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 22697020
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 22728541
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 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