Solved

Switch to SQLServer from Access

Posted on 2008-10-11
8
502 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Will Entity Framework work with Access/Jet?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 49

Expert Comment

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

/gustav
0
 

Author Comment

by:milkwood
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

13 Experts available now in Live!

Get 1:1 Help Now