[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()***************************************************************

[brucevde]

The syntax is right, assuming all field and table names are correct.
What errors, if any, are you getting? Do both queries fail?

[manofsteel00]

I'm not getting any errors; however, it does not insert the data from the TEST and ALTER tables into the MAIN table. And my program runs off the MAIN table. Any suggestions would be greatly appreciated. Thank you.

[manofsteel00]

CORRECTION: I'm sorry it does not insert the data from the TEST and PICKSL tables into the MAIN table.

[brucevde]

I would need a copy of the database to help resolve the issue.

[manofsteel00]

I can email it to you if you would like

[Hack]

I can email it to you if you would like

Just zip it and attach it.

[manofsteel00]

I had to scale down the database to attach it.

[Fishy]

Hi,
I Just inserted this into a form and it Updates Ok.

Private Sub CmdUpdate_Click()
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\vbcodedownload\cycle\cyclecount.mdb"
Conn.Open
Dim MyCommand As ADODB.Command
Set MyCommand = New ADODB.Command
MyCommand.ActiveConnection = Conn
MyCommand.CommandText = "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)"
Debug.Print MyCommand.CommandText
MyCommand.Execute
End Sub

[manofsteel00]

ok I'll check. Thank you.

[brucevde]

I'm not getting any errors; however, it does not insert the data from the TEST and PICKSL tables into the MAIN table.

I downloaded your database and using your code was able to insert the records into main with no problems. Sorry, no help at all and I am out of ideas.

Is there anything else accessing the database while this code is executing? Other connections? Is it possible to upload your entire program?

[salvelinus]

I've noticed that Access saved queries are treated differently than ones run from code. In my case, it's usually when checking for Null, but could be with your "Not Exists" type statement.
If I write a query in SQL design view with some correct SQL syntax & save it, it will run correctly. The same syntax in code may give an error. Going back to the saved query and checking SQL view, Access has changed the syntax for checking Nulls, in my case. Not at work, so can't give you the exact problem, but it's something to do with IsNull(Foo) vs. Not Null vs IsNull(Foo) = False, etc.

[dee-u]

As fishy has tried, what happens if you only try to insert one record at a time?

[manofsteel00]

Thanks for everyone's help; Fishy's code worked. Thanks again.