Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    79

    Object Required Error On Insert Into Statement

    Hi all,

    Using Access 2007.

    Trying to write some VBA to append user-selected tables to comaprison tables.

    My code is bombing here at db.Execute
    Code:
     'Previous month
         sSQL = ""
         sSQL = sSQL & "INSERT INTO tbl_PartsPrevious (PT, Part) "
         sSQL = sSQL & "SELECT " & sPTable & ".PT, " & sPTable & ".Part "
         sSQL = sSQL & "FROM " & sPTable & ";"
         
         Debug.Print sSQL
         
         db.Execute (sSQL)
    I wrote an append query using the query designer. That SQL looks very much like the SQL above.

    The table exists, the spelling is correct as are the names of the fields.
    Here is the SQL string that Debug.Pring prints to the immediate window

    INSERT INTO tbl_PartsPrevious (PT, Part) SELECT tblParts1209.PT, tblParts1209.Part FROM tblParts1209;
    Do you see anything wrong?
    Thx
    w

    Full code:
    Code:
    Option Compare Database
    
    Sub CompareMonths()
    
        'Environment
         DoCmd.SetWarnings False
         
        'Variables
         Dim sPTable As String
         Dim sCTable As String
         Dim sSQL As String
         
        'Clear comparison tables
         DoCmd.OpenQuery "qdel_tblPartsPrevious_Clear"
         DoCmd.OpenQuery "qdel_tblPartsCurrent_Clear"
         
        'Get selected table name
         sPTable = Forms!Parts!lstPMonth.Value 'Previous Month
         sCTable = Forms!Parts!lstCMonth.Value 'Current Month
         
        'Append selected tables to comparison tables
         strStatus = "Appending records..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
         
         
        'Previous month
         sSQL = ""
         sSQL = sSQL & "INSERT INTO tbl_PartsPrevious (PT, Part) "
         sSQL = sSQL & "SELECT " & sPTable & ".PT, " & sPTable & ".Part "
         sSQL = sSQL & "FROM " & sPTable & ";"
         
         Debug.Print sSQL
         
         db.Execute (sSQL)
         
        'Current month
         sSQL = ""
         sSQL = sSQL & "INSERT INTO tbl_PartsCurrent (PT, Part) "
         sSQL = sSQL & "SELECT " & sCTable & ".PT, " & sPTable & ".Part "
         sSQL = sSQL & "FROM " & sCTable & ";"
         db.Execute (sSQL)
     
     
        varStatus = SysCmd(acSysCmdClearStatus)
    
    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    You never declare or set db.
    Paul

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    By the way, you don't need the SetWarnings line since the Execute method doesn't throw them. If you decide to keep it, you should always turn them back on.
    Paul

  4. #4
    Join Date
    Feb 2012
    Posts
    79
    Thanks Paul,

    Works great now!

    Appreciate your help
    w

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    Happy to help!
    Paul

Posting Permissions

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