Copy ms access tables from one DB to an other using ado.net in vb.

[Guest]

Hi everyone,
I need to know how to copy a ms access table from database A to database B
using ado.net in vb.net. What classes of the OLEDB namespaces will allow me
to do this? Can you please provide a code sample?
Thanks for your help.

 

[Cor Ligthert [MVP]]

Zadi,

This can have an easy answer, you cannot. AdoNet does has no features to
create an ms access database. You can only access a database by using SQL
commands.

I hope this gives an idea.

Cor

 

[Miha Markic [MVP C#]]

As Cor mentioned, there is no built in support for this.
You might use OleDbConnection.GetOleDbSchemaTable to get table metadata and
then use appropriate sql commands (or jet engine) to add this table
structure to another table.

 

[Paul Clement]

¤ Hi everyone,
¤ I need to know how to copy a ms access table from database A to database B
¤ using ado.net in vb.net. What classes of the OLEDB namespaces will allow me
¤ to do this? Can you please provide a code sample?
¤ Thanks for your help.

The following code will copy tables and their data from one access database to another. However, it
will not create indexes or relationships. If you need to create those you will need to execute some
Access SQL DDL statements.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

Function ExportAccessToAccess() As Boolean

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [MS
Access;DATABASE=E:\My Documents\db10.mdb;].[Table1] FROM Table1", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

End Function


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Paul,

As you probably well know, do I know that too, however that is not using
ADONET as the OP askes, that is using ADODB (What is posible to use in VS
Net).

Cor

 

[Paul Clement]

¤ Paul,
¤
¤ As you probably well know, do I know that too, however that is not using
¤ ADONET as the OP askes, that is using ADODB (What is posible to use in VS
¤ Net).

Cor,

He was asking about using the OLEDB (System.Data.OleDb) namespace and not ADODB.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Paul,

You are right, I misrelated your answer. The clue is that you export it to
an existing ms Access Database and I was even not reading completely your
message, used as I am to your AdoDB answer on this.

Your answer is correct.



Sorry

I am a little bit :-( about my reply to you.

Cor

 

[Paul Clement]

¤ Paul,
¤
¤ You are right, I misrelated your answer. The clue is that you export it to
¤ an existing ms Access Database and I was even not reading completely your
¤ message, used as I am to your AdoDB answer on this.
¤
¤ Your answer is correct.
¤
¤
¤
¤ Sorry
¤
¤ I am a little bit :-( about my reply to you.
¤
¤ Cor
¤

No problem Cor. ;-)


Paul
~~~~
Microsoft MVP (Visual Basic)