Copy all records from access MDB to others via ADONET

[Paul Clement]

¤ If you have got a raw clipboard from your code somewhere, it might help.
¤ Otherwise i'll mess with it later on myself.
¤ Good to know that (possibly) INSERT INTO can handle the db name.
¤

Below is an example that operates between SQL Server and Excel. The column names are assumed to
match:

INSERT INTO [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Orders2] SELECT * FROM [Orders$];

Below is another example (Access and Excel) that uses column names:

INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4 from [Excel 8.0;DATABASE=E:\My
Documents\Book10.xls;HDR=No;IMEX=1].[Sheet1$];


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Edwin Knoppert]

Thanks, you are a good help

Just a minor question though, the copy never creates the primary key while
the source db has a PK.
The target MDB does not get indexed.
I know i can (alreay did) do this by a CREATE UNIQUE INDEX but then i need
to 'manualy' open the target and make the modification.
Maybe there is an option in the SELECT INTO stuff itself?

Thanks,

 

[Paul Clement]

¤ Thanks, you are a good help
¤
¤ Just a minor question though, the copy never creates the primary key while
¤ the source db has a PK.
¤ The target MDB does not get indexed.
¤ I know i can (alreay did) do this by a CREATE UNIQUE INDEX but then i need
¤ to 'manualy' open the target and make the modification.
¤ Maybe there is an option in the SELECT INTO stuff itself?
¤

The following worked for me and creates a primary key constraint for a column called RecID:

ALTER TABLE [MS Access;DATABASE=c:\test files\db1 xp.mdb;].[Table7] ALTER COLUMN RecID INTEGER
CONSTRAINT PK_Table7 PRIMARY KEY

If you need any help with Jet SQL see the following:

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


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Edwin Knoppert]

Look at that !

Also the website is super!
Will bookmark it.

Thanks!