move data from one database to another

[hondaman2003]

I am using Visual Studio 2008 with a project using .NET 2.0. I am using an Access database as the backend. I need to keep this backend database as small as possible because we are doing business over a WAN and the network performance is really poor. I would like to move old data from this backend database to an archive database that is completely identicle. I have tried the 'MERGE' method for the target dataset and that is successful, however the 'hadschanges' property remains 'false' so when I call the update method it doesn't think there is anything to update so the data is not saved back to the target database.

The next idea I have is to loop through the source dataset and add those records one at a time. How do I do that?


P.S.
There are a lot of things about this situation that is really poor but there is nothing I can do about those things at this time regarding not using an SQL server or any other database management system or the network issues and also using .NET 2.0.

 

[Keyth]

Its a shame you cant migrate your work to SQL Express 2008 as it would handle all that with one query.


I think best way you can do it is with data reader.
Load values into a dataset.
Loop though row by row and execute insert command to archive database.

You really need to migrate to SQL Express 2008!

 

[PRPhx]

I have to add my 2 cents as to upgrading to SQL 2008 Express. There are just too many advantages in going with SQL Server (express or not). Among them (and you can use these as arguements for an update):

1) It's FREE!!!!

2) Would resuce network traffic (You do know that when you return a result set from Acces, the entire table is moved across the network)

3) You can set up a trigger to do your archiving

4) Set up jbs to automatically back up your data and do nightly maint.

5) Integrates well with the .Net framework.

6) It's not to terribly difficult to migrate data drom access to sql server.

 

[jontout]

Not an overly tecnical response, but considering you're using Access - I'd use Windows copy and paste to create the archive and manually purge any historical data, then compact and repair?

 

[hondaman2003]

Guys, you are right in every regard. The problem I run into is that IT doesn't have the resources to do it and since I'm not in IT, I'm not allowed to do it myself. In addition, they don't want me using MS Access but again, they don't have the resources to do this little project either.

It's a fun little quandary I and the business side are in right now.

 

[hondaman2003]

jontout,

That would work for the first time, but once you have the archive data in one place and the non-archived data in another place it would no longer work.