DAO.QueryDef driven by VBA does not work while via GUI does

[mdlueck]

Ssssiiigggghhhh.... :banghead:

Tried and true shared code all of a sudden fails silently on one query. I download records from the SQL BE DB into FE temp tables with the following shared code:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

I have commented out the cleanup of the DAO.QueryDef objects as the new record was not getting inserted into the FE temp table, so thought to step through and see where the error might be.

Executing (via GUI) the Pass-Through query selects the correct newly added record.

Executing the outer wrapper DAO.QueryDef pops the warning message that one record will be inserted, plop plop fizz fizz the desired record ends up in the FE temp table.

So, I shall paste the contents of the two DAO.QueryDef objects... perhaps I have a typo that causes execution via VBA automation to fail.

vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update_PT has the following query:

SET NOCOUNT ON; DECLARE @aoesid AS int; SET @aoesid = 9; EXEC dbo.clsObjAOEStationTbl_RefreshLocalTmpTbl_1ea @aoesid;

vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update has the following query:

INSERT INTO tmptblqry_aoestation ( id, authid, authusername, logtimestamp, cmplstatusid, cmplstatustitle, stationname, ergobriefflg, ergobestflg, mnfm, loto, notes )
SELECT t.id, t.authid, t.authusername, t.logtimestamp, t.cmplstatusid, t.cmplstatustitle, t.stationname, t.ergobriefflg, t.ergobestflg, t.mnfm, t.loto, t.notes
FROM [COLOR=Blue][B]vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update_PT[/B][/COLOR] AS t;

rrrrrr????? Paste again...

vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update_PT

Oh... that does beat all... the forum software is showing a mysterious space between the P and the T in the Pass-Through DAO.QueryDef name! I simply append on a static:

  'Define the name for the BE query
  strQryNameBE = "vba" & strQueryAPIName & [B][COLOR=Red]"_PT"[/COLOR][/B]

And pasting the source code there is no mysterious space between P and T.

I have already run my Decompile / Compact / Compile procedure and the problem yet persists.

Any suggestions where the hidden character might be coming from? TIA!

P.S. And the same table name appears in the outer DAO.QueryDef wrapper selecting/inserting. I marked that in blue.

 

[mdlueck]

Well I tested the DAO.QueryDef name in my shared code...

strThisChar >_<
strThisChar >U<
strThisChar >p<
strThisChar >d<
strThisChar >a<
strThisChar >t<
strThisChar >e<
[B][COLOR=Red]strThisChar >_<
strThisChar >P<
strThisChar >T<
[/COLOR][/B]Set daoQDFbe = daoDB.CreateQueryDef(vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update[B][COLOR=Red]_PT)[/COLOR][/B]
Set daoQDFfe = daoDB.CreateQueryDef(vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update)

No mysterious character.

And the record did not get to the FE temp table either. gggrrr...... :banghead:

Ohhh... but if I copy/paste only the DAO.QueryDef name itself from the Immediate window THEN the mysterious character will show up... as below:

vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update_PT

But it does not show up if I (as above) copy/pate more of the Immediate window output.

Perhaps I also discovered a bug in the forum software?

 

[mdlueck]

This is a test...

vbaclsObjAOEStationTbl_RefreshLocalTmpTbl_Update

of the outer wrapper DAO.QueryDef name

This is the inner Pass-Through DAO.QueryDef name

vbaclsqryclsObjAOEStationTbl_RefreshLocalTmpTbl_Update_PT

There it is...

I hand typed this entire message, so obviously the mysterious space is being inserted by the forum software.

So, back to the search for where my record goes missing?

 

[mdlueck]

Found the root cause.

It would work via the GUI (left over DAO.QueryDef objects) as it had THEN completed the INSERT operations to ALL of the tables the Stored Procedure was accidentally configured to SELECT from.

When the one SP was running via VBA code, then ALL of the records had not yet been INSERTed into the various tables.

sssiiggghhh.... TWO too many joins in the one offending SP. Nuke'ed 'em, all better... Time to go home...