ODBC Call failed

[Skullish]

Hi,
First of all, here are my work tools : I have an Access 2003 app that uses an SQL Server DataBase (i know it's weird). I am using Microsoft SQL Server Management Studio Express.
I am now trying to execute a query from my app to insert some rows from 2 tables into another. My query works well when i use it rigth into SQL Server Management or when i create a new query in Access, but it don't work in my app... It gives me the "ODBC Call failed" error for an unkwown reason that i can't discover because my query works well in other situations...

Let's see my VBA code :

Private Sub cmdReport_Click()
On Error GoTo Erreur
ErrRoll = False

'** sauvegarde l'enregistrement !!
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim Db As Database
Dim Ws As Workspace
Dim Rs As Recordset
Dim Rs1 As Recordset
Dim Rs2 As Recordset
Set Ws = DBEngine.Workspaces(0)
Set Db = CurrentDb
Dim HasDuplicate As Boolean
HasDuplicate = False
'** Recherche les doublons dans Tag #
Set Rs = Db.OpenRecordset("SELECT DISTINCTROW First(TbTrxInHstDtl.NoTrx) AS NoTrxChamps, First(TbTrxInHstDtl.TagNo) AS UnitéNumberChamps, Count(TbTrxInHstDtl.NoTrx) AS NombreDeDbls FROM TbTrxInHstDtl " _
    & "GROUP BY TbTrxInHstDtl.NoTrx, TbTrxInHstDtl.tagNo " _
    & "HAVING (((Count(TbTrxInHstDtl.NoTrx))>1) AND ((TbTrxInHstDtl.NoTrx)=" & Me![NoTrx] & ") AND ((Count(TbTrxInHstDtl.TagNo))>1));", 8)
If Not Rs.BOF Then
    MsgStop ("Le même numéro de Tag est inscrit en double !" & Chr$(13) & "Corrigez.")
    [RqTrxInDtl].SetFocus
    Exit Sub
End If
'*** check si en inventaire on a meme Tag No pour meme BOL(in)
Set Rs = Db.OpenRecordset("SELECT TbInventaire.TagNumber, TbTrxInHstHdr.NoTrx FROM (TbInventaire INNER JOIN TbTrxInHstDtl ON TbInventaire.TagNumber = TbTrxInHstDtl.TagNo) INNER JOIN TbTrxInHstHdr ON (TbTrxInHstHdr.NoTrx = TbTrxInHstDtl.NoTrx) AND (TbInventaire.ClientProduitNo = TbTrxInHstHdr.ClientProduitNo) AND (TbInventaire.CliSource = TbTrxInHstHdr.ClientSource) AND (TbInventaire.BolIn = TbTrxInHstHdr.ClientBOLNo) " _
    & "WHERE (((TbTrxInHstHdr.NoTrx)=" & Me![NoTrx] & "));", 8)
If Not Rs.BOF Then
    If vbNo = MsgConfirmA("Le numéro d'entreposage(U.E.) " & Rs![TagNumber] & " est inscrit" _
        & Chr$(13) & "en inventaire pour le même client" _
        & Chr(13) & " et le même numéro d'expédition du client(Exp.#-Client) !" _
        & Chr(13) & "Voulez-vous reporter quand même ?") Then
        [RqTrxInDtl].SetFocus
        Exit Sub
    End If
End If
'*** version 3.1 le 06/ déc. 2002
'*** y a t il déjà eu une Trx In pour ce client,, meme BOL number
'** et meme produit ???
'Set Rs1 = Db.OpenRecordset("SELECT TbTrxInHstHdr.ClientSource, TbTrxInHstHdr.ClientProduitNo, TbTrxInHstHdr.ClientBOLNo FROM TbTrxInHstHdr " _
'    & "WHERE (((TbTrxInHstHdr.ClientSource)='" &[ListClientSource] & "') AND ((TbTrxInHstHdr.ClientProduitNo)='" &[ListClientProduitNo] & "') AND ((TbTrxInHstHdr.ClientBOLNo)=" & [ClientBOLNo] & "));", 8)

If vbNo = MsgConfirmQ("Reporter cette transaction maintenant ?") Then
    Exit Sub
End If
DoCmd.Hourglass True
Ws.BeginTrans
ErrRoll = True

'** insère Dtl
Db.Execute ("INSERT INTO TbTrxInHstDtl ( NoTrx, TagNo, FormatExp, UnitéDeMesure, Localisation, [Note], Qté, QtéExtension, LigneNo, QtéUnit ) " _
    & "SELECT TbTrxInHstDtl.NoTrx, TbTrxInHstDtl.TagNo, TbTrxInHstDtl.FormatExp, TbProduits.UnitéDeMesure, TbTrxInHstDtl.Localisation, TbTrxInHstDtl.Note, TbTrxInHstDtl.Qté, TbTrxInHstDtl.QtéExtension, TbTrxInHstDtl.LigneNo, TbTrxInHstDtl.QtéUnit FROM (TbTrxInHstHdr INNER JOIN TbTrxInHstDtl ON TbTrxInHstHdr.NoTrx = TbTrxInHstDtl.NoTrx) INNER JOIN TbProduits ON (TbTrxInHstHdr.ClientSource = TbProduits.CliId) AND (TbTrxInHstHdr.ClientProduitNo = TbProduits.CliProduitNo) " _
    & "WHERE (((TbTrxInHstDtl.NoTrx)=" & Me![NoTrx] & "));"), dbFailOnError
'*** Envoie en inventaire
Db.Execute ("INSERT INTO TbInventaire (DateIn, CliSource, ClientProduitNo, CliLotNo, NbreUnits, QtéStock, TotalStock, FormatExp, TagNumber, Localisation, TrxLigneNo, RemorqueIn, BOlIn, TrxNumber, LaNote) " _
    & "SELECT DateTrx, ClientSource, ClientProduitNo, ClientLotNo, QtéUnit, Qté, QtéExtension, FormatExp, TagNo, Localisation, LigneNo, RemorqueNo, ClientBOLNo, TbTrxInHstHdr.NoTrx, Note " _
    & "FROM TbTrxInHstHdr INNER JOIN TbTrxInHstDtl ON TbTrxInHstDtl.NoTrx = TbTrxInHstHdr.NoTrx " _
    & "WHERE TbTrxInHstHdr.NoTrx = " & Me![NoTrx]), dbFailOnError

My code is bugging at the last Db.Execute.

Can someone help me please?

[nico5038]

Guess the INSERT has an error.
Best to place a breakpoint in the code and when the db.execute has been reached to do a print of the string in the immediate window.
Next copy / paste the string into a query and execute it in the query editor to get the exact error message.

Nic;o)

[Skullish]

Finaly i don't have this bug anymore... don't know what i did to correct it but it's ok now with this same query.

I definitly hate Access

[nico5038]

It might fail when fields are empty, just add some code to test them before executing the query.

Success and "au revoir" :-)

Nic;o)