Convert from Access 2000 to SQL 2005

networkn
networkn used Ask the Experts™
on
I have a CRM which uses MS Access 2000, and the developer no longer supports the DB on a MS Access Platform. I need to convert from MS Access to SQL and despite my best efforts and those of the developer we don't seem to be able to find a solution.

They have tried the upsizing wizard, but this didn't bring across the history notes for each record which is the single most important data. I have tried the server migration assistant for access 2005 and whilst all the data is there, it seems to have renamed all the keys, meaning the database cannot be written to by the front end application.

I would like to find a preferably free way to bring the data across, I am neither an expert in access or sql, but do understand the basics of databases such as tables records, primary keys etc.

Would appreciate any assistance anyone can offer.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi networkn,

Did you have a look at this link. It seems like this is what you would want -
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx
i am no expert either. the way i do it is,,

go to start -> run, type

dtswizard

and press enter. This will start the SQL Server Import and Export Wizard. Press Next on first screen.. and second screen, choose Microsoft Access for Data Source and then select your file. Press Next. On 3rd screen, select SQL Native Client as Destination, localhost as server name and select your database. Press next. I think you can fill in rest of the wizard questions..

i hope this works.. it does for me :)

Author

Commented:
manav08: that is the tool I wrote I had tried already and it had renamed the primary keys which made the front end unable to write to the database!
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
SadafRasheed:

On Windows 2003 I don't have that dtswizard what is the equivalent tool for server 2003?

I have SQL Express 2005
No idea. i googled and among other it returned the following url. may be it can help

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_23394391.html

Author

Commented:
Nope that link doesn't contain the information I require to proceed

all i can do now is that if its not private, and you want the conversion for only one time. you can attach the access db here, i can convert it and attach the sql db here..

sql server 2005 db should be compatible with sql express :s

Author

Commented:
hi, unfortunately, it is confidental and wouldn't be suitable to attach here as it's 600MB.

Thank you for the offer, if you think of anything else, please let me know.
Top Expert 2006

Commented:
<<but this didn't bring across the history notes for each record which is the single most important data.>>

Is this literally data? Like a Memo field? Can you elaborate?

<<I have tried the server migration assistant for access 2005 and whilst all the data is there, it seems to have renamed all the keys>>
As part of the Migration options, you will need to allow identity inserts for the tables. I've never seen SQL rename fields, did it simply create new ID fields in your tables and set them up as indexes? If so, that's a configuration option that you can turn off.

I use the Access upsizing wizard so that I can bring data over along with schema. This allows the ID fields to come over in tact. It may be that you have to bring over all but the Notes fields, then create these in the table manually on SQL and run an update query to move the data over.

SQL express does pose some limitations, however I don't believe it limits Schema creation or editing.

How far did you get with the built in Access Upsizer? Could you move over any tables?

J

Author

Commented:
>> Is this literally data? Like a Memo field? Can you elaborate?

Hi, sorry to sound dense, but I am not sure how in SQL I can give you the information you are looking for? If you can give me a pointer in the right direction I can try for you.
 
>>As part of the Migration options, you will need to allow identity inserts for the tables. I've never seen >>SQL rename fields, did it simply create new ID fields in your tables and set them up as indexes? If so, >>that's a configuration option that you can turn off.

Could you tell me where exactly I would do this, so I can try and see if that resolves the problem?

>>I use the Access upsizing wizard so that I can bring data over along with schema. This allows the ID >>fields to come over in tact. It may be that you have to bring over all but the Notes fields, then create >>these in the table manually on SQL and run an update query to move the data over.

I would need some help doing this, as right now I am not sure how this would work or how to do it. My knowledge of the inner workings is limited.

I think the developer of the software tried using the access upsizer and all tables and data were moved, but I think he was talking about views not being copied, or linked or something along those lines? I thnk the upshot of it was that the data was there, but not visible to the front end application. I think it was the history notes which is a seperate table, and these are linked to each contact. It's one of the three most important tables other than the phone numbers and addresses.
Top Expert 2006
Commented:
Upsizing an application isn't as easy as just moving tables over. As you've already found, Queries don't import. You'll have to re-write the ones that have IIF statements in them, or the ones that perform a function call. Straight SQL queries can be pasted into a new query without any issues, however we really don't have a good sense yet of the scope of your requirements. There is linking that has to be addressed as well plus procedures and functions to write to support your app.

I would consider searching around in your area to try to find someone that has done this and hire him/her for a couple of weeks. That way you can look over shoulders and learn as the work is getting done.

Author

Commented:
Surely there must be applications which do this without the need for hiring someone for weeks to convert a 500MB Database?
Top Expert 2012

Commented:
>>Surely there must be applications which do this without the need for hiring someone for weeks to convert a 500MB Database?<<
If all you are talking about is data, that may be true.  However, somehow I suspect you are also talking about your MS Access queries, if that is the case, then you are simply out of luck. As, in my very biased opinion, you should be.  There is no software in the world that is going to convert your MS Access code to **reliable/scalable/performant** T-SQL code.  It just does not exist.  Why is that you say?  Well, unless this is a trivial app, you will not see any performance/maintenance/security improvements unless you are prepared to redesign and re-write from scratch in order to take advantage of MS SQL Server.  Sure you can definitely learn on the job (as most of us have done in the past) by "upgrading" the code, just don't expect miracles. It ain't going to happen.  Did I tell you I was biased?

Author

Commented:
Well I don't need any performance increase, the ONLY reason we are moving to SQL is the company who makes the software will no longer upgrade ms access databases, performance on MS Access for 10 users was ok, so SQL will be better than that I suspect just based on the fact not so much data is going down the wire all the time.

Earlier versions of this application (point release) were able to be converted in the past, but the developer who did it for the company has left, and I think they are struggling.

What is a "view" that they are referring to that isn't coming across?
Top Expert 2012
Commented:
MS SQL Server VIEWs can losely be compared to MS Access Queries.
Top Expert 2006

Commented:
If your company refuses to upgrade MS Access, then what are you going to use as a Front end for your app anyway?
If you're not out for performance improvement, then just upgrade to a supported MS Access version for your OS. The converstion from Access to Access will be a lot easier to manage than from Access to SQL, Especially SQL Express. If your company refuses to pay for a conversion, and they are not likely to pay for an Access upgrade, seems safe to say that they may not need this data as badly as they thought.

I don't want to take anything away from your skills, because given time, you CAN learn SQL and how to use it, but optimizing queries and building stored procedures, views, designing tables and tying all of this together underneath an old Access front end is not a good business plan.

There are a lot of good SQL books out there. The SQL guys on this site (EE) are awesome as well. Some of the best in the world. You could piece together enough knowledge to keep you moving forward with no problem. But I really do feel you would benefit from having someone experienced sitting with you to show you the ins and outs. If you are going to have to support this once it is done, then the experience you gain from the pros will get you further and in less time that it would take to work your way to the finish on your own.

We can answer questions here...and we can guide you, but each situation warrants its own question in accordance to the rules of this site.

J

Author

Commented:
Ok well we are an IT consultant, this application is supplied by a third party to a customer of ours. The software vendor have dropped support for their application on MS Access and THEY are the ones who need to provide the updates to things like schema, which they won't be doing for MS Access. It's nothing to do with me, except that they are having trouble converting from MS Access to the supported SQL Platform, hence I thought I would get some additional information. In the past when we have converted this application, it's gone fine using a tool they provided us specifically to handle it, but since then some of the remaining MS Access clients have updated to later version of this application which in turn updated MS Access and now the tool doesn't work, and the person who wrote the tool for the third party has left.

Once the application is converted, then the support will be with the software vendor.
Commented:
Ok well I have solved this myself. Basically it involved downloading :

MS Access 2003 SP3
SQLServer2005_SSMSEE.msi
SQLEXPR_TOOLKIT.EXE (For the DTSWizard (Must install Business Intelligence Development Studio Component to get the dtswizard)

I created a new DB in SQL Express and then opened the DTSWizard, I converted ONLY The tables from the first screen, the views are at the bottom and the wizard converts them to tables which isn't what we want. So as source I selected MS Access, Destination Native SQL. After that was done, I used the SSMSEE to move the views, and was told you can safely ignore it telling you that it didn't convert anything because there was nothing to migrate. That was it, now it's working just fine.
Top Expert 2012

Commented:
Moderator,

>>Solved it myself. Refund of points requested. <<
So in essence they are saying is that they reached their solution without any help or advice from experts here.  I would argue that is not the case:

They opened this question a week ago and many valuable comments were made.  Then they requested help from Community Support and had more advice and yet still think that no one helped them.

I would urge that they need to re-evaluate this thread impartially. I suspect they will find that their original question was more then adequately addressed and at the very least a split is in order.  Further, their additional questions while not totally on topic were also addressed.

Anthony

Author

Commented:
I am wondering which of the posts acperkins believes directly assisted in the solution I requested or posted subsequently? Few of the posts contained technical information, most of the posts told me I couldn't do it or I would have to hire someone to do it manually. I re-read all the posts and still don't see any posts that led me anywhere near the solution I ended up getting. I don't mind handing out the points, I don't pay extra for them, but only if I get what I asked for which in this case I didn't. I don't dispute information was given to me, however none of that information was what I asked for. I thought I already wrote this last night but today the post is missing! Hence I have reposted it.
Top Expert 2012

Commented:
Let's set the record straight first.  Your original question had to do with data migration  You had used the upsizing wizard but wanted "to find a preferably free way to bring the data across").  What then later ensued was discussion as to whether non-data (queries) objects could be converted over, jefftwilley and later I maintained that this could not be done with any tool to produce reliable T-SQL code.  jefftwilley then took it a step further and suggested that you hire a professional.  But this was to address converting the code and not the original question of migrating data.

Now having got that out of the way, let's focus on the original question:  Data Migration from MS Access to MS SQL Server.  No one said it could not be done, of course it can.  We help users on this site every day who are converting over their data from MS Access to MS SQL Server.  Just check for yourself.

You had stated that the upsizing wizard seemed "to have renamed all the keys" this is clearly not the case.
manav08Date suggested (http:#a24888763) that you use SQL Server Migration Assistant for Access
SadafRasheed recommended (http:#a24888782) the SQL Server Import/Export Wizard (which you subsequently ended up using) and once again you claimed it renamed all the keys.
You only then told us that you had SQL Server Express which although it does not come with SSIS, does offer the Import/Export wizard. This was explained in the link manav08Date posted (http:#a24888810)
SadafRasheed even offered to do it for you, which although maybe unrealistic, at the very least contradicts your statement that we said it could not be done.
jefftwilley explained (http:#a24889950) how you had to enable IDENTITY Inserts and questioned that it would rename columns.  And once again confirmed that it was quite straight forward to convert data over.
It was at this point that you went off on a tangent discussing converting over non-data elements.  
jefftwilley explained to you (http:a24897261) how this was a no-go.  But he was responding to your new question and not to your original question regarding data elements.
But you insisted ("Surely there must be applications which do this without the need for hiring someone for weeks to convert a 500MB Database?".  I merely confirmed what jefftwilley had already said.
You then asked what a VIEW was and I answered.  Again, this is a code question and unrelated to your original question.

You finally tried the Import/Export Wizard and confirmed what we had been telling you all along: "the views are at the bottom and the wizard converts them to tables which isn't what we want ... I used the SSMSEE to move the views, and was told you can safely ignore it telling you that it didn't convert anything because there was nothing to migrate. "

Again, I would urge you to **impartially** reread all the comments and then ask yourself if you honestly believe that the experts here made no contribution to you reaching a solution.

If you feel now that you should award some points, please do not include me, I did not address the original question.

Thanks,
P.S. Most people here call me by my name: Anthony

Author

Commented:
I'm sorry I have reread this many times and I just don't agree with your assessment of how this worked out. Your inferring I am not impartial but I can assure you I have no reason to withhold points without merit, as they cost me nothing (a fault with the site in my opinion), and I have always been generous handing out points where they were due. No-one else has voiced an objection to the way I want to close it, and you aren't even wanting the points!

"If all you are talking about is data, that may be true.  However, somehow I suspect you are also talking about your MS Access queries, if that is the case, then you are simply out of luck".  << This is where YOU told me I couldn't do what I wanted.

What I originally asked for, was a preferably free way to do what I in the end did. Ok I didn't do it with one tool, but both were free as per my original request. I expected that someone would have said.. USE X tool and following Y procedure to achieve what you want.  If that had occurred then I would gladly given points.

By your own admission you have said that both you and JW said that converting views/queries wouldn't work and I would need to recreate them, which isn't actually correct in this instance. I used one tool to bring across the views and the other tool to bring across the tables. Admittedly my ignorance lead me to believe the keys had changed names when this wasn't the case, but no-one provided me with the information that led me to the solution I ended up using. I am sorry we disagree but I also had a colleague of mine read this thread and he agreed with me.

Again it's not about not giving points, its about the fact my original question was not answered in a way to lead me to a solution in the manner (or close to) I asked for it.

I don't wish to argue this any further, if anyone else has someone to add, lets hear it, otherwise the moderators can decide.
Top Expert 2012

Commented:
>>No-one else has voiced an objection to the way I want to close it, and you aren't even wanting the points!<<
What is your point?

>>This is where YOU told me I couldn't do what I wanted.<<
You obviously did not read what I wrote.  I never stated you could not do what you asked in your original question. Period. Full stop.  Punto y aparte.

>>USE X tool and following Y procedure to achieve what you want.<<
They did, you were not listening.  Again see my links.

>>By your own admission you have said that both you and JW said that converting views/queries wouldn't work and I would need to recreate them, which isn't actually correct in this instance.<<
Again, let's not get distracted from your original question.  We are talking about data migration not code.

>>Again it's not about not giving points, <<
Be serious, of course it is.

>>otherwise the moderators can decide.<<
And that is the only point I agree with.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial