Results 1 to 3 of 3
  1. #1
    mgilbert86 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    9

    Question Export Select Query Results as Table to New Database

    Hello,

    I'm trying to find out if it is possible within VBA in Access to export the results of a select type query in x database and place/import them as a new table in y database.

    I do not want to copy the query design / the query its self but the results.

    Currently I have found the following code and am led to believe this works but can't get the syntax right.

    Set dbs = OpenDatabase("C:\*X_database_location*")


    dbs.Execute "SELECT queryname.* INTO IN 'C:\*Y_database_location*'" FROM queryname, dbFailOnError

    Can you please help?

    Thanks,



    Michael

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,684
    Should be. I have code that inserts records into existing tables of other db. This avoids the issue of having to constantly delete and recreate tables in other db. The tables are established and I just replace the records.

    'exports data to ConstructionExtract Access file
    Dim strExtract As StringstrExtract = gstrBasePath & "Program\Editing\ConstructionExtract.accdb"
    'delete records from ConstructionExtract tables
    CurrentDb.Execute "DELETE FROM SampleInfo IN '" & strExtract & "'"
    'insert records into ConstructionExtract tables
    CurrentDb.Execute "INSERT INTO SampleInfo IN '" & strExtract & "' SELECT * FROM ConstructionSampleInfo;"

    It is not even necessary to declare and set database object variable.

    However, if you really need to create a new table each time, try:

    CurrentDb.Execute "SELECT queryname.* INTO IN 'C:\*Y_database_location*' FROM queryname", dbFailOnError

    Note the relocation of second quote mark to end of sql statement.

    I presume you substitute actual db path and name for 'C:\*Y_database_location*'.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,897
    attach the table from the outside db, then append the records to this table.
    (same as an export)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  2. Select Query returning no Results
    By Rhemo in forum Access
    Replies: 2
    Last Post: 09-15-2012, 04:11 AM
  3. Replies: 1
    Last Post: 04-30-2012, 05:10 PM
  4. Replies: 5
    Last Post: 02-27-2012, 08:37 PM
  5. Replies: 11
    Last Post: 12-14-2010, 01:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums