[AccessD] Create Temp Table from Recordset

MessageMy code creates the table in the FE, which, if it's a temp table, is where you want it. Yes?

I didn't do anything special - it just seems to be the default.  Wait.  I SET db=CurrentDb which I believe points to the front end.  Are you setting the db that you run the execute against to the FE or the BE.  The table should end up wherever the db is pointing.  I think...

Rocky


Rocky

  ----- Original Message ----- 
  From: ACTEBS 
  To: accessd at databaseadvisors.com 
  Sent: Monday, June 09, 2003 8:34 AM
  Subject: RE: [AccessD] Create Temp Table from Recordset


  Rocky,

  But how did you tell it which DB you wanted to create the table in? I tried the db.execute....method but it created the table in the BE which is something I don't want. That's why I stopped using the SELECT ....IN clauses and just created the recordset hoping I could save it locally as a table. The reason is, the users can save the FE anywhere and run it so the IN portion of the Make Table syntax won't work. 

  Vlad
    -----Original Message-----
    From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin - Beach Access Software
    Sent: Tuesday, 10 June 2003 1:20 AM
    To: accessd at databaseadvisors.com
    Subject: Re: [AccessD] Create Temp Table from Recordset


    Vlad:

    I just did this by creating a make table query in the QBE and copying out the SQL code.  I don't know if you can use straight SQL to populate your table or you need to process through code.  I used db.execute instead of DoCmd.RunSQL to run the SQL so I don't have to Set Warnings False.  If you want to get rid of the table afterwards - DROP tbl.

    I don't know that if your make table SQL doesn't return any records, it will create the table anyway as an empty table but I think it will.


    HTH

    Rocky

      ----- Original Message ----- 
      From: ACTEBS 
      To: access group 
      Sent: Monday, June 09, 2003 7:48 AM
      Subject: [AccessD] Create Temp Table from Recordset


      Guys,

      Is there a method of creating a temp table in your DB from a recordset? eg:

      dim strSQL as string
      dim db as dao.database
      dim rst as dao.recordset

      strSQL = "SELECT someTable etc etc etc"

      set db = opendatabase("some remote DB")
      set rst =db.openrecordset(strsql)

      with rst
          make a new table in my db from the recordset
      end with

      I've never had to do this and have wasted heaps of time on this. Any help will be much appreciated...

      Thanks
      Vlad



--------------------------------------------------------------------------


      _______________________________________________
      AccessD mailing list
      AccessD at databaseadvisors.com
      http://databaseadvisors.com/mailman/listinfo/accessd
      Website: http://www.databaseadvisors.com



------------------------------------------------------------------------------


  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030609/e1be65ca/attachment-0001.html>