Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Switch to SQLServer from Access

Posted on 2008-10-11
8
Medium Priority
?
537 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 300 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 300 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 52

Accepted Solution

by:
Gustav Brock earned 900 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

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 52

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 52

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
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.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

564 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