Microsoft SQL Server Migration Assistant for Access Problem -- "There is nothing to process"
Hi. I am trying to migrate an MS Access 2003 database to MS SQL Server 2005, using the Microsoft SQL Server Migration Assistant for Access.
I have used the Migration Assistant successfully in the past.
Now, however, when I create a migration project, Add the database, connect to my MS SQL Server, and try to "Convert, Load, and Migrate" the MS Access 2003 database, I get an error:
"There is nothing to process."
The migration assistant will not convert, load, or migrate my database. Apparently the Assistant does not recognize or see the database?
A few months ago, jmoss111 very kindly helped me with this exact problem:
https://www.experts-exchange.com/questions/24322709/Microsoft-SQL-Server-Migration-Assistant-for-Access-Problem-There-is-nothing-to-process.html
jmoss111 fixed the migration problem, but says that he had to "jump through hoops to do it including removing and creating indexes; copying, deleting, and renaming tables, and dropping sub datasheets".
Can someone tell me what I am doing wrong? Why does my Access 2003 database require all of these modifications before it will cooperate with the Microsoft SQL Server Migration Assistant for Access?
I do not see any subdatasheets in this database. There are six tables, unrelated. It's a very simple database. Six tables; each has data; but nothing complicated.
I'm really grateful for any help or ideas. I need to migrate this data ASAP.
Thanks so much.
Eric
I have used the Migration Assistant successfully in the past.
Now, however, when I create a migration project, Add the database, connect to my MS SQL Server, and try to "Convert, Load, and Migrate" the MS Access 2003 database, I get an error:
"There is nothing to process."
The migration assistant will not convert, load, or migrate my database. Apparently the Assistant does not recognize or see the database?
A few months ago, jmoss111 very kindly helped me with this exact problem:
https://www.experts-exchange.com/questions/24322709/Microsoft-SQL-Server-Migration-Assistant-for-Access-Problem-There-is-nothing-to-process.html
jmoss111 fixed the migration problem, but says that he had to "jump through hoops to do it including removing and creating indexes; copying, deleting, and renaming tables, and dropping sub datasheets".
Can someone tell me what I am doing wrong? Why does my Access 2003 database require all of these modifications before it will cooperate with the Microsoft SQL Server Migration Assistant for Access?
I do not see any subdatasheets in this database. There are six tables, unrelated. It's a very simple database. Six tables; each has data; but nothing complicated.
I'm really grateful for any help or ideas. I need to migrate this data ASAP.
Thanks so much.
Eric
ASKER
Hi, karlt03,
Thanks for your note. I was considering the SQL Server Import and Export Wizard ... however, I do not have that Wizard available to me in my MS SQL Server Management Studio Express interface.
Do you know of any way to enable the SQL Server Import and Export Wizard in MS SQL Server Management Studio Express?
I am researching this....
The MS Access 2003 database is not password protected.
Thanks again for your help.
Eric
Thanks for your note. I was considering the SQL Server Import and Export Wizard ... however, I do not have that Wizard available to me in my MS SQL Server Management Studio Express interface.
Do you know of any way to enable the SQL Server Import and Export Wizard in MS SQL Server Management Studio Express?
I am researching this....
The MS Access 2003 database is not password protected.
Thanks again for your help.
Eric
ASKER
It's a weird problem.
I found this:
http://msdn.microsoft.com/en-us/library/ms140052(SQL.90).aspx
>>>>In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data.
However, when I right click my database and point to Tasks, I do not have an option to import or export data.
Have you ever run into this?
I found this:
http://msdn.microsoft.com/en-us/library/ms140052(SQL.90).aspx
>>>>In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data.
However, when I right click my database and point to Tasks, I do not have an option to import or export data.
Have you ever run into this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Tested it on Access 2003, and it works fine :) You might want to make sure that your DSN's default database is the database you want to copy your table to. Otherwise it might export your access table to the master database or something crazy.
ASKER
Got it. Working on this. Interesting.
ASKER
Karl,
I see where you are going with this. It looks like it could work. And I was able to connect MS Access 2003 to my SQL Server 2005 using the Export wizard, and creating a new DSN that points at my database in my SQL Server. That's all good.
However, which I click Finish to complete the export process, I get an ODBC error: MS SQL Server reports, correctly, that the table I am trying to export exists already.
Will the Export wizard not overwrite the existing table? Or should I delete the existing table in the MS SQL Server database?
Thanks again for your help.
Eric
I see where you are going with this. It looks like it could work. And I was able to connect MS Access 2003 to my SQL Server 2005 using the Export wizard, and creating a new DSN that points at my database in my SQL Server. That's all good.
However, which I click Finish to complete the export process, I get an ODBC error: MS SQL Server reports, correctly, that the table I am trying to export exists already.
Will the Export wizard not overwrite the existing table? Or should I delete the existing table in the MS SQL Server database?
Thanks again for your help.
Eric
Eric,
Yeah the thing I noticed with Access 2003 is that it doesnt say whether the export you've done was successful or not. Access 2007 does a better job at this. But yeah, I think with Access 2003, once you've selected the ODBC, that's it. Job done. The table is already created, and the data is imported. BUT it doesnt notify you of this.
So it might pay to just try again, if you look at management studio, all the data should be there, bu if you want to confirm, then yeah, go ahead and drop that table and re-export it.
Anyway, gotta go. Good luck with it. I'll check back with you later.
Yeah the thing I noticed with Access 2003 is that it doesnt say whether the export you've done was successful or not. Access 2007 does a better job at this. But yeah, I think with Access 2003, once you've selected the ODBC, that's it. Job done. The table is already created, and the data is imported. BUT it doesnt notify you of this.
So it might pay to just try again, if you look at management studio, all the data should be there, bu if you want to confirm, then yeah, go ahead and drop that table and re-export it.
Anyway, gotta go. Good luck with it. I'll check back with you later.
ASKER
This worked like a charm. Thank you Karl! Take care.
For a simple database I would probably just create the database manually and then use the SQL Server Import and Export Wizard and then import the 6 tables. That should be the fastest, most painless way to do it. In my opinion :) skip the Migration Assistant altogether.
But if you must use it, make sure the Access database is not password protected. That might be stopping the Migration assistant from finding anything to process.
cheers and good luck