SQL Server Upgrade wizard

[Arne Garvander]

I am trying to upgrade some tables from Access 2007 to Sql Server 2008.
I get the error message that only Sql Server 6.5 and above are supported.
What am I doing wrong?

 

[Albert D. Kallal]

I am trying to upgrade some tables from Access 2007 to Sql Server 2008.
I get the error message that only Sql Server 6.5 and above are supported.
What am I doing wrong?
--

Are you trying to use a access data project, or just regular linked tables?

If your goal is to just move some tables up to sql server...then don't
choose to create a access data project, but choose linked tables...

That message hints that access does not support sql server 2008 as a data
project. (it might be a setting on sql side that is set for compatibility
for previous versions of sql server).

However, linked tables should work just fine for you...

 

[Arne Garvander]

Yes I know how to link an Access table to SQL Server.
Before I can do that I must copy my access tables to SQL server, which
sometimes is error free and sometimes full of errors.

 

[Albert D. Kallal]

Yes I know how to link an Access table to SQL Server.
Before I can do that I must copy my access tables to SQL server, which
sometimes is error free and sometimes full of errors.

Correct....

What I saying is do not choose the option to crate a new client/server
application (that means a access data project).

Try the option

[x] Link SQL server tables to the existing application.

Unless you really do need an access data project, then simply move the data
to sql server and use linked tables. As I was explaining in the previous
post access data projects are FAR MORE sensitive to the version of sql
server you are using.

So, give linked tables a try. Using Linked tables are far more tolerant and
flexible in terms of the version of sql server that you are using.

In fact, I not sure that access 2007 supports sql server 2008 as a "access
data project" since sql server 2008 was not out yet when access came out.

In general, you have more flexibility if you avoid the access data project
and use linked tables anyway.

So, try both approaches, but again the question was:

Are you trying to use a access data project, or just regular linked tables?

Simply give the linked table idea a try in place of using the client/server
option (when you choose client/server then you are creating what is called a
"access data project".

Note that choosing either option (client/server or Link Sql server tables)
will result with you having linked tables to sql server. However, as
mentioned choosing the option to link SQL server tables is more flexibility
and more tolerantly of different versions of sql server...

 

[Arne Garvander]

Albert,
Thanks for trying, but you don't seem to understand my problem.
I have legacy data in Access. I need to copy my data to SQL server BEFORE I
can link to SQL server. Exporting data from Access is error prone.

--
Arne Garvander
(I program VB.Net for fun and C# to get paid. When get paid, I laugh all the
way to the bank.)

Yes I know how to link an Access table to SQL Server.
Before I can do that I must copy my access tables to SQL server, which
sometimes is error free and sometimes full of errors.

Correct....

What I saying is do not choose the option to crate a new client/server
application (that means a access data project).

Try the option

[x] Link SQL server tables to the existing application.

Unless you really do need an access data project, then simply move the data
to sql server and use linked tables. As I was explaining in the previous
post access data projects are FAR MORE sensitive to the version of sql
server you are using.

So, give linked tables a try. Using Linked tables are far more tolerant and
flexible in terms of the version of sql server that you are using.

In fact, I not sure that access 2007 supports sql server 2008 as a "access
data project" since sql server 2008 was not out yet when access came out.

In general, you have more flexibility if you avoid the access data project
and use linked tables anyway.

So, try both approaches, but again the question was:

Are you trying to use a access data project, or just regular linked tables?

Simply give the linked table idea a try in place of using the client/server
option (when you choose client/server then you are creating what is called a
"access data project".

Note that choosing either option (client/server or Link Sql server tables)
will result with you having linked tables to sql server. However, as
mentioned choosing the option to link SQL server tables is more flexibility
and more tolerantly of different versions of sql server...

 

[Albert D. Kallal]

Albert,
Thanks for trying, but you don't seem to understand my problem.
I have legacy data in Access. I need to copy my data to SQL server BEFORE
I
can link to SQL server. Exporting data from Access is error prone.

I understand your problem perfectly.

What I'm saying when you use the upsizing wizard if the upsizing wizard
works will link tables for you after!!!

Of course if the upsizing wizard fails, then it's ***not*** going to create
that table link for you! However keep in mind that you have two methods of
transferring data to SQL server, and I'm simply saying to try both of the
methods. Those two methods use different technologies BEFORE the table
linking occurs. Ok...got it now?

That's why I asked if your tried both methods of upsizing. One method uses
the native oleDB database connection, and the other method uses an ODBC
connection and driver. So, there is two methods of transferring data and
upsizing data to SQL server here. Sometimes choosing one method of the other
will produce better results.

This occurs *****B E F O R E ***** the table link occurs!

So, NO, I am NOT asking you to link the data. I'm asking you did you try
both methods of upsizing and was there ***ANY*** difference at all between
the two methods of transferring and upsizing data to SQL server?

There was (is) a possibility that one of the methods might of work for you
and therefore would save you some programming and having to write some code
to scrub the data before you transferred that data.

ok, so I shall assume that you tried both methods of upsizing data (you
tried oleDB methoed, and you tried the odbc method).

Now that we've determined you tried both methods, then we're gonna have to
do a little bit of hunting here and use some alternate methods to fix this
problem.

The NEXT thing I would look at is corrupted date data. In fact simply bring
up the access table in question in view mode and do a sort on a date column.
You can then scroll down to the very first date values. You often find 2 or
3 bad date values that are well outside the range that SQL server allows
(and in 9 out ot 10 times you can also recognize that the few date values
are completely outside of what dates are typical for your application). The
the upsizing tools crap out and are **very** sensitive to bad date values.
So now that that's the next area you should concentrate on.

So, check your date value or date ranges. In my case I avoided having to
write some data scrubbing code because I found only about five records that
were Obviously outside of a reasonable date value. I simply edited those 4
bad data values and then my transfer (upsize) to sql server worked just
fine.

So, little bit of patience and thinking on your part can result in a lot of
fruit and a lot of progress on this issue. Even if the above idea of
sorting the date collum and looking for a few bad records does not help,
that's the first area I would concentrate on in terms of some scrubbing code
to allow the transfer.

The other area is to look at if your using a a packed decimal field in
access....

 

[Arne Garvander]

I have used three methods. The data transfer fails on the datetime column.