Moving new data to already upsized SQL Server

[ahjeck]

Hi, I have upsized my Access 2000 back-end tables into SQL Server 2000 successfully and did a simple table-link of those to my Access front-end.

I did many testing with test data, but now it's time to move in the live data (in Access). Is there a way to move just the Access data into the already upsized SQL Server tables?

 

[SQL_Hell]

Hi there

In enterprise manager right click on the database, select all tasks, then select import data. This will open a import/export wizard which is a wizard that creates a DTS package. Select MS acess for your data source, select the tables you want to copy and make sure you select 'replace data' rather than 'append data'.

 

[ahjeck]

Thanks for the reply SQL_hell. This is exactly what I wanted. One question though... I'm using SQL server 2000. I see the append (which is by default) but I do not see 'replace data'. I do see 'Delete rows in destination table' so I'm assuming what they're doing is removing the SQL server table and then replacing it with the new data.

Anyhow, it works fine, with some minor conflicts that I had to fix. Thanks!

 

[SQL_Hell]

Yep that's right, the correct option is to check the 'delete rows in destination table'. Sorry my mistake I couldn't remember what the options were off hand .
I normally check the drop and recreate table option as well (just to be sure)

You can also save and shcedule the DTS package created by the wizard, which is useful should you need to do it again.

I'm glad I could help