• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Need to convert Access database to SQL

We have an Access database that I want converted to SQL.  The reason is it is growing large.  We are the Society for the Prevention of Cruelty to Animals, (Non profit) amd can't afford to pay someone to do it.  I'm looking for anyone who might Volunteer to do it for us.  Also I heard rumor that there was a program that could automatically convert Access to SQL
0
J.R. Sitman
Asked:
J.R. Sitman
  • 7
  • 2
  • 2
  • +3
2 Solutions
 
Ephraim WangoyaCommented:
You can easily do this in SQL Management Studio

First create a database in SMSS

The right click on the newly created database, select Tasks->Import Data

You will get a wizard that will guide you through importing the Access dta into SQL Server

Basically you will need to select the datasource, in this MS Access, then enter the file name

Next you select the destination, which will be the database you just created, then select the tables you want transfered

it finish and you have your sql server tables created for you
0
 
J.R. SitmanAuthor Commented:
Sounds way to simple.  However, I'll try it and post later.
0
 
J.R. SitmanAuthor Commented:
I made a copy of the current backend and when I go through the wizard I got an error so I did it again and chose "test connection" and got the same error.  See attached.
sqlconvert.png
0
Technology Partners: 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!

 
Anthony PerkinsCommented:
>>Sounds way to simple. <<
You are right the conversion is simple. The fun starts later:
1. Syntax is different.
2. Data types that do not convert or are converted to "lowest common denominator" data types.
3. Lousy performance, because the app is written for MS Access not MS SQL Server.

Sorry to be the harbinger of bad news.  But it is best to be prepared, rather than frustrated and disappointed.
0
 
joeviCommented:
What version of Access are you running? It may be simpler to export all tables to SQL Server from Access. It's wizard driven as well.

However, per acperkins, the fun really begins after you get the data transferred. Some thoughts/opinions:

1) Using linked tables, you may only need to make minor modifications to any existing Access code.
2) Performance need not be 'lousy'. With qualified guidance you should experience performance that 's more than acceptable (but it'll never be optimal with an Access front end).
3) There's no real long term downside to migrating your data out of Access to SQL Server. It'll be time well spent.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might try the SQL Server Migration Assistant (SSMA: http://www.microsoft.com/sqlserver/2005/en/us/migration.aspx). It generally does a better job of moving things to the SQL platform.

That said, as acperkins indicates, you've often got a much larger task ahead of you.

As to your error: Exactly what are you doing when you receive this error?

I'd do this AFTER insuring that your upsizing succeeds, and after making a copy of the current Access database:

1) Build a "FrontEnd" application in Access. To do that, build a new, blank databse, open THAT databse, then click File - Get External Data - Import. Locate the existing database, and import all Tables to the new database.

2) Now open your current Access app and delete all tables.

3) Relink that app to the newly created SQL Server tables.
0
 
J.R. SitmanAuthor Commented:
Thanks for the updates.  I figured it was too good to be true.  

Anyway the error comes when I'm doing the import from the Access backend.  I rebooted the server last night and this morning when noone is connected, I tried again.  Same error.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried with the SSMA, linked in my earlier answer? That tends to give better results, at least for me.

0
 
Anthony PerkinsCommented:
It sounds like it cannot find the mdw file or whatever the file is called to "secure" the MS Access database.  Perhaps when you make a copy you should remove the secure part.  This should make the "upgrade" easier.
0
 
J.R. SitmanAuthor Commented:
How do I remove the mdw from the backend?
0
 
J.R. SitmanAuthor Commented:
I removed the security.  Then used the migration wizard.  Lots of errors.  So it seems obvious I won't be able to this on my own.  Any volunteers?  See attached errors.
op3.png
op2.png
op1.png
0
 
jmoss111Commented:
What version of Access is this database in?
0
 
jmoss111Commented:
Also what version is the front end in, because the backend wont be a problem?
0
 
J.R. SitmanAuthor Commented:
2003, but we use it with 2007.  It is still an MDB
0
 
J.R. SitmanAuthor Commented:
Thanks for all the help.  I used your information to do this with the assistance of a friend
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now