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

It's a confusing query I guess too:

• The error message doesn't seem to be very helpful - but I don't know that you can use single quotes inside DMax arguments. They normally need to be in double quotes. So you could still try to rehabilitate DMax though I seriously doubt ADO can run a DMax function.
• In your last post, why join the tables on reading dates if, as you say, the tables are related on PropKey?
• FYI I don't think a double has any business being an ID because you don't want to use floating point numbers for joining tables or with WHERE criteria that use equality comparisons.
• I would recommend you use tables aliases or table names so we can know what fields come from what tables.
• Your reply to ranman about why not to put the query on the Access side doesn't really make sense. It seems worth a try anyway.
• The strange thing is that this sounds like dead simple SQL to write. Can you provide a plain English explanation again and give the table structure (field names and data types - ignoring the fields that don't really matter). There has to be a very simple clue here that I am missing. Maybe again because I don't know what fields are in which tables - we need the tables identified.

• I can't control what error messages are output to the user.

• Correction, it is a Long Integer data type.

• raman's idea: I guess I don't know how to implement that. I never said I was a query guru, that's why I'm here looking for help. If there's a way to store the query in Access and call it using ADO or even MS Query and send the Prop_KEY to pull the data, I'm all ears.

• It is strange indeed. The query works fine in Access using DMax, but if attempted using ADO in VBA then I get the error. I used single quotes because the query string is encapsulate in double quotes. I suppose i could try chr(34) instead to see if that makes a difference. Maybe I do have the join incorrect as well. This join seemed to yield the correct results.

• At the end of the day, here are the fields that matter:

tblComments:
WH_IDX - Long Integer - Unique property id (not the primary key).
commentDate - date/time - Date in which comment applies to.

tblDailyReadings:
Prop_KEY - Long Integer - Unique property id (not the primary key).
ReadingDate - date/time - Date in which daily reading applies to.
Field9 - Double - Daily readings for properties.

 

[xenou]

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

I would probably write the SQL this way:

SELECT 
    dr.ReadingDate, dr.Field1, dr.Field2,
    dr.Field3, dr.Field4, dr.Field5, dr.Field6, 
    dr.Field7, dr.Field8, dr.Field9, dr.Field10,
    dr.Field11, dr.Field12, dr.Field13, dr.Field14,
    dr.Field15, dr.Prop_KEY,
    (SELECT MAX(tblComments.commentDate) FROM tblComments 
        WHERE tblComments.WHX_IDX = [B]99[/B]) AS MaxValComment
FROM 
    tblDailyReadings As dr
WHERE 
    dr.Prop_KEY = [B]99[/B]

The subquery for max comment date for comments would return NULL if no matches are found. Recommend testing it "raw" this way with a known prop_KEY that will return a max value - a little easier than going to the trouble of work with the VBA code which would be more susceptible to bugs or at least typos and syntax errors.

I'm still a little confused about why your original code (SQL) uses a right join on dates. From your descriptions, the dates are not part of the relationship between the table or in any case not necessary to get the data you want to get.



If it works, my literal ID of 99 would have to be substituted with your variable prop_KEY:

"SELECT dr.ReadingDate, dr.Field1, dr.Field2, " _
        & "dr.Field3, dr.Field4, dr.Field5, " _
        & "dr.Field6, dr.Field7, dr.Field8, " _
        & "dr.Field9, dr.Field10, dr.Field11, " _
        & "dr.Field12, dr.Field13, dr.Field14, " _
        & "dr.Field15, dr.Prop_KEY, " & _
        & "(SELECT MAX(tblComments.commentDate) FROM tblComments WHERE tblComments.WHX_IDX = " & Prop_KEY & ") AS MaxValComment " _
        & "FROM tblDailyReadings as dr " _
        & "WHERE dr.Prop_KEY = " & prop_KEY
        & ";"

Note that as far as having the query in Access, you could indeed write the query in Access using a parameter. Then when querying with ADO you supply the parameter value. All things being equal, I think that this is easier with DAO, however (which is a little more Access-friendly as a rule).

 

[bs0d]

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

Thanks for the feedback. Looks like a sub-query may be best rather than the domain aggregate function. I'll test to make sure it produces the expected output.

You mentioned the Access query using a parameter. Can you show me an example of a query requiring a parameter in Access, then passing the required parameter to the query in VBA?

 

[xenou]

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

Sure. To use an example parameter query, first create a parameterized query in Access. This page gives a good explanation:
Access Tips: Parameter Queries

I *always* use the query parameters window to explicitly type my parameters. On the same page see the section entitled "Asking the Questions in the Right Order". This will allow you to name the datatypes for the query parameters.

Once you have a query you can just use ADO to provide a parameter. Note that in this case we call the query by name, so it is akin to running a simple stored procedure.

[COLOR="Navy"]Sub[/COLOR] ImportRecordsetACE()
[COLOR="Navy"]Dim[/COLOR] cn [COLOR="Navy"]As[/COLOR] ADODB.Connection
[COLOR="Navy"]Dim[/COLOR] cmd [COLOR="Navy"]As[/COLOR] ADODB.Command
[COLOR="Navy"]Dim[/COLOR] param [COLOR="Navy"]As[/COLOR] ADODB.Parameter
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] ADODB.Recordset

[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]

    [COLOR="SeaGreen"]'//Create ADO Objects[/COLOR]
    [COLOR="Navy"]Set[/COLOR] cn = CreateObject("ADODB.Connection")
    [COLOR="Navy"]Set[/COLOR] rs = CreateObject("ADODB.Recordset")
    [COLOR="Navy"]Set[/COLOR] cmd = CreateObject("ADODB.Command")
    
    [COLOR="SeaGreen"]'//Open a connections[/COLOR]
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.ConnectionString = "C:\myTemp\ADO_DAO_Examples\db1.mdb"
    cn.Open

    [COLOR="SeaGreen"]'//Set cmd object attributes and parameters[/COLOR]
    cmd.ActiveConnection = cn
    cmd.CommandText = "Query1"
    [COLOR="Navy"]Set[/COLOR] param = cmd.CreateParameter("Enter ID", adInteger, adParamInput, 5)
    param.Value = 2
    cmd.Parameters.Append param
    [COLOR="Navy"]Set[/COLOR] param = [COLOR="Navy"]Nothing[/COLOR]

    [COLOR="SeaGreen"]'//Go[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = cmd.Execute()
    Sheet1.Range("A1:AZ10000").Clear
    Sheet1.Range("A1").CopyFromRecordset rs
                    
                    
My_Exit:
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] rs.State = 1 [COLOR="Navy"]Then[/COLOR]
        rs.Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Set[/COLOR] cmd = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] cn [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] cn.State = 1 [COLOR="Navy"]Then[/COLOR]
        cn.Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Set[/COLOR] cn = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                    

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

For the sake of completeness here's an example using DAO instead of ADO:

[COLOR="Navy"]Sub[/COLOR] DAOParamTest()
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] DAO.Workspace
[COLOR="Navy"]Dim[/COLOR] db [COLOR="Navy"]As[/COLOR] DAO.Database
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset
[COLOR="Navy"]Dim[/COLOR] qdf [COLOR="Navy"]As[/COLOR] DAO.QueryDef
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]


    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
    
    [COLOR="SeaGreen"]'DAO Workspace, Database, and Query Objects[/COLOR]
    [COLOR="Navy"]Set[/COLOR] ws = DBEngine(0)
    [COLOR="Navy"]Set[/COLOR] db = ws.OpenDatabase("C:\myTemp\TestDB2.mdb")
    [COLOR="Navy"]Set[/COLOR] qdf = db.QueryDefs("Query1")
    
    [COLOR="SeaGreen"]'Set Parameter[/COLOR]
    qdf.Parameters("[Enter ID]") = 2
    
    [COLOR="SeaGreen"]'Open Recordset[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = qdf.OpenRecordset()
    
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs.EOF [COLOR="Navy"]Then[/COLOR]
        
        [COLOR="SeaGreen"]'Clear old data (if rs always returns the same # of rows this isn't needed)[/COLOR]
        Sheet1.Cells(1, 1).CurrentRegion.ClearContents
        
        [COLOR="SeaGreen"]'Write Headers[/COLOR]
        [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] rs.Fields.Count - 1
            Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Name
        [COLOR="Navy"]Next[/COLOR] i
        
        [COLOR="SeaGreen"]'Write Data[/COLOR]
        Sheet1.Cells(2, 1).CopyFromRecordset rs
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]


[COLOR="SeaGreen"]'Close Workspace and clean up objects[/COLOR]
My_Exit:
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
ws.Close
[COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Set[/COLOR] qdf = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Set[/COLOR] db = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Set[/COLOR] ws = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
MsgBox Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[bs0d]

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

Is there a way to pass the parameter to the query without a dialog box? Like in my case, I already have the parameter value stored in a Prop_KEY variable.

 

[xenou]

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

Hi,
yes, that's a simple tweak to the code.

In ADO, from above example:

param.Value = 2

Change to:

param.Value = my_var

In DAO, from above example:

qdf.Parameters("[Enter ID]") = 2

Amend to:

qdf.Parameters("[Enter ID]") = my_var

Where my_var is your variable (in this case, prop_KEY)

 

[bs0d]

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

Hi,
yes, that's a simple tweak to the code.

In ADO, from above example:

param.Value = 2

Change to:

param.Value = my_var

In DAO, from above example:

qdf.Parameters("[Enter ID]") = 2

Amend to:

qdf.Parameters("[Enter ID]") = my_var

Where my_var is your variable (in this case, prop_KEY)

Thanks again. This changes a lot for me, I feel I got a better understanding about the versatility in queries. It seems to me it would probably be easier to just store all of your queries in Access and target the queries by name in your code in VBA / ADO / DAO. Any chance the queries execute faster or slower if you reference a query that exists in access, vs. having the full SQL in your code?

 

[xenou]

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

Hi,

Thanks again. This changes a lot for me, I feel I got a better understanding about the versatility in queries. It seems to me it would probably be easier to just store all of your queries in Access and target the queries by name in your code in VBA / ADO / DAO. Any chance the queries execute faster or slower if you reference a query that exists in access, vs. having the full SQL in your code?

I don't know for sure. In my opinion there would be no noticeable difference - Access will probably parse the query very quickly even if you are passing it a raw string just the same as if it were saved in Access already. That said it is a matter of choice whether you prefer to keep the queries in Access and call them by name (simplifies the code, makes it easier to work with the queries, and allows for re-use of the queries) or to store the SQL in the code (which creates a self-documenting code and keeps the database uncluttered).

 

[bs0d]

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

So I finally had a chance to test this query, and it doesn't appear to work. My test is directly in Access with a literal ID hard-coded that is known to have values in tblComments.

Here is what the query is doing:
MaxValComment field is returning the max date from tblComments for the desired property ID. It is returning this on every row of data returned from tblDailyReadings.

Here is what I need it to do:
MaxValComment field should return the max value from dr.Field9 for each corresponding date that exists in tblComments.

So, for example, a comment exists in tblComments on date 1/1/2014 for Prop_KEY 99 (WH_IDX 99 in tblComments). Assume the max value in dr.Field9 = 1500. In the results of my query (daily data), on 1/1/2014, I need the MaxValComment to show 1500. If no other comments exist for any dates, MaxValComment is NULL on all rows except 1/1/2014. Also note, should that max value be less than 1, I need it to return 1.

 

[xenou]

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.