Very Computer

I have been trying to use the DTS Import to upsize an Access 97 DB to SQL
7.0 but I have not been able to determine whether, or how, SQL 7.0 imports
Access "autonumber" fields. All of my tables use autonumber fields to
maintain relationships so I must be able to import them and have SQL 7.0
make them identity fields, seeded with the values that are already in the
Access DB.
Can anyone tell me if SQL 7.0 will recognize this and create the table in
the SQL DB properly so that all of my relationships integrity is not lost?
Does SQL 7.0 recognize Access autonumber fields?
If it recognizes autonumber fields, will it create them as identity fields
when it imports into SQL 7.0?
Will it seed them properly so that my current data and relationships are not
lost and it continues to increment the field correctly when records are
added?
Do I need to do anything to make sure this happens?
Do I need to manually set up a trigger for each table to do this?

The documentation has not been helpful in this area.

--
Thanks,
Chris Stamey
-----------------

http://www.Stamey.nu
http://www.Stamey.nu/DNSConcepts.asp
http://www.Farther.com/VBAnswerNetwork
Senior Network Engineer
Windows NT Administrator
Aspiring AS400 Administrator
VB Developer
ASP and Web Developer
Got a little JavaScript under my belt too.
More to come...
-----------------
Don't make me use uppercase...
Calm down -- it's only ones and zeroes.

I would suggest trying to use the Upsizing Tools for Access instead of DTS. When you use the upsizing wizard in Access2000, the autonumber data type in Access is converted to an Identity column in SQL Server.  There is a upsizing tool for Access97at htp://www.microsoft.com/AccessDev/ProdInfo/AUT97dat.htm, but I not sure if it works the same.
If you have not already seen the Access to SQL Server white paper, go to http://www.microsoft.com/sql/interopmigrate/accessmigration.htm

  I have been trying to use the DTS Import to upsize an Access 97 DB to SQL 7.0 but I have not been able to determine whether, or how, SQL 7.0 imports Access "autonumber" fields. All of my tables use autonumber fields to maintain relationships so I must be able to import them and have SQL 7.0 make them identity fields, seeded with the values that are already in the Access DB.
  Can anyone tell me if SQL 7.0 will recognize this and create the table in the SQL DB properly so that all of my relationships integrity is not lost?
  Does SQL 7.0 recognize Access autonumber fields?
  If it recognizes autonumber fields, will it create them as identity fields when it imports into SQL 7.0?
  Will it seed them properly so that my current data and relationships are not lost and it continues to increment the field correctly when records are added?
  Do I need to do anything to make sure this happens?
  Do I need to manually set up a trigger for each table to do this?

  The documentation has not been helpful in this area.

  --
  Thanks,
  Chris Stamey
  -----------------

  http://www.Stamey.nu
  http://www.Stamey.nu/DNSConcepts.asp
  http://www.Farther.com/VBAnswerNetwork
  Senior Network Engineer
  Windows NT Administrator
  Aspiring AS400 Administrator
  VB Developer
  ASP and Web Developer
  Got a little JavaScript under my belt too.
  More to come...
  -----------------
  Don't make me use uppercase...
  Calm down -- it's only ones and zeroes.

Thanks for the info. I DLed the upsizing tool and it seemed to work fine,
including the relationships and the identity fields. I now have to get my
Access application to work with it, or write a new front end.

Thanks,
Chris Stamey
-----------------

http://www.Stamey.nu
http://www.Stamey.nu/DNSConcepts.asp
http://www.Farther.com/VBAnswerNetwork
Senior Network Engineer
Windows NT Administrator
Aspiring AS400 Administrator
VB Developer
ASP and Web Developer
Got a little JavaScript under my belt too.
More to come...
-----------------
Don't make me use uppercase...
Calm down -- it's only ones and zeroes.

    I would suggest trying to use the Upsizing Tools for Access instead of
DTS. When you use the upsizing wizard in Access2000, the autonumber data
type in Access is converted to an Identity column in SQL Server.  There is a
upsizing tool for Access97at
htp://www.microsoft.com/AccessDev/ProdInfo/AUT97dat.htm, but I not sure if
it works the same.
    If you have not already seen the Access to SQL Server white paper, go to
http://www.microsoft.com/sql/interopmigrate/accessmigration.htm

        I have been trying to use the DTS Import to upsize an Access 97 DB
to SQL 7.0 but I have not been able to determine whether, or how, SQL 7.0
imports Access "autonumber" fields. All of my tables use autonumber fields
to maintain relationships so I must be able to import them and have SQL 7.0
make them identity fields, seeded with the values that are already in the
Access DB.
        Can anyone tell me if SQL 7.0 will recognize this and create the
table in the SQL DB properly so that all of my relationships integrity is
not lost?
        Does SQL 7.0 recognize Access autonumber fields?
        If it recognizes autonumber fields, will it create them as identity
fields when it imports into SQL 7.0?
        Will it seed them properly so that my current data and relationships
are not lost and it continues to increment the field correctly when records
are added?
        Do I need to do anything to make sure this happens?
        Do I need to manually set up a trigger for each table to do this?

        The documentation has not been helpful in this area.

        --
        Thanks,
        Chris Stamey
        -----------------

        http://www.Stamey.nu
        http://www.Stamey.nu/DNSConcepts.asp
        http://www.Farther.com/VBAnswerNetwork
        Senior Network Engineer
        Windows NT Administrator
        Aspiring AS400 Administrator
        VB Developer
        ASP and Web Developer
        Got a little JavaScript under my belt too.
        More to come...
        -----------------
        Don't make me use uppercase...
        Calm down -- it's only ones and zeroes.

It's easier to SaveAs/export Access tables in ascii (text) format, then do a bcp to the SQL server. (that's for large tables over a million records).Otherwise you get fields that are 255 characters, or  you get timeouts.
It's also workable if the tables are small to Save As/Export and export it to an ODBC source, then point to a DSN.

  I have been trying to use the DTS Import to upsize an Access 97 DB to SQL 7.0 but I have not been able to determine whether, or how, SQL 7.0 imports Access "autonumber" fields. All of my tables use autonumber fields to maintain relationships so I must be able to import them and have SQL 7.0 make them identity fields, seeded with the values that are already in the Access DB.
  Can anyone tell me if SQL 7.0 will recognize this and create the table in the SQL DB properly so that all of my relationships integrity is not lost?
  Does SQL 7.0 recognize Access autonumber fields?
  If it recognizes autonumber fields, will it create them as identity fields when it imports into SQL 7.0?
  Will it seed them properly so that my current data and relationships are not lost and it continues to increment the field correctly when records are added?
  Do I need to do anything to make sure this happens?
  Do I need to manually set up a trigger for each table to do this?

  The documentation has not been helpful in this area.

  --
  Thanks,
  Chris Stamey
  -----------------

  http://www.Stamey.nu
  http://www.Stamey.nu/DNSConcepts.asp
  http://www.Farther.com/VBAnswerNetwork
  Senior Network Engineer
  Windows NT Administrator
  Aspiring AS400 Administrator
  VB Developer
  ASP and Web Developer
  Got a little JavaScript under my belt too.
  More to come...
  -----------------
  Don't make me use uppercase...
  Calm down -- it's only ones and zeroes.