Copy ms access tables from one table to an other using vb.net

[mpoincare]

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

[RobDog888]

You could execute a query to insert all the records to a new table.

SELECT Table1.* INTO Table2 FROM Table1;

[Shuja Ali]

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

How about using a simple query to do this, instead of using any of the classes.

a simple query to copy a Table from one MDB file to Another MDB File will look like this

SELECT Table1.* INTO TABLE1 IN 'C:\test.mdb' FROM Table1;

You will have to run this query using the OleDBCommand Object.

First open a connection to the database from which you want to copy the table, then execute the above mentioned query(Change it to suit your needs). Give the path of the new DB where you want your table to be copied..

PS: This query will always drop the table if it exists in the Target DB.

[jonni]

Hi,

Do you by any chance know how to do this if the source db is SQL Server instead of Access.

I tried with access and it worked fine, even though the target table had to be droppet before copied..

I tried with SQL Server as source db both using SQL and OleDb but I didn't get it to work.

Any tips?

Regards

Jonni

[Boumxyz2]

SQL Server syntax should be the same

I use this every day

SELECT * into tablename1 from tablename2

will copy table2 into table1

[Andreas Masur]

[ Merged threads ]

[jonni]

Maybe I was unclear, I don't want the target db to be SQL Server only the source db. I want to copy a SQL Server table to an access db, so I guess I have to tell to which db I want to copy the table to.

For Access it's working this way:

SELECT Table1.* INTO Table2 IN 'C:\LeLog\dbSync2.mdb' FROM Table1

When I run this query to an access db it works fine but when I run it to SQL Server it get: Incorrect syntax near keyword 'IN', so I suppose the syntax is different to name the destination db in SQL Server..

Any ideas?

Regards

Jonni

[Boumxyz2]

ok you want to copy a table from Access to SQL-Server and / or Copy a SQL server table to access..

Well I think you may want to check on DTS...


Because I don't think in access you can do

SELECT * into Table1 from servername.databasename.owner.tablename

In SQL server if you want to copy from server to server can either use DTS

SELECT * into server.database.owner.tablename from server.database.owner.tablename