Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-15
26
Medium Priority
?
311 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 2000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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