|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Copy all records from access MDB to others via ADONETI 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.. On Wed, 12 Jul 2006 14:04:41 +0200, "Edwin Knoppert" <n***@hellobasic.com> wrote: ¤ 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) 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) Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:0m7ab2db48sh7jtov4430b5vrsmev8d2ke@4ax.com... > On Wed, 12 Jul 2006 14:04:41 +0200, "Edwin Knoppert" <n***@hellobasic.com> > wrote: > > ¤ 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) 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. -- Show quote____________________________________ 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***@pbsoft.speedlinq.nl> wrote in message news:e93eou$dmt$1@azure.qinip.net... > 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) > > > > > > "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in > bericht news:0m7ab2db48sh7jtov4430b5vrsmev8d2ke@4ax.com... >> On Wed, 12 Jul 2006 14:04:41 +0200, "Edwin Knoppert" >> <n***@hellobasic.com> wrote: >> >> ¤ 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) > > 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. Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> schreef in bericht news:OA4O5IfpGHA.4032@TK2MSFTNGP03.phx.gbl... > 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***@pbsoft.speedlinq.nl> wrote in message > news:e93eou$dmt$1@azure.qinip.net... >> 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) >> >> >> >> >> >> "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in >> bericht news:0m7ab2db48sh7jtov4430b5vrsmev8d2ke@4ax.com... >>> On Wed, 12 Jul 2006 14:04:41 +0200, "Edwin Knoppert" >>> <n***@hellobasic.com> wrote: >>> >>> ¤ 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) >> >> > > On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert" <i***@pbsoft.speedlinq.nl> wrote: ¤ 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) Thanks!!
I'll try tomorrow.. :) If this works i may assume speed is much better this way :)Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:f7icb25jgcmtlrhr7k9kcrt5fj0ubb14k0@4ax.com... > On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert" > <i***@pbsoft.speedlinq.nl> wrote: > > ¤ 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) Super!!
MDB, Excel and CSV (TEXT) export now works instantly! Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:f7icb25jgcmtlrhr7k9kcrt5fj0ubb14k0@4ax.com... > On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert" > <i***@pbsoft.speedlinq.nl> wrote: > > ¤ 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) 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 Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:f7icb25jgcmtlrhr7k9kcrt5fj0ubb14k0@4ax.com... > On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert" > <i***@pbsoft.speedlinq.nl> wrote: > > ¤ 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) Forget it, it seem some caching of the csv file.
Seems to work now, now fixing the caching somehow :) Show quote "Edwin Knoppert" <n***@hellobasic.com> schreef in bericht news:44b79e5e$0$2028$ba620dc5@text.nova.planet.nl... > 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 > > > "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in > bericht news:f7icb25jgcmtlrhr7k9kcrt5fj0ubb14k0@4ax.com... >> On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert" >> <i***@pbsoft.speedlinq.nl> wrote: >> >> ¤ 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) > > 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 Show quote "Edwin Knoppert" <n***@hellobasic.com> schreef in bericht news:44b7a2ba$0$2021$ba620dc5@text.nova.planet.nl... > Forget it, it seem some caching of the csv file. > Seems to work now, now fixing the caching somehow :) > > > "Edwin Knoppert" <n***@hellobasic.com> schreef in bericht > news:44b79e5e$0$2028$ba620dc5@text.nova.planet.nl... >> 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 >> >> >> "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in >> bericht news:f7icb25jgcmtlrhr7k9kcrt5fj0ubb14k0@4ax.com... >>> On Wed, 12 Jul 2006 20:24:34 +0200, "Edwin Knoppert" >>> <i***@pbsoft.speedlinq.nl> wrote: >>> >>> ¤ 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) >> >> > > 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. -- Show quote____________________________________ 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" <n***@hellobasic.com> wrote in message news:44b4e57d$0$2022$ba620dc5@text.nova.planet.nl... > 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.. > > 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.. 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. Show quote "Edwin Knoppert" <n***@hellobasic.com> schreef in bericht news:44bcb215$0$2032$ba620dc5@text.nova.planet.nl... > 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.. > > (+Using a transaction)
Show quote "Edwin Knoppert" <n***@hellobasic.com> schreef in bericht news:44bcb215$0$2032$ba620dc5@text.nova.planet.nl... > 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.. > > On Tue, 18 Jul 2006 12:02:58 +0200, "Edwin Knoppert" <n***@hellobasic.com> wrote: ¤ 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) 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.) Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:3n7qb29kvc0cvrtl1n25lkb8tmk7d8vp38@4ax.com... > On Tue, 18 Jul 2006 12:02:58 +0200, "Edwin Knoppert" <n***@hellobasic.com> > wrote: > > ¤ 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) On Tue, 18 Jul 2006 22:05:17 +0200, "Edwin Knoppert" <i***@pbsoft.speedlinq.nl> wrote: ¤ 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) 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! Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:jfnsb2lo8pt1e65q7ikvhhuarpm6qn5ek3@4ax.com... > On Tue, 18 Jul 2006 22:05:17 +0200, "Edwin Knoppert" > <i***@pbsoft.speedlinq.nl> wrote: > > ¤ 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) On Wed, 19 Jul 2006 19:26:35 +0200, "Edwin Knoppert" <i***@pbsoft.speedlinq.nl> wrote: ¤ 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) 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, Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:5rvsb2ttrs9u1blldosvvidc61qu25o3e6@4ax.com... > On Wed, 19 Jul 2006 19:26:35 +0200, "Edwin Knoppert" > <i***@pbsoft.speedlinq.nl> wrote: > > ¤ 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) On Wed, 19 Jul 2006 21:29:07 +0200, "Edwin Knoppert" <i***@pbsoft.speedlinq.nl> wrote: ¤ 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) Look at that ! :)
Also the website is super! Will bookmark it. Thanks! Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:gq3vb2hm6mr2n2op6851342ctcp62irc2m@4ax.com... > On Wed, 19 Jul 2006 21:29:07 +0200, "Edwin Knoppert" > <i***@pbsoft.speedlinq.nl> wrote: > > ¤ 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) Sorry Paul, it was you who gave me this supurb SQL help, overlooked that :)
Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> schreef in bericht news:3n7qb29kvc0cvrtl1n25lkb8tmk7d8vp38@4ax.com... > On Tue, 18 Jul 2006 12:02:58 +0200, "Edwin Knoppert" <n***@hellobasic.com> > wrote: > > ¤ 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)  |
|||||||||||||||||||||||
Other interesting topics