Copy all records from access MDB to others via ADONET

[Edwin Knoppert]

I'm generating an MDB or XLS or CSV from an access database (MDB)

I want to copy all records in the query over to the newly created database
and (empty) table.

I seen: http://www.w3schools.com/sql/sql_select_into.asp which only shows a
basic select into..

Assume i have two connections open, the xls table i can create if it's a
must.

I wanted to use alias fieldnames so the target may have a different
fieldname.
Possibly even the target tablename could be different, while this is not an
issue yet.

Any idea? (besides some dumb and slow row/col loop)

Remember, from MDB to XLS e.o..

 

[Paul Clement]

¤ I'm generating an MDB or XLS or CSV from an access database (MDB)
¤
¤ I want to copy all records in the query over to the newly created database
¤ and (empty) table.
¤
¤ I seen: http://www.w3schools.com/sql/sql_select_into.asp which only shows a
¤ basic select into..
¤
¤ Assume i have two connections open, the xls table i can create if it's a
¤ must.
¤
¤ I wanted to use alias fieldnames so the target may have a different
¤ fieldname.
¤ Possibly even the target tablename could be different, while this is not an
¤ issue yet.
¤
¤ Any idea? (besides some dumb and slow row/col loop)
¤
¤ Remember, from MDB to XLS e.o..
¤

Are you trying to export to an existing Excel Worksheet or are you creating a new one on the fly
through the export?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[William \(Bill\) Vaughn]

If the target is any version of SQL Server, you can use the SqlBulkCopy
function to export rows (from anywhere) to a SQL Server table.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Edwin Knoppert]

I'm creating XLS and XSV (TEXT) through ADO itself.
Not via excel (com-interface etc)

Since both are connection objects i was hoping i could use SQL statements
for these.
CSV is extremely slow with record by record export.
(INSERT INTO per record)

 

[William \(Bill\) Vaughn]

As I said, if you're importing INTO SQL Server, BCP is the trick--regardless
of the data format. XML and CSV are going to be slower.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Edwin Knoppert]

I know, but that's not the issue now heh?

Like i said, i'm looking for a faster export and try to abandon the row by
row export.
Maybe a dataset or somethinng like that might work better.
So i don't need to use an INSERT INTO statement.
O btw, the INSERTs are dine during a transaction.
Not sure if that helps with such formats.

 

[Paul Clement]

¤ I'm creating XLS and XSV (TEXT) through ADO itself.
¤ Not via excel (com-interface etc)
¤
¤ Since both are connection objects i was hoping i could use SQL statements
¤ for these.
¤ CSV is extremely slow with record by record export.
¤ (INSERT INTO per record)
¤

Below is an example that exports from Access to Excel. It creates a new Worksheet in the Workbook.
It's also possible to export into an existing Worksheet, if necessary, by modifying the SQL
statement.

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

AccessConn.Open()

'New sheet in Workbook
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel
8.0;DATABASE=e:\My Documents\BookTest.xls;HDR=NO;].[Sheet7] from [Table7]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Edwin Knoppert]

Thanks!!
I'll try tomorrow..


If this works i may assume speed is much better this way

 

[Edwin Knoppert]

Super!!
MDB, Excel and CSV (TEXT) export now works instantly!

 

[Edwin Knoppert]

Any chance the fieldnames will get inserted by TEXT (csv)?

I see them in Excel and MDB, not in csv.

Thanks,

PS, already tried HDR = YES

 

[Edwin Knoppert]

Forget it, it seem some caching of the csv file.
Seems to work now, now fixing the caching somehow

Any chance the fieldnames will get inserted by TEXT (csv)?

I see them in Excel and MDB, not in csv.

Thanks,

PS, already tried HDR = YES

On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert"

¤ I'm creating XLS and XSV (TEXT) through ADO itself.
¤ Not via excel (com-interface etc)
¤
¤ Since both are connection objects i was hoping i could use SQL
statements
¤ for these.
¤ CSV is extremely slow with record by record export.
¤ (INSERT INTO per record)
¤

Below is an example that exports from Access to Excel. It creates a new
Worksheet in the Workbook.
It's also possible to export into an existing Worksheet, if necessary, by
modifying the SQL
statement.

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

AccessConn.Open()

'New sheet in Workbook
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT *
INTO [Excel
8.0;DATABASE=e:\My Documents\BookTest.xls;HDR=NO;].[Sheet7] from
[Table7]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Edwin Knoppert]

A schema.ini is created in the csv directory with defaults.
To prevent using wrong settings prepare a new schema.ini in this folder.
This was my 'caching' problem.

It's fairly simple.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Forget it, it seem some caching of the csv file.
Seems to work now, now fixing the caching somehow

Any chance the fieldnames will get inserted by TEXT (csv)?

I see them in Excel and MDB, not in csv.

Thanks,

PS, already tried HDR = YES

On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert"

¤ I'm creating XLS and XSV (TEXT) through ADO itself.
¤ Not via excel (com-interface etc)
¤
¤ Since both are connection objects i was hoping i could use SQL
statements
¤ for these.
¤ CSV is extremely slow with record by record export.
¤ (INSERT INTO per record)
¤

Below is an example that exports from Access to Excel. It creates a new
Worksheet in the Workbook.
It's also possible to export into an existing Worksheet, if necessary,
by modifying the SQL
statement.

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

AccessConn.Open()

'New sheet in Workbook
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT *
INTO [Excel
8.0;DATABASE=e:\My Documents\BookTest.xls;HDR=NO;].[Sheet7] from
[Table7]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Edwin Knoppert]

Any chance the query can append records without opening the target?
It seems i can do this INSERT INTO once and then have to open the target for
appending??

Due the complex query i use i have to break it up in batches (really)
I wanted to prevent the 2nd and more appending queries to insert specific
rows if a userid already exists.
Possibly i can reset the field attrribute to allow only unique id's but
probably only after the 1st export batch.

I'm copying users with several zipcode ranges, even overlapping ranges.
The query got to complex to interpret by the system.
There can be more than 50 from-to ranges.

I know.. SQL server and stored procedures.. but not today..

 

[Edwin Knoppert]

I rewrote my code to do this all in a local temp table and then export it
and drop this table agin.
I'm not so satisfied though.

 

[Edwin Knoppert]

(+Using a transaction)

 

[Paul Clement]

¤ Any chance the query can append records without opening the target?
¤ It seems i can do this INSERT INTO once and then have to open the target for
¤ appending??
¤
¤ Due the complex query i use i have to break it up in batches (really)
¤ I wanted to prevent the 2nd and more appending queries to insert specific
¤ rows if a userid already exists.
¤ Possibly i can reset the field attrribute to allow only unique id's but
¤ probably only after the 1st export batch.
¤
¤ I'm copying users with several zipcode ranges, even overlapping ranges.
¤ The query got to complex to interpret by the system.
¤ There can be more than 50 from-to ranges.
¤
¤ I know.. SQL server and stored procedures.. but not today..
¤

Not sure if I quite understand your question but if you want to perform subsequent INSERTs the
answer is yes. Perhaps you could be a bit more specific and identify the type of database export you
are now working with?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Edwin Knoppert]

I had this excellent example for exporting data from my database connection
(MDB) to another (empty) database and Excel etc.. (see in this thread)

This all works fine but except it does't like multiple copies using this
technique.
So i rewrote it today to to first prepare a temporary table.
I'm reusing the SELECT and FROM parts, first the SELECT INTO and then
multiple INSERT INTO's
The best for me is indeed subsequent exports to a secundairy file and avoid
the temporary table and transaction stuff.
Then the temp table is copied to the new empty db and then removed again.

I think i only need a way to prepare the INSERT INTO in such a way it can
copy (append!) the data to the secundairy database.
Point is that so far i had no need to open the 2nd database and want to
avoid that.

The 2nd database is currently for this system always an MDB and serves as
intermediate file.
The actual export to (another) MDB or Excel or CSV is on demand (downloading
a subset by customers)
This part is very fast since it's a simply SELECT * INTO.., no worries about
that.

Even if the export query could be fixed i will at some point bang the wall
again therefore i don't mind the multiple inserts that much.
(While it's now slower than before unf.)

 

[Edwin Knoppert]

Sorry Paul, it was you who gave me this supurb SQL help, overlooked that

 

[Paul Clement]

¤ I had this excellent example for exporting data from my database connection
¤ (MDB) to another (empty) database and Excel etc.. (see in this thread)
¤
¤ This all works fine but except it does't like multiple copies using this
¤ technique.
¤ So i rewrote it today to to first prepare a temporary table.
¤ I'm reusing the SELECT and FROM parts, first the SELECT INTO and then
¤ multiple INSERT INTO's
¤ The best for me is indeed subsequent exports to a secundairy file and avoid
¤ the temporary table and transaction stuff.
¤ Then the temp table is copied to the new empty db and then removed again.
¤
¤ I think i only need a way to prepare the INSERT INTO in such a way it can
¤ copy (append!) the data to the secundairy database.
¤ Point is that so far i had no need to open the 2nd database and want to
¤ avoid that.

I don't know of any way to export without opening the database. Even if you include the connection
string in the SQL statement you're still opening the file to which you are exporting. However,
including the connection string in the SQL statement is just as possible with an INSERT INTO as it
is with a SELECT INTO, if that is what you are referring to.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Edwin Knoppert]

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.

Thanks so far!