Using DTS to migrate Access ot SQL Server

[Wes Groleau]

When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

But it executes each query, and created a _table_ to hold the results.

The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.

I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.

--
Wes Groleau

Beware of the man who works hard to learn something, learns
it, and finds himself no wiser than before ... He is full of
murderous resentment of people who are ignorant without having
come by their ignorance the hard way.
-- Kurt Vonnegut

[Jonathan Roberts]

Re: Using DTS to migrate Access ot SQL Server

Wes Groleau wrote:

When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.
>
But it executes each query, and created a _table_ to hold the results.
>
The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.
>
I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.
>

Have you tried using the Upsize wizard?

Jonathan

[Erland Sommarskog]

Re: Using DTS to migrate Access ot SQL Server

Jonathan Roberts (gremln007@diyn ics.com) writes:

Wes Groleau wrote:

>When I try to import from Access, the DTS wizard only allows me to
>import tables and queries. OK, I'm not surprised the "macros" and
>reports don't come over.
>>
>But it executes each query, and created a _table_ to hold the results.
>>
>The sensible thing would be that SELECT queries become views
>and the others become stored procedures. But I find no way
>controls I can select to do that.
>>
>I have to manually create the views/procedures and paste in the SQL.
>And then I still have to translate it into standard SQL.
>>

>
Have you tried using the Upsize wizard?

I have never worked with Access myself, but from people that hold
expertise in both Access and SQL Server, I have heard strong recommendations
of staying away from the wizard.

Migrating from Access to SQL Server is quite an undertaking, and there
are many differences. Not the least since Access since is more or less
all-in-one, while with SQL Server you get a client/server setup.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Wes Groleau]

Re: Using DTS to migrate Access ot SQL Server

Jonathan Roberts wrote:

Wes Groleau wrote:

>When I try to import from Access, the DTS wizard only allows me to
>import tables and queries. OK, I'm not surprised the "macros" and
>reports don't come over.
>>
>[snip complaint details].

>
Have you tried using the Upsize wizard?

The upsize Wizard does not work. On the third or fourth
page, it pops up a box saying nothing but "Overflow"
Nothing happens in SQL Server.

--
Wes Groleau
"Lewis's case for the existence of God is fallacious."
"You mean like circular reasoning?"
"He believes in God. Therefore, he's fallacious."

[Wes Groleau]

Re: Using DTS to migrate Access ot SQL Server

Erland Sommarskog wrote:

Migrating from Access to SQL Server is quite an undertaking, and there
are many differences.

This is very true. But as flawed as it is, importing via DTS
and then "fixing it" is less of an undertaking than building
the whole thing manually from nothing.

And in any case, when the application you wrote or inherited
gets big enough to make Access become unstable, migrating to
SOMEthing becomes imperative.


--
Wes Groleau

Expert, n.:
Someone who comes from out of town and shows slides.

[Wes Groleau]

Re: Using DTS to migrate Access ot SQL Server

Wes Groleau wrote:

[snip extraneous comments and non-extraneous stuff full of typos].

I guess those flaws sort of obscured my question.

Access upsizing wizard does not work.

DTS in SQL Server 2000 has a wizard that DOES work.

But does there exist any form of automation that will
take all Access QUERIES into SQL Server views and/or sprocs
INSTEAD OF into tables containing the results of the queries?

--
Wes Groleau

Even if you do learn to speak correct English,
whom are you going to speak it to?
-- Clarence Darrow

[Jonathan Roberts]

Re: Using DTS to migrate Access ot SQL Server

Erland Sommarskog wrote:

>Have you tried using the Upsize wizard?

>
I have never worked with Access myself, but from people that hold
expertise in both Access and SQL Server, I have heard strong recommendations
of staying away from the wizard.
>
Migrating from Access to SQL Server is quite an undertaking, and there
are many differences. Not the least since Access since is more or less
all-in-one, while with SQL Server you get a client/server setup.
>
>

Erland,

You're right; it isn't perfect by any stretch. However, it can be a
start. I did it a few years back when an app was breaking due to
Access' problematic use of file locks and networking. There are some
gotchas, but it saved me some time. Once upsized, you can create an ADP
fairly quickly to get your forms up and running (assuming you were using
Access forms in the first place as your interface).

Jonathan

[Jonathan Roberts]

Re: Using DTS to migrate Access ot SQL Server

Wes Groleau wrote:

Jonathan Roberts wrote:

>Wes Groleau wrote:

>>When I try to import from Access, the DTS wizard only allows me to
>>import tables and queries. OK, I'm not surprised the "macros" and
>>reports don't come over.
>>>
>>[snip complaint details].

>>
>Have you tried using the Upsize wizard?

>
The upsize Wizard does not work. On the third or fourth
page, it pops up a box saying nothing but "Overflow"
Nothing happens in SQL Server.
>

Glad you mentioned it. You have hit the number one common problem with
the wizard. It does work but you have to overcome this issue. Google
it to find many pages of info. One KB is here:
http://support.microsoft.com/kb/272384. I don't remember the exact fix,
but it wasn't hard to resolve.

If you don't want to go that route, you can certainly pull out all your
queries manually and create views in SQL Server. I don't know of an
automated way to do this. If you have a lot of queries, this way may
not be practical.

Good luck and keep posting if you need help.

Jonathan

[Steve]

Re: Using DTS to migrate Access ot SQL Server

On Feb 3, 12:24 pm, Wes Groleau <groleau+n...@f reeshell.orgwro te:

When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.
>
But it executes each query, and created a _table_ to hold the results.
>
The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.
>
I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.
>
--
Wes Groleau
>
Beware of the man who works hard to learn something, learns
it, and finds himself no wiser than before ... He is full of
murderous resentment of people who are ignorant without having
come by their ignorance the hard way.
-- Kurt Vonnegut

Did you try the SQL Server Migration Assistant for Access?

http://www.microsoft.com/sql/solutions/migration/access/default.mspx

[Wes Groleau]

Re: Using DTS to migrate Access ot SQL Server

Jonathan Roberts wrote:

Wes Groleau wrote:

>The upsize Wizard does not work. On the third or fourth
>page, it pops up a box saying nothing but "Overflow"
>Nothing happens in SQL Server.

>
Glad you mentioned it. You have hit the number one common problem with
the wizard. It does work but you have to overcome this issue. Google
it to find many pages of info. One KB is here:
http://support.microsoft.com/kb/272384. I don't remember the exact fix,
but it wasn't hard to resolve.

"Obtain the latest service pack" You're right--that's not hard.
I didn't bother to look because my predecessor said that the only
fix was to install Access 2003. Installing a new version--or installing
a service pack--is not something our I.S. department will do on demand.

If you don't want to go that route, you can certainly pull out all your
queries manually and create views in SQL Server. I don't know of an
automated way to do this. If you have a lot of queries, this way may
not be practical.

It's not all that bad. Shift to SQL view. select, copy, "New View",
paste, save. But still, automation would be nice.


--
Wes Groleau

http://freepages.rootsweb.com/~wgroleau/Wes

[Wes Groleau]

Re: Using DTS to migrate Access ot SQL Server

Steve wrote:

On Feb 3, 12:24 pm, Wes Groleau <groleau+n...@f reeshell.orgwro te:

>When I try to import from Access, the DTS wizard only allows me to
>import tables and queries. OK, I'm not surprised the "macros" and
>reports don't come over.
>>
>But it executes each query, and created a _table_ to hold the results.
>>
>The sensible thing would be that SELECT queries become views
>and the others become stored procedures. But I find no way
>controls I can select to do that.
>>
>I have to manually create the views/procedures and paste in the SQL.
>And then I still have to translate it into standard SQL.
>>
>--
>Wes Groleau
>>
> Beware of the man who works hard to learn something, learns
> it, and finds himself no wiser than before ... He is full of
> murderous resentment of people who are ignorant without having
> come by their ignorance the hard way.
> -- Kurt Vonnegut

>
Did you try the SQL Server Migration Assistant for Access?
http://www.microsoft.com/sql/solutio...s/default.mspx

No. That's SQL Server 2005. Thanks anyway.

--
Wes Groleau

In any formula, constants (especially those obtained
from handbooks) are to be treated as variables.

[Ben]

Re: Using DTS to migrate Access ot SQL Server

Wes,

Just a thought. What are you trying to accomplish with the migration or
upsizing of your Access application? What is the Access version?
I used the Upsize Wizard to upsize our Access application. It went well
after I cleared all issues with my Access database. The Access
database structure is less strict and was meant to work on itself. Due to
the imperfections of the Access database the upsize wizard oftentimes
end up giving you a lot of errors the first or even 5th time. This is true
if I use even SQL Server 2005 Express. The data in the Access database
will also cause the upsize wizard to crash because SQL Server will not allow
erroneous data to creep into its tables.

FYI, using DTS will not expose all Access database problems also. You have
to understand and know which errors are caused by what.
Once you successfully upsized your access table, then you can concentrate on
deciding either to keep the queries or create views and stored
procedures in the SQL Server. This approach will be of help to you when you
link the tables from SQL Server in your Access application.

If you also try to google "Upsizing Access to SQL Server", you should find
more information regarding your task.

I also encourage you to use the Upsize Wizard of Access because that is why
it is there. It will definitely be quicker for you in the long run.
The upsize wizard if the access database or data have problems.

Based on my experience, the simplest approach (less work/change approach) is
usually the correct one.

"Wes Groleau" <groleau+news@f reeshell.orgwro te in message
news:1Yyxh.1926 $5U4.1714@trndd c07...

Steve wrote:

>On Feb 3, 12:24 pm, Wes Groleau <groleau+n...@f reeshell.orgwro te:

>>When I try to import from Access, the DTS wizard only allows me to
>>import tables and queries. OK, I'm not surprised the "macros" and
>>reports don't come over.
>>>
>>But it executes each query, and created a _table_ to hold the results.
>>>
>>The sensible thing would be that SELECT queries become views
>>and the others become stored procedures. But I find no way
>>controls I can select to do that.
>>>
>>I have to manually create the views/procedures and paste in the SQL.
>>And then I still have to translate it into standard SQL.
>>>
>>--
>>Wes Groleau
>>>
>> Beware of the man who works hard to learn something, learns
>> it, and finds himself no wiser than before ... He is full of
>> murderous resentment of people who are ignorant without having
>> come by their ignorance the hard way.
>> -- Kurt Vonnegut

>>
>Did you try the SQL Server Migration Assistant for Access?
>http://www.microsoft.com/sql/solutio...s/default.mspx

>
No. That's SQL Server 2005. Thanks anyway.
>
--
Wes Groleau
>
In any formula, constants (especially those obtained
from handbooks) are to be treated as variables.

[Erland Sommarskog]

Re: Using DTS to migrate Access ot SQL Server

Wes Groleau (groleau+news@f reeshell.org) writes:

"Obtain the latest service pack" You're right--that's not hard.
I didn't bother to look because my predecessor said that the only
fix was to install Access 2003. Installing a new version--or installing
a service pack--is not something our I.S. department will do on demand.

In that case... Do a check with SELECT @@version in your SQL 2000 instance
to check that you have at least SP3. The version number should read
8.00.760 for SP3 and 8.00.2039 for SP4. If it's lower than 760, you should
insist on an upgrade.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Wes Groleau]

Re: Using DTS to migrate Access ot SQL Server

Erland Sommarskog wrote:

Wes Groleau (groleau+news@f reeshell.org) writes:

>"Obtain the latest service pack" You're right--that's not hard.
>I didn't bother to look because my predecessor said that the only
>fix was to install Access 2003. Installing a new version--or installing
>a service pack--is not something our I.S. department will do on demand.

>
In that case... Do a check with SELECT @@version in your SQL 2000 instance
to check that you have at least SP3. The version number should read
8.00.760 for SP3 and 8.00.2039 for SP4. If it's lower than 760, you should
insist on an upgrade.
>
>

--
Wes Groleau
^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^
^ A UNIX signature isn't a return address, it's the ASCII equivalent ^
^ of a black velvet clown painting. It's a rectangle of carets ^
^ surrounding a quote from a literary giant of weeniedom like ^
^ Heinlein or Dr. Who. ^
^ -- Chris Maeda ^
^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^

[Wes Groleau]

Re: Using DTS to migrate Access ot SQL Server

Erland Sommarskog wrote:

Wes Groleau (groleau+news@f reeshell.org) writes:

>"Obtain the latest service pack" You're right--that's not hard.
>I didn't bother to look because my predecessor said that the only
>fix was to install Access 2003. Installing a new version--or installing
>a service pack--is not something our I.S. department will do on demand.

>
In that case... Do a check with SELECT @@version in your SQL 2000 instance
to check that you have at least SP3. The version number should read
8.00.760 for SP3 and 8.00.2039 for SP4. If it's lower than 760, you should
insist on an upgrade.

THANKS, I will. My colleague is meeting tomorrow with I.S. to
discuss exactly what they are supposed to do and what we are
supposed to do. I gave him the MS KB article; I'll give him
this info as well.

--
Wes Groleau
"To know what you prefer, instead of humbly saying
Amen to what the world tells you you should prefer,
is to have kept your soul alive."
-- Robert Louis Stevenson