How to COPY a recordset to an new table in a new database

Quick and easy way to do it:

Dim DB As Database
Dim SQL As String

Set DB = DBEngine.OpenDatabase(c:\OLD.mdb , , True)
SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM Orders"
DB.Execute (SQL)
DB.Close
Set DB = Nothing

You may have to create the Archieve table with same structure like Orders
table beforehand.

"Info Online4u" <info DeleteThis @online4u.be> wrote in message
news:%23NLCCXq4DHA.2760@TK2MSFTNGP09.phx.gbl...
 > Suppose I have a Database 'db' (x.mdb) and a filtered recordset 'rs'
(select
 > * from myTable where .....)
 >
 > How can I COPY all data from rs to a new table 'newtbl' in a new database
 > 'newdb.mdb' ???
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: How to COPY a recordset to an new table in a new database 

Seems the SQL
SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM Orders WHERE x='y'"
does not work.
Without the where it works, but I want to save a 'filtered' recordset.
How ?

Juan


"Phil Hunt" <phung.TakeThisOut@ktintl.com> schreef in bericht
news:uT6H2eD5DHA.2540@TK2MSFTNGP11.phx.gbl...
 > Quick and easy way to do it:
 >
 > Dim DB As Database
 > Dim SQL As String
 >
 > Set DB = DBEngine.OpenDatabase(c:\OLD.mdb , , True)
 > SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM Orders"
 > DB.Execute (SQL)
 > DB.Close
 > Set DB = Nothing
 >
 > You may have to create the Archieve table with same structure like Orders
 > table beforehand.
 >
 > "Info Online4u" <info.TakeThisOut@online4u.be> wrote in message
 > news:%23NLCCXq4DHA.2760@TK2MSFTNGP09.phx.gbl...
  > > Suppose I have a Database 'db' (x.mdb) and a filtered recordset 'rs'
 > (select
  > > * from myTable where .....)
  > >
  > > How can I COPY all data from rs to a new table 'newtbl' in a new
database
  > > 'newdb.mdb' ???
  > >
  > >
  > >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: How to COPY a recordset to an new table in a new database 

I thought you were gone. I am surprised that it does not work with the
where.
You have to do it 2 steps.
SQL = "INSERT INTO temptable SELECT * FROM Orders where x='y'"
SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM temptable"

It works, but you have to make sure the temptable is clear before the first
SQL.

"Info Online4u" <info RemoveThis @online4u.be> wrote in message
news:%23VtKEZy5DHA.1816@TK2MSFTNGP12.phx.gbl...
 > Seems the SQL
 > SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM Orders WHERE
x='y'"
 > does not work.
 > Without the where it works, but I want to save a 'filtered' recordset.
 > How ?
 >
 > Juan
 >
 >
 > "Phil Hunt" <phung RemoveThis @ktintl.com> schreef in bericht
 > news:uT6H2eD5DHA.2540@TK2MSFTNGP11.phx.gbl...
  > > Quick and easy way to do it:
  > >
  > > Dim DB As Database
  > > Dim SQL As String
  > >
  > > Set DB = DBEngine.OpenDatabase(c:\OLD.mdb , , True)
  > > SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM Orders"
  > > DB.Execute (SQL)
  > > DB.Close
  > > Set DB = Nothing
  > >
  > > You may have to create the Archieve table with same structure like
Orders
  > > table beforehand.
  > >
  > > "Info Online4u" <info RemoveThis @online4u.be> wrote in message
  > > news:%23NLCCXq4DHA.2760@TK2MSFTNGP09.phx.gbl...
   > > > Suppose I have a Database 'db' (x.mdb) and a filtered recordset 'rs'
  > > (select
   > > > * from myTable where .....)
   > > >
   > > > How can I COPY all data from rs to a new table 'newtbl' in a new
 > database
   > > > 'newdb.mdb' ???
   > > >
   > > >
   > > >
  > >
  > >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: How to COPY a recordset to an new table in a new database 

I changed it
Like this it works.


'Procedure to (re)make new.mdb and table archive
.....

Set db = DBEngine.OpenDatabase("old.mdb")
' Table tmp MAY NOT exist if using select into tmp, so you must delete table
tmp
' If table tmp does not exist you'll get an error, therefor resume next.
On error resume next
db.Execute "DROP TABLE tmp"
db.TableDefs.Refresh
On error goto MyError
SQL = "SELECT * INTO tmp FROM Orders WHERE x='y'"
db.execute(SQL)
Set db1 = DBEngine.OpenDatabase("c:\new.mdb")
SQL = "INSERT INTO archive IN c:\new.mdb SELECT * FROM tmp"
db.execute(SQL)
db.Close
db1.Close
Set db = Nothing
Set db1 = Nothing

thx for the tip
Juan


"Phil Hunt" <phung.TakeThisOut@ktintl.com> schreef in bericht
news:OtD0ry05DHA.2720@TK2MSFTNGP09.phx.gbl...
 > I thought you were gone. I am surprised that it does not work with the
 > where.
 > You have to do it 2 steps.
 > SQL = "INSERT INTO temptable SELECT * FROM Orders where x='y'"
 > SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM temptable"
 >
 > It works, but you have to make sure the temptable is clear before the
first
 > SQL.
 >
 > "Info Online4u" <info.TakeThisOut@online4u.be> wrote in message
 > news:%23VtKEZy5DHA.1816@TK2MSFTNGP12.phx.gbl...
  > > Seems the SQL
  > > SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM Orders WHERE
 > x='y'"
  > > does not work.
  > > Without the where it works, but I want to save a 'filtered' recordset.
  > > How ?
  > >
  > > Juan
  > >
  > >
  > > "Phil Hunt" <phung.TakeThisOut@ktintl.com> schreef in bericht
  > > news:uT6H2eD5DHA.2540@TK2MSFTNGP11.phx.gbl...
   > > > Quick and easy way to do it:
   > > >
   > > > Dim DB As Database
   > > > Dim SQL As String
   > > >
   > > > Set DB = DBEngine.OpenDatabase(c:\OLD.mdb , , True)
   > > > SQL = "INSERT INTO Archive in c:\NEW.mdb SELECT * FROM Orders"
   > > > DB.Execute (SQL)
   > > > DB.Close
   > > > Set DB = Nothing
   > > >
   > > > You may have to create the Archieve table with same structure like
 > Orders
   > > > table beforehand.
   > > >
   > > > "Info Online4u" <info.TakeThisOut@online4u.be> wrote in message
   > > > news:%23NLCCXq4DHA.2760@TK2MSFTNGP09.phx.gbl...
   > > > > Suppose I have a Database 'db' (x.mdb) and a filtered recordset 'rs'
   > > > (select
   > > > > * from myTable where .....)
   > > > >
   > > > > How can I COPY all data from rs to a new table 'newtbl' in a new
  > > database
   > > > > 'newdb.mdb' ???
   > > > >
   > > > >
   > > > >
   > > >
   > > >
  > >
  > >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: How to COPY a recordset to an new table in a new database