If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Writing a VBA Query for MS Access and have it show the Query Object Window
I need to write VBA code for MS Access that runs a query and sets it in
the Query Objects view. I have set up and performed operations on tables but can not find the code to set up the query. I have to add this query over and over to different databases and I do not want to type it out every time I create another data. I need to get the following queries to show up as a query object after I process them on the form. 060004_Original SELECT Count([060004 Original].facilityid) AS [060004 Original] FROM [060004 Original]; 060004_Appended SELECT Count([060004].facilityid) AS [060004 Appended] FROM 060004; 060004 AMS SELECT Count(AMS.facilityid) AS [060004 AMS] FROM AMS WHERE [AMS.facilityid]=60004; 060004 BRG SELECT Count(BRG.facilityid) AS [060004 BRG] FROM BRG WHERE [BRG.facilityid]=60004; 060004 Companion SELECT Count(Companion.facilityid) AS [060004 Companion] FROM Companion WHERE [Companion.facilityid]=60004; 060004 Dalcon SELECT Count(Dalcon.facilityid) AS [060004 Dalcon] FROM Dalcon WHERE [Dalcon.facilityid]=60004; 060004 HPAS SELECT Count(HPAS.facilityid) AS [060004 HPAS] FROM HPAS WHERE [HPAS.facilityid]=60004; 060004 Medic SELECT Count(Medic.facilityid) AS [060004 Medic] FROM Medic WHERE [Medic.facilityid]=60004; 060004 OMS SELECT Count(OMS.facilityid) AS [060004 OMS] FROM OMS WHERE [OMS.facilityid]=60004; Final Record Count SELECT [060004_Original].[060004 Original] AS [060004 ORG], [060004_Appended].[060004 Appended] AS [060004 APP], [060004 AMS].[060004 AMS] AS AMS, [060004 BRG].[060004 BRG] AS BRG, [060004 Companion].[060004 Companion] AS COM, [060004 Dalcon].[060004 Dalcon] AS DAL, [060004 HPAS]. [060004 HPAS] AS HPS, [060004 Medic].[060004 Medic] AS MED, [060004 OMS]. [060004 OMS] AS OMS, ([060004 APP]-[060004 ORG]) AS [Total Appended], (AMS+BRG+COM+DAL+HPS+MED+OMS) AS [Total Systems], (([Total Appended])- [Total Systems]) AS [Check] FROM 060004_Original, 060004_Appended, [060004 AMS], [060004 BRG], [060004 Companion], [060004 Dalcon], [060004 HPAS], [060004 Medic], [060004 OMS]; trend_rpt SELECT [060004].facilityid, Sum([060004].revenue) AS [Sum of Revenue], Sum( [060004].payment) AS [Sum of Payments], Sum([060004].adjustment) AS [Sum of Adjustments], [060004].transmoyr, [060004].dosmoyr FROM 060004 GROUP BY [060004].facilityid, [060004].transmoyr, [060004].dosmoyr, [060004] ..transYear, [060004].transMonth, [060004].dosYear, [060004].dosMonth; If you can show me how to code one I can code the rest. Thanks in advance. -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
On Wed, 02 Feb 2005 23:14:27 GMT, "Pete Straman Straman via
AccessMonster.com" wrote: I need to write VBA code for MS Access that runs a query and sets it in the Query Objects view. I have set up and performed operations on tables but can not find the code to set up the query. I have to add this query over and over to different databases and I do not want to type it out every time I create another data. Take a look at the CreateQuerydef method: Dim strSQL As String Dim strName As String strSQL = "SELECT this, that, theother FROM..." strName = "QueryX" Dim qd As DAO.Querydef Set qd = db.CreateQuerydef(strName, strSQL) You could even have a table with all the querynames and SQL strings (in Text and Memo fields respectively) and loop through the table creating the queries. John W. Vinson[MVP] |
#3
|
|||
|
|||
Hi John,
Thank you for your help. When I compile it does not recognize the DAO definition. I have been trying to find the same methods under ADO without sucess. Can you show me how it should look using ADO? I have entered the example as shown in Access help for ADO but it is not compiling either. Pete -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
On Thu, 03 Feb 2005 16:43:52 GMT, "Pete Straman Straman via
AccessMonster.com" wrote: Hi John, Thank you for your help. When I compile it does not recognize the DAO definition. I have been trying to find the same methods under ADO without sucess. Can you show me how it should look using ADO? I have entered the example as shown in Access help for ADO but it is not compiling either. I've given up on ADO. Microsoft is no longer developing it (it's all ADO.NET which is *utterly* different and is not supported in Access); and a Query in the Access Query window is a DAO object, not supported by ADO. You'll just need to use Tools... References and select Microsoft DAO x.xx (highest version) if you want to put queries into the Queries window in Access. John W. Vinson[MVP] |
#5
|
|||
|
|||
Thanks again John
Here is how I coded it after setting Micosoft DAO to 3.6 Object Library under Tools/References Private Sub cmdRecordCount_Click() Dim db060141 As Database Dim qdfNew As QueryDef Set dbs060141 = OpenDatabase("060141.mdb") With dbs060141 ' Create 060141 Original Record Count Query Set qdfNew = .CreateQueryDef("060141_Original", "SELECT Count([060141 Original].facilityid)" _ + " AS [060141 Original]FROM [060141 Original];") .Close End With MsgBox "Record Count and 'trend_rpt' queries have been created." End Sub C. Pete Straman -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
On Fri, 04 Feb 2005 00:03:24 GMT, "Pete Straman Straman via
AccessMonster.com" wrote: Thanks again John Here is how I coded it after setting Micosoft DAO to 3.6 Object Library under Tools/References So, did it work? I presume that this is creating a query in a database other than the one running the code; otherwise you could simply have use CurrentDb. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|