access and sql

[DevAccess]

Hello

I am upsizing ms access db ( access 2003 ) with sql server

"version Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (Intel X86)
Jul 3 2017 02:37:05
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.3 <X64> (Build 17134: ) (WOW64) "

But it gives me error "The upsizing wizard only works with microsft sql server ( version 6.50 sp or higher ). Please log in to a sql server data source"

Please suggest what is wrong here even this happens if I choose to select existing database in wizard as an option for upsize

what other alternatives one should go for ?

Thanks
MAN

 

[isladogs]

Try doing it from SQL Server using the SQL Server Import & Export Wizard.
Its similar but more powerful

if that's not available using the Enterprise Evaluation Edition, instead use the free SQL Server Express Management Studio (SSMS)

 

[DevAccess]

Try doing it from SQL Server using the SQL Server Import & Export Wizard.
Its similar but more powerful

if that's not available using the Enterprise Evaluation Edition, instead use the free SQL Server Express Management Studio (SSMS)

Thanks this has worked, but I had to create primary keys manually after import. is there anyway we can import primary keys as well while import.

 

[isladogs]

I know I've done it successfully in the past but hadn't done this for a few years and had to check.
My first attempt using the Wizard supplied with the Express edition had the same issue as you. Why MS make it difficult is beyond me.

Try clicking Edit Mappings & then Enable Identity Insert which SHOULD work ... but didn't when I just tried it!
If that still fails for you, you may need to edit the script manually

OR try a free converter by Bullzip 'Access to MS SQL' from http://www.bullzip.com/download.php

Or do some googling as I'm sure it should be straightforward ....
Here's a couple of links that may be useful
https://stackoverflow.com/questions/5893575/import-and-export-wizard-with-identity-column/25452468
https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=en-GB&k=k(sql12.dts.impexpwizard.reviewissues.f1)&rd=true

Let me know how you get on.
In the meantime, perhaps someone else can provide the definitive solution

UPDATE:
I've just retested using the upsizing wizard in A2010 & the PK fields are successfully transferred to SQL server
So there must be a simple trick to fix this in the SQL import wizard ...

 

[Pat Hartman]

It isn't reasonable to expect all versions of all software to talk to all versions of other software. When A2003 was built, SQL Server 2014 was 11 years in the future. Usually new versions of software can talk to at least some old versions but old versions can almost never talk to newer versions and so the upsizing wizard isn't recognizing the SQL Server version.

Convert to at least A2010 (last version with integrated upsizing wizard) and that might work. Otherwise download SSMA (SQL Server Migration Assistant). This product has gotten flaky in the last couple of years because MS made the poor decision to not support 32 bit versions of Access which are of course what they actually recommend installing. Another case of the left hand not knowing what the right hand is doing. But, It is designed to work as a bridge between a wide set of Access versions and a wide set of SQL Server versions and so it might be your best option. You will need to install the 32 bit version (which I am told was recently released) because you are using a 32 bit version of Office regardless of whether or not you are running a 64-bit version of Windows. All that matters is the bit=ness of Access.

 

[isladogs]

Pat

Have you tried the SQL Server Import Export wizard yourself recently?

I still can't get it to successfully import the PK even though I've enabled identity insert. And yet I'm sure it used to work
I'm using SQL Server Management Studio 2014 Express Edition