What would be the best aproach?

[Frank]

Hi All,

I have a task at hand and I am trying to plan out how I should approach it.

I can use either VS.NET 2003 or 2005

I need to grab data out of an Access 97 db and push it to SQL Server 2000.

I intend on developing a standalone Windows app that sits on the same
computer where the Access db resides.

I was thinking I would use a datareader to grab the data. Can I then
directly push the data into the SQL Server db?

Any thoughts will be appreciated.

[Andy]

Use VS 2005 if you have a choice; .Net 2.0 is much improved.

As for the data import.. SQL server can do that without the need for
you to create a program. Import the data, then, if needed, use T-Sql
to put it into another table structure / correct the data.

You can write your own program though, and yes you'd use the DataReader
to read from Access; you could use SqlCommand to call procedures or ad
hoc Sql to insert the data into Sql Server.

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,
"Frank" <fk******@pfmail.com> wrote in message
news:um**************@TK2MSFTNGP04.phx.gbl...

Hi All,

I have a task at hand and I am trying to plan out how I should approach
it.

I can use either VS.NET 2003 or 2005
Does not matter really, not for the task in hand
I need to grab data out of an Access 97 db and push it to SQL Server
2000.

I intend on developing a standalone Windows app that sits on the same
computer where the Access db resides.
Also worth considering is a console app
I was thinking I would use a datareader to grab the data. Can I then
directly push the data into the SQL Server db?

A MUCH better approach would be to use DTS, you can define your package in
enterprise manager, save it to disk and then just call it from code, this
has the added benefit to take care of any transformation of the data
needed..
Having a dataset created is not a good idea here.

Use this code to execute a DTS

using DTS;

void RunPackage( string packSource, string packName, string dataSource)
{
try
{
Package2Class package = new Package2Class();
object pVarPersistStgOfHost = null;

// if you need to load from file
package.LoadFromStorageFile(
packSource,
null,
null,
null,
packName,
ref pVarPersistStgOfHost);

package._Package_Connections.Item(1).DataSource = dataSource; // the SQL
conn string
package.Execute();
package.UnInitialize();

// force Release() on COM object
//
System.Runtime.InteropServices.Marshal.ReleaseComO bject(package);
package = null;
}