Solved

Migrating / Exporting Database from MS Access 2003 to SQL 2005 Express Edition

Posted on 2010-09-15
26
309 Views
Last Modified: 2012-05-10

I am moving MS Access 2003 .mdb database to SQL 2005 Express Edition ?

How to migrate MS Access DB ( Entire Database ) to SQL 2005 Express Edition ?

Also, i want to make sure that i can write Stored Procedures in SQL 2005 Express Edition ..

I am migrating the database ... as because i am facing poor performance in MS Access ..!
0
Comment
Question by:chokka
[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
  • 10
  • 9
  • 5
  • +1
26 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 33687104
0
 

Author Comment

by:chokka
ID: 33687337
What you suggest .. Going with SQL 2005 Express will be better or 2008 will be better ?

Yes, i am migrating only Database and not Forms or Reports
0
 
LVL 75
ID: 33687355
Well, not really an SQL guy; however, I'm pretty certain that 2008 would be preferred over 2005 - now 5 years old.

mx
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:chokka
ID: 33687364
Also i can write Stored Procedures .. and also SQL Express edition is a light weight edition and be easily deployed on any computers .. Am i right ?
0
 
LVL 75
ID: 33687382
"and be easily deployed on any computers "
Not 100% sure on Express # of users.  It is limited to 4GB I believe.  I'm sure you can write Stored Procedures.

mx
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33687501
Yes, Stored Procedures are available in Express.  I would recommend SQL Server 2008.  With SQL 2008 R2 Express you get up to 10gb, there is no specific user-limit; however, Express is limited to 1 cpu and 1gb ram, so they figure you will outgrow it eventually. *smile*
0
 
LVL 75
ID: 33687519
1 GB of ram?  Are you sure?  Doesn't make sense.  Most all new computers come with 4GB ...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33687548
Yes, unfortunately. I was reminded of this the other day.  SQL Express will not take full advantage of the computer having 4GB.  Considering this is typically a solution for desktop usage, the extra memory gets used by the users other applications anyway.  Quite honestly, I run SQL Express and do a fair bit on it and don't notice the limitation to any point of frustration.  Think its limitation is solely to stop a high-use application to grow over time and the owner never having a reason to license higher editions of SQL.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33687558
Now if you developing an application using embedded or compact edition, then the limit is whatever the OS can handle but database max is reduced to 4GB.

http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
0
 
LVL 75
ID: 33687567
ok.  Interesting limitation, but I *suppose* ... it makes sense ...

mx
0
 

Author Comment

by:chokka
ID: 33687960


Whether this approach will work?

1) Using SSIS, i am planning to export MS Access DB to SQL 2005 Standard Edition Database.

2) Once SQL DB is created, i will detach it and attach with SQL 2005 Express Edition

I am doing this double work, since i am afraid SSIS is not supported in Express Edition
0
 

Author Comment

by:chokka
ID: 33688084


1) I have exported MS Access DB to SQL 2005.

2) Then i detached SQL 2005 DB

3) I copied DB to my local working folder ( .mdf and .ldf )

4) I am able to attach .mdf to SQL 2005 Express Edition

Now, i am not able to create a Table or Drop a Table or .. any activity ..
It throws an exception saying that Database is readonly
Issue.JPG
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33688121
Ensure the proper permissions are set as well as check the properties of the SQL database weren't set to READONLY in the transition.  It is likely the former issue, though, as the logins would not have transfered so you probably just need to map your login back to appropriate role on the database you attached.
0
 

Author Comment

by:chokka
ID: 33688223
Do i need missing some steps while detaching from SQL 2005 and attaching to SQL 2005 Express Edition?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33688252
Not really.  You are switching servers, so same issues/rules apply.  You can move the database, but you are not inheriting the *server* configuration.  Logins are a server level concern.  You just need to map your login to database user or role which should have copied over okay from detach/attach process.
0
 

Author Comment

by:chokka
ID: 33704801
Okay,

Now, i have SQL Express DB which is working well and able to interact with .Net App

On taking build version or deploying this application along with SQL Express on client machine(s), do i need to pass the same connection string or connection string varies from Client Machine - Machine ..?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33704892
As long as the clients have all installed the SQL Server client drivers you should be fine.  They just have to install everything in the same fashion -- If you are embedding the DB in an application you are deploying, you may also want to look at Compact Edition; however, you should be fine with SQL Express.  If it is real light weight, .NET has a library for SQLite also, but that might be a dirty word in a SQL 2005 thread so I will stop there before my SQL buddies here oust me from the club. *smile*

But seriously as IT professionals, it is good to love technologies like I love SQL Server; however, it is always prudent to know the alternatives and start your design vendor agnostic and then go hunting for the right pieces to the puzzle which it is fine to have a vendor of choice with which you do so.

Regards,
Kevin
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33704899
If you have difficulties with the .NET side, I would recommend asking a follow question on that side of the house with more code details ...
0
 

Author Comment

by:chokka
ID: 33705043
Kevin .. i just have one last question.

My .Net Application has a local Database.
Initially, i was using MS Access and then i moved to SQL Express.

Now question is when i install .Net Application on client machine, do i need to install SQL Express Installer also on every machine ..
Or just attaching a local version of DB Is fine .. ?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33705115
If I am not mistaken, you will need to install SQL Express.  The last time I did an application like that, it appeared as if you could just use the MDF/SDF on the clients, but I always found they needed to install Express (desktop edition when I last did that) or compact edition.  Therefore, unless this is drastically changed, the clients will have to one of the free versions of SQL installed.

SQLite is just a file, so in that case you would not need anything.

Again, you can verify this with the .NET geniuses.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33705818
>>Now question is when i install .Net Application on client machine, do i need to install SQL Express Installer also on every machine ..<<
It depends.  You will need to install it whereever you have the databases.  The rest of the workstations just need the client components and it would be over-kill and a waste of CPU/Memory/disk space to install SQL Server Express on all.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33705874
That is assuming a server / client application, AC.  Sounded like the application uses a local MDF and question is does that require SQL Express to attach in application and believe the answer to be Yes.  Should it be done is a different matter which you just addressed, but can you confirm attaching MDF via .NET no longer needing SQL locally installed ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33706013
>>Sounded like the application uses a local MDF and question is does that require SQL Express to attach in application and believe the answer to be Yes.<<
Right, if each one requires their own database then they will need to install SQL Server Express on all workstations.

>>Should it be done is a different matter which you just addressed, but can you confirm attaching MDF via .NET no longer needing SQL locally installed ?<<
You can attach a database from anywhere, so long as you belong to the local Administrators group.
0
 

Author Comment

by:chokka
ID: 33716614
Yes acperkins, you are right ... This should be a locally installed database.

Later we sync with all these local database to the corporate database.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33716936
>> Later we sync with all these local database to the corporate database.
Then it probably makes sense to install SQL Express or Compact Edition on each client as you will probably want to utilize the SQL Server Subscription Services having these endpoints all read from the Corporate SQL Server.
0
 

Author Comment

by:chokka
ID: 33717990


1)  Can i assume, each and every computer will have different connection string.

i.e I am working on Computer1 with connection string as follows ..

Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;

Whether this will change from Client1Computer to Client2Computer etc ..!


2) Database which i created from VS 2005 Environment is not reflecting or not able to drag from SQL 2005 Management Studio. What should be the possible reason?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

617 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