Query won't execute via 'Code', does fine when pasted into Query grid

[MichaelRed]

I have generated a number of queries to transform some data (tables) in one database into similar tables in another database. All of the queries are generated as sql strings in a module. Most of them work just fine. Two of them do not. They do not generate any errors. When I output the SQL strings (via Debug) in the course of execution and copy these into the query grid and execute them - THEY WORK as EXPECTED!!!

I have, after pasting the sql string into the sql view of the query grid, set the mode to 'design' and resetting the view to 'SQL' copied the sql string and compared it to the generated sql string - with no differences.

I then changed the view to the grid, executed the query (with apparently good results), and again reverted to the SQL view, copied it and compared to the originally generated strin - Again without any difference.

Does anyone here know anything else I can try?

Code to generate one of the strings:

    strSQL = "INSERT INTO " & strDestTblName & " " _
           & "( " & strDestFields & " ) "
    'Special Query, Uses to get all at once *****************************
    strSQL = strSQL & "SELECT tblTransactions.TranReason, " _
           & "tblTransactions.ProductNumber, " _
           & "IIF (Left([SerialNo], 2) = " & Quo & "NA" & Quo & ", " & Quo _
           & "" & Quo & ", SerialNo) As SerNo, " _
           & "Sum(tblTransactions.QPulled) AS QAsgn " _
           & "FROM tblTransactions In " & Chr(39) & dbs_Bos.Name & Chr(39) & " " _
           & "Where " _
           & "TranReason Not LIke " & Quo & "AAT*" & Quo & " And " _
           & "QPulled <> 0 " & " " _
           & "GROUP BY tblTransactions.TranReason, " _
           & "tblTransactions.ProductNumber, SerialNo " _
           & "HAVING ((tblTransactions.TranReason) in (Select JobId From tblJobs));"

the generated string:

INSERT INTO tblTransAct_Service ( [TranReason], [ProductNumber], [SerNo], [QAsgn]  ) SELECT tblTransactions.TranReason, tblTransactions.ProductNumber, IIF (Left([SerialNo], 2) = "NA", "", SerialNo) As SerNo, Sum(tblTransactions.QPulled) AS QAsgn FROM tblTransactions In 'L:\ihs\BosProduction\BOS_BE\ihs_data.mdb' Where TranReason Not LIke "AAT*" And QPulled <> 0  GROUP BY tblTransactions.TranReason, tblTransactions.ProductNumber, SerialNo HAVING ((tblTransactions.TranReason) in (Select JobId From tblJobs));

Help?


MichaelRed

 

[RoyVidar]

I have really no idea, but one thought - it seems you're fetching information from tblTransactions in another database, and matching it's TranReason field with JobID in a local table - are you entirely sure the tblJobs is populated at the time you run this? Could there be some "race condition", where at the time it runs, there are no matching records, but when you try it a bit later, there are?

Roy-Vidar

 

[traingamer]

I assume you're using DoCmd.RunSQL to execute this?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MichaelRed]

Roy-Vidar

hmmmmmmmmmmmmm ... mmmmmmmmmmm interesting thought. I didn't actually check the sequence, but will re-verify this in the morning. I 'distinctly remember' actually stopping the code via a break point after the sql was generated and doing the copy/paste/execute sequences mentioned above ... then, again, at my age memory is not so reliable, so I'll do it again.

traingamer

Actually have done did that way initially, then switched to putting the sql as the qdf.sql and doing the qdf.execute. No difference in the results.

Thanks to both for at least looking at the quagmire.



MichaelRed

 

[JBinQLD]

Have you tried qualifying these two fields?

INSERT INTO tblTransAct_Service ( [TranReason], [ProductNumber], [SerNo], [QAsgn]  ) SELECT tblTransactions.TranReason, tblTransactions.ProductNumber, IIF (Left([SerialNo], 2) = "NA", "", SerialNo) As SerNo, Sum(tblTransactions.QPulled) AS QAsgn FROM tblTransactions In 'L:\ihs\BosProduction\BOS_BE\ihs_data.mdb' Where [red]tblTransactions.[/red]TranReason Not LIke "AAT*" And [red]tblTransactions.[/red]QPulled <> 0  GROUP BY tblTransactions.TranReason, tblTransactions.ProductNumber, SerialNo HAVING ((tblTransactions.TranReason) in (Select JobId From tblJobs));

Perhaps clutching at straws but maybe worth a go

JB

 

[MichaelRed]

Alas, alack and awry (all over again!!)

As usual the error was between the seat and the keyboard (completly bypassing any ssemblance of the neural network).

The Order of execution was correct. tblJobs was populating correctly. The bonehead intervened!. The App receiving the data wants to only work with jobs which are closed. The append query did so wuite nicely, but then included the field (inexplicitly as FALSE) and then the next little snippet deleted all jobs which were not "Closed". Thus the 'appearance' that the queries (for the jobs and transaction) did not work, when in fact they were just fine and dandy.

I must be blessed, for I go round and round and round ... So I should be known as a WHEEL!

But again thanks for the attempt. I know it is difficult to work with incomplete information ... I was SO convinced that the troubles were RIGHT there in that little snippet ...



MichaelRed

 

[traingamer]

I was SO convinced that the troubles were RIGHT there in that little snippet ...

We've all been there.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard