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.
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]
"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
& ";"
[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]
[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]
param.Value = 2
param.Value = my_var
qdf.Parameters("[Enter ID]") = 2
qdf.Parameters("[Enter ID]") = my_var
Hi,
yes, that's a simple tweak to the code.
In ADO, from above example:
Change to:Code:param.Value = 2
Code:param.Value = my_var
In DAO, from above example:
Amend to:Code:qdf.Parameters("[Enter ID]") = 2
Code: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?