Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INSERT INTO syntax (A2K3)

    I need to use the values from a SQL statement in a new SQL INSERT INTO Statement. How can I do that?

    Here is my current code:
    <hr>Private Sub cmdSelect_Click()
    Dim SQL As String
    Dim strSQL As String
    Dim sWhere As String
    Dim iCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim iSelectHowMany As Integer
    Dim aSel() As Integer
    Dim IsRepeated As Boolean
    Dim vItem As Variant
    Dim tdf As DAO.TableDef
    Dim Fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strTableName As String

    iSelectHowMany = 25
    DoCmd.SetWarnings False
    '********************************
    DoCmd.OpenQuery "qry_DeleteArchive"
    DoCmd.OpenQuery "qry_RandomAudits"

    Call AddCounter("tbl_temp", "Audit_ID")

    DoCmd.OpenQuery "qry_RunAudit"
    '************************************

    SQL = "Select * from [tbl_Audit_Archive]"

    ' Find number of records in table.
    iCount = DCount("*", "tbl_Audit_Archive")

    Randomize
    i = Int(iCount * Rnd()) + 1
    ReDim aSel(0)
    aSel(0) = i

    j = 1
    Do While j < iSelectHowMany
    IsRepeated = False
    Do While Not IsRepeated
    i = Int(iCount * Rnd()) + 1

    For Each vItem In aSel
    If vItem = i Then
    IsRepeated = True
    Exit For
    End If
    Next vItem

    If Not IsRepeated Then
    ReDim Preserve aSel(j)
    aSel(j) = i
    j = j + 1
    Exit Do
    End If
    Loop
    Loop

    For Each vItem In aSel
    sWhere = sWhere & ", " & vItem
    Next
    SQL = SQL & " where [Audit_ID] in (" & Mid(sWhere, 2) & ")"

    Me.subform.Form.RecordSource = SQL

    DoCmd.RunSQL "INSERT INTO myAudits(Member_id, Load_Date) SELECT Member_ID, Load_Date FROM SQL"
    DoCmd.SetWarnings True
    End Sub
    <hr>
    I get the "can not find the input table or query SQL" message.

    What am I doing wrong now? PLEASE HELP!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: INSERT INTO syntax (A2K3)

    You have placed the text SQL within the string, so the Jet Engine thinks that you want to get records from a table named SQL.
    Moreover, nested SQL must be enclosed in quotes. Try

    DoCmd.RunSQL "INSERT INTO myAudits(Member_id, Load_Date) SELECT Member_ID, Load_Date FROM (" & SQL & ")"

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO syntax (A2K3)

    Thanks Hans.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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