I have used the code below to copy data from one Access file to another
Access file, and it's seems to be pretty fast. When the source tables
are in Oracle and using an OracleConnection as source, my insertstring
won't work anymore. Will I have to start filling datasets, traverse the
rows and copy each field in for-loops? Won't that be terribly slow? Is
there a faster way?

//Destination Access file exists and has the same tablenames and
fieldnames as the source, so:
OleDbConnection conSrc =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
"data source=" + @"c:\source.mdb" );
conSrc.Open();
string insertstring = @"INSERT INTO tablename IN "c:\destination.mdb"
SELECT * FROM tablename";
OleDbCommand cmd = new OleDbCommand(insertString, conSrc);
cmd.ExecuteNonQuery();

On Sat, 29 Oct 2005 01:39:42 +0200, Troy McLure <(E-Mail Removed)> wrote:

¤ I have used the code below to copy data from one Access file to another
¤ Access file, and it's seems to be pretty fast. When the source tables
¤ are in Oracle and using an OracleConnection as source, my insertstring
¤ won't work anymore. Will I have to start filling datasets, traverse the
¤ rows and copy each field in for-loops? Won't that be terribly slow? Is
¤ there a faster way?
¤
¤ //Destination Access file exists and has the same tablenames and
¤ fieldnames as the source, so:
¤ OleDbConnection conSrc =
¤ new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
¤ "data source=" + @"c:\source.mdb" );
¤ conSrc.Open();
¤ string insertstring = @"INSERT INTO tablename IN "c:\destination.mdb"
¤ SELECT * FROM tablename";
¤ OleDbCommand cmd = new OleDbCommand(insertString, conSrc);
¤ cmd.ExecuteNonQuery();

The following SQL string works for me (connect to the Access database not Oracle):

INSERT INTO [AccessTable] SELECT * FROM [OracleTable] IN '' [ODBC;Driver={Microsoft ODBC For
Oracle};Server=OracleServer;Uid=userid;Pwd=password;]


Paul
~~~~
Microsoft MVP (Visual Basic)

I'll try that. Thanks!

Paul Clement wrote:
> On Sat, 29 Oct 2005 01:39:42 +0200, Troy McLure <(E-Mail Removed)> wrote:
>
> ¤ I have used the code below to copy data from one Access file to another
> ¤ Access file, and it's seems to be pretty fast. When the source tables
> ¤ are in Oracle and using an OracleConnection as source, my insertstring
> ¤ won't work anymore. Will I have to start filling datasets, traverse the
> ¤ rows and copy each field in for-loops? Won't that be terribly slow? Is
> ¤ there a faster way?
> ¤
> ¤ //Destination Access file exists and has the same tablenames and
> ¤ fieldnames as the source, so:
> ¤ OleDbConnection conSrc =
> ¤ new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
> ¤ "data source=" + @"c:\source.mdb" );
> ¤ conSrc.Open();
> ¤ string insertstring = @"INSERT INTO tablename IN "c:\destination.mdb"
> ¤ SELECT * FROM tablename";
> ¤ OleDbCommand cmd = new OleDbCommand(insertString, conSrc);
> ¤ cmd.ExecuteNonQuery();
>
> The following SQL string works for me (connect to the Access database not Oracle):
>
> INSERT INTO [AccessTable] SELECT * FROM [OracleTable] IN '' [ODBC;Driver={Microsoft ODBC For
> Oracle};Server=OracleServer;Uid=userid;Pwd=password;]
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)