Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    100

    Unanswered: problem with insert into query

    hello,

    I am having problems with the following query

    strQuery = " INSERT INTO CLOSED_TRACKING SELECT TRACKING.* " & _
    " FROM TRACKING, CLOSED_TRACKING " & _
    " WHERE (TRACKING.STATUS = 'closed' );"

    The query works fine if there are already records in the closed_tracking table.
    However if the closed_tracking table is empty, Access throws an error.
    Does anyone know about that problem?
    regards Proximus

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    by empty do you mean non-existant? if so, you need to use SELECT INTO instead.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Apr 2004
    Posts
    100
    the closed_tracking table is existant. However there are no records in it.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    How are the two tables connected? Inner Join, Left Join or Right Join?

  5. #5
    Join Date
    Apr 2004
    Posts
    100
    there is no relationship between these tables at all.
    however both tables are absolute identical, since the cosed_tracking table just serves as a backup table for the tracking table.

  6. #6
    Join Date
    Apr 2004
    Posts
    100
    does no one have any idea how to solve that?

  7. #7
    Join Date
    Sep 2004
    Posts
    161
    I dont understand what you make with your query, i have test it and i have this result :
    strquery = " INSERT INTO Table1 SELECT Table2.* " & _
    " FROM Table2, Table3 " & _
    " WHERE (Table2.Test2 = 'closed' );"
    DoCmd.RunSQL strquery

    In Table 2
    R closed
    U
    In table 3
    B closed
    A closed
    after run the query in table 1
    R closed
    R closed
    ??????????????

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I don't know how to fix the problem. but you can suppress the error if you want.
    Code:
    On Error Resume Next
    	 [Possible error producing code here...]
    On Error Goto 0
    ghozy.

  9. #9
    Join Date
    Apr 2004
    Posts
    100
    isn't it possible to copy records from one table to another. The tables are absolutely identical! There is no relationship between both tables. However when I run this query there are no records copied as long as there isn't at least one record in the destinatin table (closed_tracking). How can I solve that problem so that the records are copied even if there is no record in the destination table. Any help would be greatly appreciated since I really have now idea.


    Set cn = CurrentProject.Connection
    'copy records
    Set cn = CurrentProject.Connection
    strQuery = " INSERT INTO CLOSED_TRACKING SELECT TRACKING.* " & _
    " FROM TRACKING, CLOSED_TRACKING " & _
    " WHERE (TRACKING.STATUS = 'closed' );"
    rs.Open strQuery, cn

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Geez!!! Look at your FROM ... That's the error. [In english] You're trying to insert into Table A FROM Table A AND Table B ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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