Roger,
What I did was
1) take 4 (typical Access queries) in SQL view
2) copy/paste those queries into a VBA Test() sub
3) parse the SQL strings as pasted (#2) into a working SQL string
"xxxxx" _
& "yyyy" _
& "zzzz"
4) pre-appended the SQL statement with the command:
CurrentProject.Connection.Execute
This approach appears to work but I cannot see the results.
In the original queries, the queries depend upon the others.
If I use CurrentProject.Connection.Execute the query dependencies work
(That
said, I am not sure whether the dependencies are being fulfilled by the
original query or the as-converted VBA subroutines (now "queries")
If I use the DoCmd.OpenQuery, the dependencies fail in sequence.
You bring up a good thought path "Action" vs "Select" queries. I
thought
I
was creating select queries but I guess I may actually have an Action
query.
Pray-tell I have no idea way to do next!
Bottom line, I would like each VBA query to display results as if I ran
the
standard query in the query module (results in datasheet view).
ACtual code:
Sub CHOOSE_Add_Fields()
CurrentProject.Connection.Execute "SELECT CHOOSEData.BFY,
CHOOSEData.APPN_SYMB, CHOOSEData.SBHD, " _
& "CHOOSEData.BCN, CHOOSEData.SA_SX, CHOOSEData.AAA_UIC,
CHOOSEData.ACRN, CHOOSEData.AMT, " _
& "CHOOSEData.DOC_NUMBER, CHOOSEData.FIPC, CHOOSEData.REG_NUMB,
CHOOSEData.TRAN_TYPE, " _
& "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE,
CHOOSEData.OBJ_CODE, CHOOSEData.EFY, " _
& "CHOOSEData.REG_MO, CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, ""
""
AS Orig_Sort, " _
& "IIf(Left([BFY],1)=""0"",Mid([BFY],2,1),[BFY]) AS LTrim_BFY, " _
& "IIf(Len([AAA_UIC])>=6,Trim(Mid([AAA_UIC],2,6)),[AAA_UIC]) AS
LTrim_AAA, " _
& "IIf(Left([REG_NUMB],1)=""0"",Mid([REG_NUMB],2,1),[REG_NUMB]) AS
LTrim_REG, " _
&
"IIf(Left([DOV_NUM],4)=""0000"",Mid([DOV_NUM],5,255),IIf(Left([DOV_NUM],3)=""000"",Mid([DOV_NUM],4,255),
" _
&
"IIf(Left([DOV_NUM],2)=""00"",Mid([DOV_NUM],3,255),IIf(Left([DOV_NUM],1)=""0"",Mid([DOV_NUM],2,255),[DOV_NUM]))))
" _
& "AS LTrim_DOV, [AMT]*-1 AS AMT_Rev" & Chr(10) _
& "FROM CHOOSEData;"
End Sub
Sub CHOOSE_Revised()
CurrentProject.Connection.Execute "SELECT [CHOOSE_Add_Fields].BFY,
[CHOOSE_Add_Fields].APPN_SYMB, " _
& "[CHOOSE_Add_Fields].SBHD, [CHOOSE_Add_Fields].BCN,
[CHOOSE_Add_Fields].SA_SX, [CHOOSE_Add_Fields].AAA_UIC, " _
& "[CHOOSE_Add_Fields].ACRN, [CHOOSE_Add_Fields].AMT,
[CHOOSE_Add_Fields].DOC_NUMBER, " _
& "[CHOOSE_Add_Fields].FIPC, [CHOOSE_Add_Fields].REG_NUMB,
[CHOOSE_Add_Fields].TRAN_TYPE, " _
& "[CHOOSE_Add_Fields].DOV_NUM, [CHOOSE_Add_Fields].PAA,
[CHOOSE_Add_Fields].COST_CODE, " _
& "[CHOOSE_Add_Fields].OBJ_CODE, [CHOOSE_Add_Fields].EFY,
[CHOOSE_Add_Fields].REG_MO, " _
& "[CHOOSE_Add_Fields].RPT_MO, [CHOOSE_Add_Fields].EFFEC_DATE,
[CHOOSE_Add_Fields].Orig_Sort, " _
& "[CHOOSE_Add_Fields].LTrim_BFY, [CHOOSE_Add_Fields].LTrim_AAA,
[CHOOSE_Add_Fields].LTrim_REG, " _
& "[CHOOSE_Add_Fields].LTrim_DOV, [CHOOSE_Add_Fields].AMT_Rev, " _
& "[CHOOSE_Add_Fields].DOV_NUM & [CHOOSE_Add_Fields].TRAN_TYPE &
[CHOOSE_Add_Fields].AMT_Rev AS CONCACT" _
& Chr(10) & "FROM CHOOSE_Add_Fields" & Chr(10) _
& "WHERE ((([CHOOSE_Add_Fields].TRAN_TYPE) In ('1K','2D')) And
(([CHOOSE_Add_Fields].LTrim_REG)<>'7'));"
End Sub
Sub CHOOSE_STARS_UMD()
CurrentProject.Connection.Execute "SELECT CHOOSE_Revised.*" _
& "FROM CHOOSE_Revised LEFT JOIN STARS_Revised ON
CHOOSE_Revised.CONCACT=STARS_Revised.CONCACT" & Chr(10) _
& "WHERE (((STARS_Revised.CONCACT) Is Null));"
End Sub
Sub STARS_CHOOSE_UMD()
CurrentProject.Connection.Execute "STARS_Revised.*" & Chr(10) _
& "FROM STARS_Revised LEFT JOIN CHOOSE_Revised ON
STARS_Revised.CONCACT=CHOOSE_Revised.CONCACT" & Chr(10) _
& "WHERE (((CHOOSE_Revised.CONCACT) Is Null));"
'DoCmd.OpenQuery "STARS_CHOOSE_UMD", , acReadOnly
End Sub
Sub STARS_Revised()
CurrentProject.Connection.Execute "SELECT STARSData.AMT,
STARSData.DR_CR,
STARSData.DOC_NUMBER, " _
& "STARSData.ACRN, STARSData.FIPC, STARSData.REG_NUMB,
STARSData.BFY,
STARSData.APPN_SYMB, " _
& "STARSData.SBHD, STARSData.BCN, STARSData.SA_SX,
STARSData.AAA_UIC,
STARSData.TRAN_TYPE, " _
& "STARSData.DOV_NUMB, STARSData.DOVE_DATE, STARSData.PIIN,
STARSData.COST_CODE, STARSData.OBJ_CODE, " _
& "STARSData.FUND_CODE, STARSData.JON_UIC, STARSData.JON_FY,
STARSData.JON_SERIAL, " _
& "STARSData.EFFEC_DATE, STARSData.EXEC_CODE, STARSData.USER_ID,
""
""
AS Orig_Sort, " _
& "STARSData.DOV_NUMB & STARSData.TRAN_TYPE & STARSData.AMT AS
CONCACT" & Chr(10) _
& "FROM STARSData" & Chr(10) _
& "WHERE (((STARSData.REG_NUMB)<>'7') AND
((STARSData.DOVE_DATE)<>'0001-01-01'));"
End Sub
:
What kind of query is it and what exactly do you want displayed? The
Execute method is only for "Action" queries, not for Select queries.
Perhaps the simplist solution is:
DoCmd.OpenQuery sqlQuery1
--
--Roger Carlson
MS Access MVP
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
"EagleOne@microsoftdiscussiongroups"
message 2003
Successfully ran a VBA query in the form of:
CurrentProject.Connection.Execute sqlQuery1
Query ran but I cannot see the results. This my 1st time converting
my
queries to VBA. I "assumed" that the results would display as if I
ran
the
query in normal mode?
I do not know the VBA syntax to display results
TIA EagleOne
