Upsizing wizard and SQL Server 2008 Express

[langdonwhite]

I have an application in Access 2003 that I am hoping to "upsize" to SQL
Server. I started down this path a few months ago initially with SQL Server
2005 but when I got back to it this week I decided to try out SQL Server 2k8.
I have downloaded and installed (more than once) SQL Server 2008 Express w/
Advanced Tools. When I ask Access to "upsize" using the wizard in the tools
menu I get an error telling me that I need to use at least SQL Server 6.5
(now, while I liked 6.5, I think 2008 might be *slightly* better .
Literally: "The Upsizing Wizard only works with Microsoft SQL Server
(Versions 6.50 SP5 or higher). Please log in to a SQL Server data source."

In ther forums/kd articles (related to A2k3 and SQL2k5) I saw a suggestion
that I use a file DSN to access the db. I tried this using both the "new"
native driver and the more traditional one. I have tried using "local" and
machine/instance_name through both the DSN and directly through access. I
have also tried all the above using windows accounts and sa for the
connection.

Consistently, I get either the above error or "Connection Failed" with State
= 01000 and Error = 53 and an embedded error of 17 with a State of 08001.

I am kind of coming to the conclusion that Access needs a patch to recognize
SQL 2k8. Has anyone else seen this issue? Know any workarounds? or know of a
known bug? I haven't found any information about pairing A2k3 and SQL2k8
basically anywhere. Alternately, does upsizing only work on "better" versions
of SQL Server (i.e. Web, Standard, Enterprise)?

TIA

 

[Sylvain Lafontaine]

Probably that your problem comes from the fact that the Upzising Wizard make
a check on the version of SQL-Server and refuse to do its (poor) work if
it's not SQL-Server 6.5, 7 or 2000. Using or not a DSN change nothing to
that. It's also possible that you might have a connection or a login issue
but as you are able to connect with SSMS (SQL-Server Management Studio), I
don't think that your problem is there.

Instead of the Upsizing Wizard, you should try with the SQL-Server Migration
Assistant for Access (SSMA-A):

http://www.google.com/search?q=SQL-Server+Migration+Assistant+for+Access

Once your application will be upsized to an MDB file with ODBC linked
tables, you'll see no difference at all between any version or edition of
SQL-Server. Of course, for passtrough queries or for ADP projects, you'll
be able to take advantage of some of the more advanced features of
SQL-Server 2008 but in the meantime, you have a lot of other things to learn
of.

 

[boblarson]

Also, what driver are you attempting to use to connect to the SQL instance?
You should be using the SQL Native Client, instead of the generic SQL Server
ODBC driver.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[Tony Toews [MVP]]

When I ask Access to "upsize" using the wizard in the tools
menu I get an error telling me that I need to use at least SQL Server 6.5
(now, while I liked 6.5, I think 2008 might be *slightly* better .

<chuckle> Thanks for posting that!

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[blad3 runn69]

Dear Sylvain,
Thank you very much for info
re: upsizing to SQL 2008 & SSMA.
SSMA sure is a great tool.
msaccess 97 into SQL 2008 no problems.
Thanks again