trying to upsize MSACCESS 2010 to sql server 2008
-
Using sql server migration assistant 7.3 to try to upsize an MSACCESS database. When I select the MSACCESS accdb files in SSMA the access tables and queries do not display. When I run the actual application everything appears to be in order.
Here is the error I receive:
I use a windows 7 64 bit machine. does anyone have a clue?When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
running ssma IN 32 BIT mode helped. Now this tables display when I select the MSACCESS database file.
But when I try to connect to sql server, I get this error:
Thanks for any advice.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
That error message indicates that you are not using the server_name/instance_ name correctly.
I use SSMS to test to see if I have the correct instance name and permissions.
Have you installed SSMS? Can you connect to the SQL Server instance with SSMS?
-
Are you expecting queries, forms and reports to be upsized to SQL Server? The only thing that the SSMA deals with are tables that are stored natively in the Access database. The typical solution is to create an ODBC data source that is then used to link to the SQL Server tables using the Linked Table Manager. When and How to Upsize Microsoft Access Databases to SQL Server is an excellent, if somewhat dated, look at the pros and cons of what you are attempting. I should note that the Access database may require some redesign of queries, and the data sources for reports and forms in order to get maximum benefit and performance.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
Question: Why upgrade to an eight-year old version that's out of support? You can't even buy licenses for SQL 2008 any longer, you'd need to buy 2016 licenses with downgrade rights.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
true abut SQL2008. This is a test environment. Still cannot connect to SQl2008 sql server using ssma.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
I've also tried importing the access data into a sql server 2014 server. The import function did not recognize the accdb file. I'm about ready to jump.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
Have you created an ODBC data source to the SQL Server database? That is the only way to connect an Access database in the .accdb format to tables in SQL Server. We've used this approach for 20 years, and quite successfully,
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! - fizzleme - Wednesday, March 22, 2017 4:49 PM
If the PC does not have Access installed then you have to install the ACE drivers. See: https://www.microsoft.com/en-us/download/details.aspx?id=13255
Can you connect with SSMS?
- HiTechCoach - Wednesday, March 22, 2017 6:22 PM
Provided you don't use the so called "improvements" of the new versions of MS Access, you can always downdrade the database file to 2000-2003 (.mdb). If your Access database uses improvements such as multi-values fields (which contradicts the normal forms), you'll have to fix the Access tables first (normalize them) before beeing able to import them in a SQL Server database, whatever the version of SQL Server you use.
-
rf44 has identified the problem I believe - a short-coming of the SSMA. If you have an installation of a recent version of Access, there is an upsizing tool that will do the same task, although perhaps not quite as elegantly. Otherwise, the suggestion to copy the tables to a .mdb format database is the best option.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
One issue is several of the tables in the access database do not have primary keys. I did not design this monstrosity.
The sql server does not have access installed, so trying the ACE drivers has merit/ The version of access we are using is 2010.When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
OK - start the Access database while holding down the SHIFT key so you have full menus and the Navigation Pane. Click on the "ribbon" tab for Database Tools, and you should see a Move Data group with SQL Server as the first option. Clicking that will start the Upsizing Wizard. There you specify the SQL Server database that you want to upsize to, or you create a new SQL Server database (if you have that capability). Hopefully that will get you started, but you will need to create an ODBC data source pointing to that selected database in the long run. Hope this helps.
On the issue of tables not having a primary key, shame on the original developer. It is easy enough to add a primary key in SQL Server, and Access will be happier with the defined key as well. Linked SQL Server tables without a primary key are generally not updatable in Access.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
I logged into another server with sql2014 installed. Tried to use the 64 bit SQL Data import function. The Access Database Engine option was present as a data source and the early stages seemed promising. It ran to completion, but nearly all of the tables were skipped with no error messages. It seems the access data in this application is seriously hosed. One lonely table did generate about a page of errors so I'm checking that now.
Is it Miller time?When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
Upon future investigation I determined most of the Access tables I tried to upsize were missing Primary keys. I will tell the developer to add them.
However, a handful of tables errored out and did not leave an error message. I'd like to know if there is a way to determine what caused these tables to fail? Is some sort of log generated?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply