Rx_
Nothing In Moderation
- Local time
- Today, 10:41
- 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:
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.
Attachments
Last edited: