Solved

Switch to SQLServer from Access

Posted on 2008-10-11
8
505 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
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 49

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
 

Author Comment

by:milkwood
ID: 22696850
Will Entity Framework work with Access/Jet?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

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 49

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 49

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

937 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

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now