Insert into SQL server table issue from VB

[keithvp]

I am working on a VB interface to a SQL server that will be used to track contract requirements. I am taking data from two tables and inserting it into another table that has been created. All of my SQL statements are correct. I have tested them in Query Analyzer. When I try to do the same thing in the VB App, the Insert Into SQL query inserts 0 records. Here is the relevant code:

Global MainDB As Database
Global wrkODBC As Workspace

Public Sub OpenDB()

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set MainDB = wrkODBC.OpenConnection("", , , "ODBC;DATABASE=Contracts;UID=Fred;PWD=***;DSN=HALDB")

End Sub


Public Sub Inv_Create()
Dim ESQL1
Dim ESQL2
Dim ESQL3
ESQL1 = "If not Exists(select table_name from Information_schema.tables where table_name = 'tblInvoice') create table dbo.tblInvoice(InvoiceNumber Char(7) null, AcctNo Char(9) null, SOTransDate DateTime null, SOShipToCode char(4) null, SOItemNumber char(15) null, SOQtyShipped Decimal null)"
MainDB.Execute ESQL1
ESQL2 = "Truncate table dbo.tblInvoice"
MainDB.Execute ESQL2
ESQL3 = "Insert into tblInvoice select distinct ARN.InvoiceNumber, ARN.Division + ARN.CustomerNumber as AcctNo, ARN.SOTransDate, ARN.SOShiptoCode, ARO.SOItemNumber, ARO.SOQtyShipped from ARN_InvHistoryHeader ARN Join ARO_InvHistoryDetail as ARO on ARN.InvoiceNumber = ARO.InvoiceNumber and ARN.HeaderSeqNumber = ARO.HeaderSeqNumber where ARO.SOItemNumber is not null"
Debug.Print ESQL3
MainDB.Execute ESQL3
End Sub


As I stated, everything works in SQL directly. My only problem is the attempt of ESQL3 fails to insert any data into the created table. What am I missing here? Thanks in advance
Keith