Upsizing from access 2000 to sql 2000

[zippynz]

Hi,

I have an access database which I need to convert to an sql 2000 database,

I have set up msde on my machine and can use the acces 200 upsizing wizard to export it to msde (sql7), everything works fine (well a delete statement doesnt but im not concerned about that now)
ok so I have had a web server set up with sql 2000 and i am now trying to export it to that,
origionally i exported it using the msde exporting tool,
that got the data and tables there but the autonumber field from access has not converted to an identity field,
so now i tried using the access tool again and all i get is an error that says overflow and i can't go any further,

anyone have any ideas about this?
all my data and tables are in the database so all i really need is a way of converting some fields to autonumber/identity fields, can this be done through an sql statement?

any help REALLY appreciated,
thanks

 

[zippynz]

anyone have any ideas?
i really need to get this to work, but can't find any info on it,
you guys are my last hope *snif snif*

 

[DougP]

I do this alot on SQL server 7.
Especially if you don't have alot of tables in Access.
First make a backup of your Access database and make sure its closed before doing the SQL Server stuff.
Open the SQL Entetrpise Manager.

Open the database where you want to add the Access tables
then open the view so you can see the Tables

Next right click on the Tables in the left pane
Click "All Tasks" then "Import Data"

you are running the DTS (Data Transformation Sevices) Wizard
The first screen is for where the data is coming from.
This is your Access database.

click in the "Source" Drop down and find Microsoft Access, its up near the top of the list.
It will import either Access '97 or 2000
the next screen is for where the data is going to. Your Local SQL server.
follow the prompts
it will import all of the tables (you pick and choose)
Very fast I might add.
15,000 records in 15 seconds

next on the client(s)
create an ODBC source or use a DSN-less connection
then link to the NEW SQL tables in the original Access database.

check everything and then delete the original tables






DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive