Running Access queries from Excel without opening the Access DB 1

[gwoman]

I am working in Excel where I have a drop down list of object names ... when I select an object from this list ... I want it to go to an Access DB that holds the data, run a query and return the data to excel with out opening up the DB. My problem is this ... when I try to set what query I want to run I don't see all the queries in my DB. I can only see Select queries and tables ... why do the other queries (i.e. union, or a query qith subqueries, etc.) not show up? The only way I can see and run those is if I actually open the DB and I don't want to have to do that.
I would greatly appreciate any help and/or suggestions ... thanks!

gwoman

 

[SkipVought]

Hi,

In the window where you select the tables, hit the OPTIONS button and make sure that VIEWS is checked (I have all FOUR checked).

Skip,

_[red][/red]

 

[gwoman]

Thanks Skip ... I tried this ... and I can see the queries now ... however when I try to expand them to select what fields I want to bring over there are no fields ... did I miss something?
Thanks =)
gwoman

 

[SkipVought]

If you EDIT the query in the QBE editor, you can see each field in the query.

Skip,

_[red][/red]

 

[gwoman]

Excellent ... thanks!

 

[gwoman]

Skip ...
I am still having issues with the QBE editor .... I found the following you had written in a different post ...

Data/Get External Data/New Database Query...

Select MS Access Driver - Drill down to your Access Data base - select your table(s)/Query(s) - Edit the query in the QBE grid - File/Return Data to Excel.

This has inserted a QueryTable in your sheet and returns the resultset of data. To get FRESH data from Access, Data/Refresh.

You should now be able to create a macro that does the refresh on the update of you well pick cell.

The problem I am having is once I get to the point of "Edit the query in the QBE grid" ... I can't seem to find where to to this ...

Appreciate you help ... thanks!

gwoman

 

[SkipVought]

So have you already set up your QueryTable and has it returned a recordset to your sheet?

If so, select anywhere the QueryTable, turn on your macro recorder, Data/Import External Data/Edit Query -- complete thru to the QBE editor, from which you will File/Return data to Excel, turn off the macro recorder, observe your code in the VB Editor (alt+F11)

Skip,

_[red][/red]

 

[gwoman]

What exactly do you mean by "set up my query table" ....
If I have not done this ... what process would I need to go through to get to this point?
Thanks ...

gwoman

 

[SkipVought]

When you Return data to Excel the first time, you have Added a QueryTable in your sheet.

Once a QueryTable has been inserted in a sheet, you can simply Data/Refresh to get a new set of data from your data source.

Can you Return data to Excel and see the recordset in your sheet?

Skip,

_[red][/red]

 

[gwoman]

Gotcha ....

Yes ... but not on the queries that are "union" or contain subqueries as once expanded there are no fields displayed to select to return data ... and these queries are the ones I want to use to return data.

thanks

gwoman

 

[SkipVought]

I can see UNION querys that are in MS Access.

In the QBE Editor, Tables/Add Tables in the Add Tables window, hit the OPTIONS... button.

Is EVERYTHING checked?

Skip,

_[red][/red]

 

[gwoman]

Yes ... they are all checked ...
I can see the query name in the the list ... but when expanded ... there are no fields under it ...

gwoman

 

[SkipVought]

Go into your Access database and EDIT the query in question. In the QBE editor in Access, activate the SQL Editor. Please Copy 'n' Paste your SQL here.

Skip,

_[red][/red]

 

[gwoman]

SELECT DISTINCT DWRPTG_FAC_WORK_ACTY_FACT.FAC_ID AS WELL_FAC_ID, DWRPTG_WELL_DMN.WELL_DMN_KEY, DWRPTG_CMPL_DMN.CMPL_FAC_ID, DWRPTG_WELL_DMN.WELL_NME, DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_STRT_DTTM, DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_FNSH_DTTM, DWRPTG_WORK_ACTY_DMN.WORK_ACTY_TYPE_CDE, DWRPTG_WORK_ACTY_DMN.WORK_ACTY_TYPE_DESC, Event_Class_Reason.Child_Code, Event_Class_Reason.[Significant Event], DWRPTG_CMPL_DMN.ENGR_STRG_NME
FROM Well INNER JOIN ((DWRPTG_WELL_DMN INNER JOIN ((DWRPTG_FAC_WORK_ACTY_FACT INNER JOIN DWRPTG_WORK_ACTY_DMN ON DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_DMN_KEY = DWRPTG_WORK_ACTY_DMN.WORK_ACTY_DMN_KEY) INNER JOIN Event_Class_Reason ON DWRPTG_WORK_ACTY_DMN.WORK_ACTY_TYPE_CDE = Event_Class_Reason.Child_Code) ON DWRPTG_WELL_DMN.WELL_FAC_ID = DWRPTG_FAC_WORK_ACTY_FACT.FAC_ID) INNER JOIN DWRPTG_CMPL_DMN ON DWRPTG_WELL_DMN.WELL_FAC_ID = DWRPTG_CMPL_DMN.WELL_FAC_ID) ON Well.Well_Fac_Id = DWRPTG_FAC_WORK_ACTY_FACT.FAC_ID
WHERE (((DWRPTG_CMPL_DMN.PRIM_PURP_TYPE_CDE)="INJ") AND ((DWRPTG_CMPL_DMN.PRIM_MATL_DESC)="Water") AND ((DWRPTG_CMPL_DMN.INJ_RSN_TYPE_DESC)="Flood"))
GROUP BY DWRPTG_FAC_WORK_ACTY_FACT.FAC_ID, DWRPTG_WELL_DMN.WELL_DMN_KEY, DWRPTG_CMPL_DMN.CMPL_FAC_ID, DWRPTG_WELL_DMN.WELL_NME, DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_STRT_DTTM, DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_FNSH_DTTM, DWRPTG_WORK_ACTY_DMN.WORK_ACTY_TYPE_CDE, DWRPTG_WORK_ACTY_DMN.WORK_ACTY_TYPE_DESC, Event_Class_Reason.Child_Code, Event_Class_Reason.[Significant Event], DWRPTG_CMPL_DMN.ENGR_STRG_NME
HAVING (((DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_STRT_DTTM) Is Not Null) AND ((DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_FNSH_DTTM) Is Not Null) AND ((DWRPTG_WORK_ACTY_DMN.WORK_ACTY_TYPE_CDE) Not Like "WELL.STACID") AND ((Event_Class_Reason.[Significant Event])="Y"))
ORDER BY DWRPTG_FAC_WORK_ACTY_FACT.FAC_ID, DWRPTG_CMPL_DMN.CMPL_FAC_ID, DWRPTG_FAC_WORK_ACTY_FACT.WORK_ACTY_STRT_DTTM
UNION SELECT DISTINCT DWRPTG_CMPL_DMN.WELL_FAC_ID, DWRPTG_WELL_DMN.WELL_DMN_KEY, DWRPTG_CMPL_DMN.CMPL_FAC_ID, DWRPTG_WELL_DMN.WELL_NME, DWRPTG_WELL_NOTES_CMNT_TB.WELL_NOTES_CMNT_DTE AS WORK_ACTY_STRT_DTTM, DWRPTG_WELL_NOTES_CMNT_TB.WELL_NOTES_CMNT_DTE AS WORK_ACTY_FNSH_DTTM, "STR2STR" AS WORK_ACTY_TYPE_CDE, DWRPTG_WELL_NOTES_CMNT_TB.CMNT_CDE AS WORK_ACTY_TYPE_DESC, "" AS Child_Code, "" AS [Significant Event], DWRPTG_CMPL_DMN.ENGR_STRG_NME
FROM Well INNER JOIN (DWRPTG_WELL_DMN INNER JOIN (DWRPTG_CMPL_DMN INNER JOIN (DWRPTG_WELL_NOTES_CMNT_TB INNER JOIN DWRPTG_TASK_TB ON DWRPTG_WELL_NOTES_CMNT_TB.CMNT_CDE = DWRPTG_TASK_TB.TASK_CDE) ON DWRPTG_CMPL_DMN.WELL_FAC_ID = DWRPTG_WELL_NOTES_CMNT_TB.WELL_FAC_ID) ON DWRPTG_WELL_DMN.WELL_FAC_ID = DWRPTG_CMPL_DMN.WELL_FAC_ID) ON Well.Well_Fac_Id = DWRPTG_CMPL_DMN.WELL_FAC_ID
WHERE (((DWRPTG_CMPL_DMN.PRIM_PURP_TYPE_CDE)="INJ") AND ((DWRPTG_CMPL_DMN.PRIM_MATL_DESC)="Water") AND ((DWRPTG_CMPL_DMN.INJ_RSN_TYPE_DESC)="Flood"))
GROUP BY DWRPTG_CMPL_DMN.WELL_FAC_ID, DWRPTG_WELL_DMN.WELL_DMN_KEY, DWRPTG_CMPL_DMN.CMPL_FAC_ID, DWRPTG_WELL_DMN.WELL_NME, DWRPTG_WELL_NOTES_CMNT_TB.WELL_NOTES_CMNT_DTE, DWRPTG_WELL_NOTES_CMNT_TB.WELL_NOTES_CMNT_DTE, "STR2STR", DWRPTG_WELL_NOTES_CMNT_TB.CMNT_CDE, "", "", DWRPTG_CMPL_DMN.ENGR_STRG_NME
HAVING (((DWRPTG_WELL_NOTES_CMNT_TB.CMNT_CDE)="STR2STR"));


Thanks

gwoman

 

[SkipVought]

Does this query return a recordset in Access?

Skip,

_[red][/red]

 

[gwoman]

Yes ...

gwoman

 

[SkipVought]

Why do you have a Group By clause in each, since you are performing NO AGGRIGATION? And why do you have AS alias names in the code after the UNION.

These two things may be causing "confusion"

SELECT DISTINCT 
  FAC.FAC_ID AS WELL_FAC_ID
, DMN.WELL_DMN_KEY
, CMP.CMPL_FAC_ID
, DMN.WELL_NME
, FAC.WORK_ACTY_STRT_DTTM
, FAC.WORK_ACTY_FNSH_DTTM
, ACT.WORK_ACTY_TYPE_CDE
, ACT.WORK_ACTY_TYPE_DESC
, RSN.Child_Code
, RSN.[Significant Event]
, CMP.ENGR_STRG_NME

FROM 
             Well                       WEL
INNER JOIN ((DWRPTG_WELL_DMN            DMN
INNER JOIN ((DWRPTG_FAC_WORK_ACTY_FACT  FAC
INNER JOIN DWRPTG_WORK_ACTY_DMN         ACT
   ON FAC.WORK_ACTY_DMN_KEY    = ACT.WORK_ACTY_DMN_KEY) 
INNER JOIN   Event_Class_Reason         RSN
   ON ACT.WORK_ACTY_TYPE_CDE   = RSN.Child_Code) 
   ON DMN.WELL_FAC_ID          = FAC.FAC_ID) 
INNER JOIN   DWRPTG_CMPL_DMN            CMP
   ON DMN.WELL_FAC_ID          = CMP.WELL_FAC_ID) 
   ON WEL.Well_Fac_Id          = FAC.FAC_ID

WHERE (((CMP.PRIM_PURP_TYPE_CDE)   ="INJ")
  AND ((CMP.PRIM_MATL_DESC)        ="Water")
  AND ((CMP.INJ_RSN_TYPE_DESC)     ="Flood"))
  AND (((FAC.WORK_ACTY_STRT_DTTM)  Is Not Null)
  AND ((FAC.WORK_ACTY_FNSH_DTTM)   Is Not Null)
  AND ((ACT.WORK_ACTY_TYPE_CDE)    Not Like "WEL.STACID")
  AND ((RSN.[Significant Event])   ="Y"))

ORDER BY FAC.FAC_ID, CMP.CMPL_FAC_ID, FAC.WORK_ACTY_STRT_DTTM

UNION 

SELECT DISTINCT 
  CMP.WELL_FAC_ID
, DMN.WELL_DMN_KEY
, CMP.CMPL_FAC_ID
, DMN.WELL_NME
, CMT.WELL_NOTES_CMNT_DTE 
, CMT.WELL_NOTES_CMNT_DTE
, "STR2STR" 
, CMT.CMNT_CDE 
, "" 
, "" 
, CMP.ENGR_STRG_NME

FROM 
            Well                       WEL
INNER JOIN (DWRPTG_WELL_DMN            DMN
INNER JOIN (DWRPTG_CMPL_DMN            CMP
INNER JOIN (DWRPTG_WELL_NOTES_CMNT_TB  CMT
INNER JOIN  DWRPTG_TASK_TB             TSK
   ON CMT.CMNT_CDE    = TSK.TASK_CDE) 
   ON CMP.WELL_FAC_ID = CMT.WELL_FAC_ID) 
   ON DMN.WELL_FAC_ID = CMP.WELL_FAC_ID) 
   ON WEL.Well_Fac_Id = CMP.WELL_FAC_ID

WHERE (((CMP.PRIM_PURP_TYPE_CDE) ="INJ")
  AND ((CMP.PRIM_MATL_DESC)      ="Water")
  AND ((CMP.INJ_RSN_TYPE_DESC)   ="Flood"))
  AND (((CMT.CMNT_CDE)           ="STR2STR"));

Skip,

_[red][/red]

 

[gwoman]

Thanks ... I will test it

gwoman

 

[gwoman]

Hi Skip ....

I tested the query and it ran fine ... however I am still running into the same issue ... in the Excel Query Editor the query is visible but when I click on the + to expand it ... the columns are not visible ...

You have been a tremendous help ... and if you happen to have any more ideas I would love to hear them!

Thanks again for you time! =)

gwoman

 

[SkipVought]

"...when I click on the + to expand it "

You are not yet in the QBE Editor Grid are you?

In the QBE Editor you have Menu Items...

File, Edit, View, Format, Table, Criteria, Records, Window, Help.

When you select Table, you get a drow down of tables and queries. There is no + to expand.

Skip,

_[red][/red]