Writing a VBA Query for MS Access and have it show the Query Object Window

[Pete Straman Straman via AccessMonster.com]

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.

(e-mail address removed)

 

[John Vinson]

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]

 

[Pete Straman Straman via AccessMonster.com]

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

 

[John Vinson]

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]

 

[Pete Straman Straman via AccessMonster.com]

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

 

[John Vinson]

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]