[RESOLVED] SQL Question

[manofsteel00]

I have some SQL code that will work if I put it in a query in MS Access, however, it will not work if I try to do it in VB6 as a string. Can anyone help me with this; I'd be greatly appreciative.

'*INSERT()************************************************************************
'NAME: INSERT()
'DESC:  Inserts data from the TEST table and the PICKSL table into the MAIN table
Private Sub insert()
    Dim sql As String
    
    Set newConnection = New ADODB.connection
    newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Documents and Settings\p6b0438\My Documents\CycleCount Test\CycleCount\DB\cycleCount.mdb"
    newConnection.Open
        
    sql = "INSERT INTO MAIN ( PICKSL, SLOT, RES, PROD, [DESC], PACK, " & _
          "[MANU ID], HITIX, [PALLET ID], [PALLET QTY], DFP ) " & _
          "SELECT t.PICKSL, t.SLOT, t.RES, t.PROD, t.DESC, t.PACK, " & _
          "t.[MANU ID], t.HITIX, t.[PALLET ID], t.[PALLET QTY], t.DFP " & _
          "FROM Test AS t " & _
          "WHERE Not Exists ( " & _
            "SELECT * " & _
            "FROM [ALTER] a " & _
            "WHERE  a.PROD = t.PROD);"
 
    newConnection.Execute (sql)
    
    sql = "INSERT INTO MAIN ( PICKSL, SLOT, PROD, [DESC], PACK, " & _
          "[MANU ID], HITIX, [PALLET QTY] ) " & _
          "SELECT p.PICKSL, p.PICKSL, p.PROD, p.DESC, p.PACK, " & _
          "p.MANUID, p.HITI, p.OH " & _
          "FROM PICKSL AS p " & _
          "WHERE Not Exists ( " & _
            "SELECT * " & _
            "FROM [ALTER] a " & _
            "WHERE  a.PROD = p.PROD);"
 
    newConnection.Execute (sql)
    
    newConnection.Close
    Set newConnection = Nothing
End Sub
'*ENDOF*INSERT()***************************************************************