Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Into *** Select ** from (Access XP)

    I have written a script to insert details from a table (assets) into a history table (telecomsupdates). As below

    strSQLUpdate = "insert into telecomsupdates " & _
    " select a.assetno, '" & txtAssetNo & "', t.simcardno, " & _
    " a.userid, getdate(), NULL, " & intTUno & " , a.office, " & _
    " a.area" & _
    " from assets a join telecoms t on a.assetno = t.assetno " & _
    " where a.assetno = '" & strMobile & "'"

    docmd.runsql strSQLUpdate

    When I run the docmd.runsql I get the following message:

    Run time error '3352'
    No destination field name in INSERT INTO Statement
    '449195584765070'

    When I run a debug.print the script is:

    insert into telecomsupdates
    select a.assetno, '4491915584765070', t.simcardno, a.userid, getdate(), NULL, 9 , a.office, a.area
    from assets a join telecoms t on a.assetno = t.assetno where a.assetno = '07810 757449'

    and in query analyser this inserts correctly.

    I am now a little perplexed, if someone can tell me what I've done wrong I would really appreciate it.

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

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

    Re: Insert Into *** Select ** from (Access XP)

    You'll have to specify to which field txtAssetNo, GetDate(), Null and intTUno have to be written. You can do this in one of two ways. I have used names fieldname1, fieldname2, fieldname3 and fieldname4 because I don't know (just like Access) what the names are.

    1. Specify all destination fields explicitly:

    strSQLUpdate = "insert into telecomsupdates " & _
    "( AssetNo, fieldname1, SimCardNo, UserID, fieldname2, " & _
    " fieldname3, fieldname4, Office, Area )" & _

    " select a.assetno, '" & txtAssetNo & "', t.simcardno, " & _
    " a.userid, getdate(), NULL, " & intTUno & " , a.office, " & _
    " a.area" & _
    " from assets a join telecoms t on a.assetno = t.assetno " & _
    " where a.assetno = '" & strMobile & "'"

    2. Assign aliases for literal values:

    strSQLUpdate = "insert into telecomsupdates " & _
    " select a.assetno, '" & txtAssetNo & "' As fieldname1, " & _
    " t.simcardno, a.userid, getdate() As fieldname2, " & _
    " NULL As fieldname3, " & intTUno & " As fieldname4, " & _
    " a.office, a.area" & _
    " from assets a join telecoms t on a.assetno = t.assetno " & _
    " where a.assetno = '" & strMobile & "'"

Posting Permissions

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