Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving Results of a query to a table (Access 2000)

    I want to have a button on a form that has its control source set to a query. That button will store the results of the query to a table. How do I do this?

    Mike

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Saving Results of a query to a table (Access 2000)

    To create a new table you use a make-table query - you can experiment with that query type using the Query Designer - take your existing query that serves as the source of the form and save it as a make table query. Of course if you are letting the user put in parameters, then you need to modify the query based on those parameters. You can do that by changing the QueryDef, but most of the time developers choose to assemble the query string in VBA and then execute it. I hope this overview gives you some idea of the process - if you need detailed assistance, provide us with more information on the query and the form it servers and someone can give you some detailed steps.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Results of a query to a table (Access 2000)

    Sorry about the lack of info. I have developed the query string in VBA. I have a form where the user selects parameters to search on. The user then clicks on the "Search" button and another form opens with its recordsource being the results of the VBA developed Query String based on the Search form parameters. I now need to be able to save the results of the query in a table.

    Mike

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Results of a query to a table (Access 2000)

    What if this has been done before? Should the records be saved in a new table with a unique name, or should the existing table be overwritten, or should the records be appended to the existing table?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Results of a query to a table (Access 2000)

    The existing tables should be overwritten.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Results of a query to a table (Access 2000)

    Tables (plural)?

  7. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Results of a query to a table (Access 2000)

    Not Plural.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Results of a query to a table (Access 2000)

    OK. Best create the empty table manually, to set the field properties the way you want. You have to do this only once; perhaps you can simply copy and paste an existing table and specify that you want to copy the structure only. Let's say this table is named tblTarget.

    You assemble an SQL string in code to serve as record source for a form; it will probably look like SELECT ... FROM ... WHERE ... ORDER BY ...
    You store this SQL in a variable strSQLSelect. You can use it as follows:

    Dim strSQLDelete As String
    Dim strSQLInsert As String

    ' Delete existing records
    strSQLDelete = "DELETE * FROM tblTarget"
    DoCmd.RunSQL strSQLDelete

    ' Insert new records
    strSQLInsert = "INSERT INTO tblTarget " & strSQLSelect
    DoCmd.RunSQL strSQLInsert

    When you run this code, you'll get the usual warnings "You are about to execute an action query", "You are about to delete 37 records", etc. During testing, this is useful to get an idea of what the code is doing. Later on, you'll want to suppress these warnings. You can do this by inserting a line

    DoCmd.SetWarnings False

    before, and a line

    DoCmd.SetWarnings True

    after the code that deletes/inserts.

  9. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Results of a query to a table (Access 2000)

    Hans,

    Thanks. This helps tremendously.

    Mike

  10. #10
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Results of a query to a table (Access 2000)

    I got this code to work. I was wondering if it is possible to save into a variable the number of records that were just inserted into the table. Also how do you declare a variable so it can be accessed anywhere within the form, but neccessarily anywhere in the database?

    Mike

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Results of a query to a table (Access 2000)

    Declare a variable at the top of the form module, below Option Compare Database and Option Explicit, but before all Subs and Functions:

    Private lngRecordCount As Long

    Such a variable will be known in the entire form module, but not outside it.

    You can set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    You can then use code like this instead of the lines with DoCmd.RunSQL:

    Dim dbs As DAO.Database
    Set dbs = CurrentDb

    strSQLInsert = ...
    CurrentDb.Execute strSQLInsert
    lngRecordCount = CurrentDb.RecordsAffected

    etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •