SQL Server Migration Assistant for Access - Access 2010 (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 16:39
Joined
Oct 22, 2009
Messages
2,803
If this needs to be posted in a different formum location, please let me know. This is not a question. It is information and a review about migrating Access 2010 into SQL Server 2008 R2.

It is well documented that the MS SQL Server 2008 R2 migration tool will not accept the ACCDB extension. While it is possible to migrate an Access 2008 back end using the Access 2010 migration tool, Access 2010 migration tool uses ODBC or other connectivity components. A back-end database i.e be_DBName - indicates a split database with a front-end (user interface) and linked tables to a back-end data only database.

The Microsoft SQL Server Migration Assistant for Access does work for Access 2010 extensions. It connectes directly to MS SQL Server 2008 R2. If the migration is being prepared and version control is important, this tool has some advantages. It is free from Microsoft at:
http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#Access
The MS verson in the attachment was updated in Jan of 2012.

While still in the preliminary testing stage. In general, all my forms worked after the upgrade to SQL Server 2008 R2. But, the date/time was converted to a type of sql server text. None of the date fields worked the same now. The textbox Date Picker no longer works at all.




Here are a few suggestions for those going through the first-time migration of Access to SQL Server:
  • When downloading the Migration tool from Microsoft - there is a no-cost license key. Set up a Microsoft ID on hotmail or other accounts in advance. After installing the Microsoft SQL Server Migration Assistant for Access tool, a window opens with a path to install the free license key that Microsoft will send by e-mail.
  • In SQL Server 2008 R2, create a user name (e.g. DataMig) for your data migration. Be sure to assign the DataMig user plenty of rights to get the migration task done.
  • Create a Database on SQL Server in advance to starting Microsoft SQL Server Migration Assistant for Access. In the attached image, the BE_2010 for Access name is matched in the SQL Be_Ver2010
  • Assign user rights (e.g. DataMig) to the database in SQL Server.
  • Know the type of security (Windows Authentication or SQL Server Authentication), the name of the server and the SQL instance. SQL Server security has a User Name and Password. Integrated or network security will require coordination with the network IT staff to insure your network ID is associate with SQL Server. For data migration, my preference is to use DataMig with a User Name and Password. This way, all records logged during migration are by the user DataMig rather than my personal network ID.
  • The log-in screen is unforgiving. All of the values must be correct and errors are somewhat general. That said, the login does not require an ODBC connection. ODBC version control in the past have sometimes taken on a life of their own.
  • Version Control - This tool allows the script to be saved. Moving data from Access to SQL Server may not be a one time event. Come up with a naming convention for migrating Tables and Queries. Often, the first test will be done on a Test SQL Server. When the final production date comes, scripts are a great way to repeat with confidence.
If I receive any thanks, interest or positive feedback, I will add additional notes about this process as I migrate databases over the next few weeks. Or this may already be too much information.
 

Attachments

  • SQL Server Migration Assistant for Access Main Screen.gif
    SQL Server Migration Assistant for Access Main Screen.gif
    40.3 KB · Views: 693
  • Login Screen for DataMig.gif
    Login Screen for DataMig.gif
    14.9 KB · Views: 482
Last edited:

jasonjason

New member
Local time
Today, 15:39
Joined
Mar 20, 2013
Messages
1
Thank you. This has been very helpful for me as I'm about to go through the same process.

I fear I'm going to have problems with Access's 'Attachment' field and form controls.
 

Rx_

Nothing In Moderation
Local time
Today, 16:39
Joined
Oct 22, 2009
Messages
2,803
I don't think you will have problems.
My experience was the data types worked great.
I used SQL Server Native Client (a download) as the ODBC driver.
Everything matched so well - can't really think of what didn't work OK.

.... was very surprised! myself.
 

Rx_

Nothing In Moderation
Local time
Today, 16:39
Joined
Oct 22, 2009
Messages
2,803
Access developer tried to install the Microsoft SQL Server Migration Assistant for Access. Step 2 requested a Microsoft License Key.
This was posted on the MS Social Media on October 29, 2013. Microsoft evidently isn't coordinating things too well. Not a surprise.

http://www.microsoft.com/en-us/download/details.aspx?id=18589

It downloads SSMA license for Access.
A second site quoted was:

https://code.google.com/p/softdesigner/source/browse/trunk/mposAAbisDesigner/mysql-ssma.license?r=32
the users claimed this license worked and market it as the answer.
 

Users who are viewing this thread

Top Bottom