Can you use DMax with ADO if your recordset is an Access database?

[bs0d]

I'm having issues with it, an error indicates the table cannot be found.

DMax('field', 'tablename', 'criteria')

 

[bs0d]

Re: Please provide further explanation of the requirement if you want further help

Hi,
My query still looks right to me. Can you provide some sample data (it doesn't have to be "real", just the right kind to illustrate the problem)? I can't test on anything without some sample data and I'm afraid if I create my own I would still be making some mistake in my understanding of these tables and what is in them.

tblDailyReadings sample:

tblComments sample:

If you query by Prop_KEY 86, you should get 5 daily readings. MaxValComment should be 9 on date 1/1/2014, 1/3/2014, and 1/5/2014 (NULL on 1/2/2014 as no comment exists for that date).

If you query by PROP_KEY 99, you should get 5 daily readings. MaxValComment should be 1, as the max of 0.5 is less than 1. The 1 will appear in the MaxValComment field for dates 1/2 and 1/3 (NULL on the 3 other dates, no comments exist).

If you query by PROP_KEY 45, you should get 5 daily readings. MaxValComment should be 50 on dates 1/2 and 1/5. Dates 1/1, 1/3 and 1/4 should have NULL value in MaxValComment field.

 

[xenou]

Re: Please provide further explanation of the requirement if you want further help

Hi,
Okay this is off the top of my head so test carefully.

General strategy: two queries. First is to get those max dates. Then we join the second query on the first query to get the data you want.

1st query qry_001_MaxOfField9

SELECT 
	DR.Prop_KEY, 
	C.CommentDate, 
	Max(DR.Field9) AS MaxOfField9
FROM 
	tblDailyReadings AS DR 
	INNER JOIN tblComments AS C 
	ON DR.Prop_KEY = C.WH_IDX
GROUP BY 
	DR.Prop_KEY, C.CommentDate;

Second query, qry_002_MaxValComment

SELECT 
	DR.Prop_KEY, 
	DR.ReadingDate, 
	IIf([MaxOfField9]<1,1,[MaxOfField9]) AS MaxValComment
FROM 
	tblDailyReadings AS DR 
	LEFT JOIN qry_001_MaxOfField9 AS Q 
	ON 
		(DR.ReadingDate = Q.CommentDate) 
		AND 
		(DR.Prop_KEY = Q.Prop_KEY);

Result:
------------------------------------------
| Prop_KEY | ReadingDate | MaxValComment |
------------------------------------------
|       86 |   01-Jan-14 |             9 |
|       86 |   02-Jan-14 |               |
|       86 |   03-Jan-14 |             9 |
|       86 |   04-Jan-14 |               |
|       86 |   05-Jan-14 |             9 |
|       99 |   01-Jan-14 |               |
|       99 |   02-Jan-14 |             1 |
|       99 |   03-Jan-14 |             1 |
|       99 |   04-Jan-14 |               |
|       99 |   05-Jan-14 |               |
|       45 |   01-Jan-14 |               |
|       45 |   02-Jan-14 |            50 |
|       45 |   03-Jan-14 |               |
|       45 |   04-Jan-14 |               |
|       45 |   05-Jan-14 |            50 |
------------------------------------------


Sample Database with queries and your sample data:
<a href="http://northernocean.net/etc/mrexcel/20141118_db1.zip">SAMPLE DATABASE</a>
sha256 checksum (zip file): e2d84c6d826c5b66462c20e121ea593993a40c29026458094558f1044ed22473

 

[bs0d]

Re: Please provide further explanation of the requirement if you want further help

How might I consolidate these if I'm going the route of defining the statements in SQL in my VBA code via an ADO query?

 

[xenou]

Re: Please provide further explanation of the requirement if you want further help

Hi, sorry not to reply sooner. Did you get it working? Since the SQL is "pure" SQL it should work with ADO. Simplest is to have the queries in Access and then just write the SQL as a simple "Select * from My_Query".

 

[bs0d]

Re: Please provide further explanation of the requirement if you want further help

So what's not quite clear to me yet is:

Can you explain:

Set param = cmd.CreateParameter("Enter ID", adInteger, adParamInput, 5)

How might you alter the line above if you're setting the param.Value in the next line (my_var)?

Also, in order to use a parameter query in ADO, must the query exist in the Access database? OR can you create a parameter query in ADO directly using SQL? If so, how might that SQL code look? Is it as simple as adding [ and ] to a field name in a WHERE clause? LIke:

WHERE ID = [ID]

Also, hopefully this isn't too confusing, but I have a case where I'm going to be querying a query. I have a base query in Access that brings a lot of information together in one place. I need to query select fields for a select property of that base query. So, must the base query have the parameter requirement, or the query to the query, which will be coded in ADO?

 

[xenou]

Re: Please provide further explanation of the requirement if you want further help

Hi,

Can you explain:

Code:

Set param = cmd.CreateParameter("Enter ID", adInteger, adParamInput, 5)

How might you alter the line above if you're setting the param.Value in the next line (my_var)?

I would imagine this to be simply:

Set param = cmd.CreateParameter("Enter ID", adInteger, adParamInput, 5)
param = my_var

Also, in order to use a parameter query in ADO, must the query exist in the Access database? OR can you create a parameter query in ADO directly using SQL? If so, how might that SQL code look? Is it as simple as adding [ and ] to a field name in a WHERE clause? LIke:

Code:

WHERE ID = [ID]

Parameter queries by definition exist already in the database. If you are creating the query on the fly you can just fill in the values as part of the query string:

my_var = 5
my_sql = "SELECT * FROM Table1 WHERE ID = " & my_var

So there is no need for parameters in that case.

 

[bs0d]

Re: Please provide further explanation of the requirement if you want further help

Thanks again. This clears up handling parameters in Access and ADO for me.

In my 3 tier query structure scenario:

Tier 1: Queries that return individual fields
Tier 2: One query combining all Tier 1 queries that gets the data I need in 1 place.
Tier 3: The "final" query to fetch the data I'm after (directly to the Tier 2 query).

I have everything working fast when I setup all tier 1 queries to require a parameter in Access. From your feedback, how would structure the Tier 3 request if all queries (Tier 1, 2 and 3) were hard-coded in my program?

I imagine this would be one gigantic query? If so, how do you structure that SQL statement? Because my problem is, the field which all Tier 1 queries should be limited by can be any number of fields. Because of this, it maybe easier to write out the big statement in VBA vs. creating what could be 100's of Tier 1 queries in Access and using a big Select Case statement to point to the right query names in Access based on the user selection.

 

[xenou]

Re: Please provide further explanation of the requirement if you want further help

Off the top of my head, as a general rule I avoid really big SQL queries. I've seen (and worked) with queries that are 100's of lines long but it's unpleasant. On the other hand using 100's of queries sounds equally awkward if not managed carefully.

If queries are all very similar you may have a way of reducing the number, or perhaps building them with code rather than by hand. You might wish to muck about with some ideas about how you could write a program that might create the queries you need - which would also enable the project to be re-created or changed more easily.

You'll have to mock up an example for me to give any specific advice. Both ideas will probably work, and maybe it's just a matter of personal preference, but in Access (particularly) very large complicated queries are apt to run very slowly. It would be interesting if you can give an example with maybe 3-4 "Tier 1" queries, and show how the user would need to give input and how the higher tiers would then use the Tier 1 queries.