Solved

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

Posted on 2010-09-15
26
298 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
  • 10
  • 9
  • 5
  • +1
26 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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
 

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 59

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 59

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 59

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 59

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
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.

 

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 59

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 59

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 59

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 59

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 59

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 59

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

20 Experts available now in Live!

Get 1:1 Help Now