Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    14

    Unanswered: insert into table sql

    Hi i have a command button on a form that inserts specified records based on a whereclause into a new table:
    LOCATION_QRY is a query:

    Dim SQL1 As String
    SQL1 = "INSERT INTO [TBLTEMP_LOC]" & _
    "SELECT LOCATION_QRY.*" & _
    "FROM [LOCATION_QRY]" & _
    Whereclause & _
    "ORDER BY [SECTION]"
    DoCmd.RunSQL (SQL1)

    This works fine

    Each record in the LOCATION_QRY contains 4 or 5 geology records. These geology records should go into a table called TBLTEMP_GEO. The records and geo records are linked by ID. So, I want to insert all geo records based on the ID of what is already in the TBLTEMP_LOC table. Like so:

    Dim SQL2 As String
    SQL2 = "INSERT INTO [TBLTEMP_GEO]" & _
    "SELECT GEOLOGY_QRY.*, [TBLTEMP_LOC].[ID1]" & _
    "FROM [GEOLOGY_QRY], [TBLTEMP_LOC]" & _
    "WHERE [GEOLOGY].[ID] = [TBLTEMP_LOCHEAD].[ID1]"

    ID and ID1 are text fields. The SQL2 works ok, it just takes a really long time. About 10 minutes. I dont think this is correct because SQL1 runs instantaneously. Also, if i want to append more records to the tables, the SQL2 will insert duplicates into the TBLTEMP_GEO. This is because it will insert the geo records for the records already in TBLTEMP_LOC and the ones that were just added by running SQL1. Any help would be appreciated. Thank you.

    Andrew

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Did you cut and paste that sql? Because you're using predicates in your where clause that don't exist in your second query...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006
    Posts
    14
    Its correct what I have. Here is the actual cut and pasted part:

    SQL1 = "INSERT INTO [TBLTEMP_LOCHEAD]" & _
    "SELECT LOCATION_HEADER.*" & _
    "FROM [LOCATION_HEADER]" & _
    Whereclause & _
    "ORDER BY [TWP], [TDIR], [RNG], [RDIR], [SECTION]"
    DoCmd.RunSQL (SQL1)

    SQL3 = "INSERT INTO [TBLTEMP_GEO]" & _
    "SELECT GEOLOGY.*, [TBLTEMP_LOCHEAD].[ID]" & _
    "FROM [GEOLOGY], [TBLTEMP_LOCHEAD]" & _
    "WHERE [GEOLOGY].[GEO_ID] = [TBLTEMP_LOCHEAD].[ID]"
    DoCmd.RunSQL (SQL3)

    I want to select the ID field in TBLTEMP_LOCHEAD and insert all the geo records that have the same id's as records in the TBLTEMP_LOCHEAD. In SQL1 the "Whereclause" is based on the search form criteria.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is TBLTEMP_LOCHEAD generated on the fly every time? If so, you should be using SELECT INTO as opposed to INSERT INTO. Also, INSERT INTO does unreliable things unless you specifically define the fields being inserted:

    INSERT INTO yourTable (field1, field2)
    SELECT yourField1, yourField2
    FROM yourTable2


    The order of columns and records id arbitrary and unreliable when you call "SELECT *". I wouldn't be surprised if that's what's making access crap itself.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2006
    Posts
    14

    Records are appended to the table

    Yea it sorta is generated on the fly. I need the INSERT INTO because I want the user to be able to append more records to the table after doing a search on the form. So, more records could be added. And then once the user is done with all the searches, I have a open report command button that has the TBLTEMP_LOCHEAD as the underlying table base. The report has a subreport with TBLTEMP_GEO as the underlying table for the subreport.

Posting Permissions

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