How programically deal with a Query result set

[sesproul]

Hello,

I am new to ACCESS programming, but not to VB, I am familiar with Recordsets, and dealing with resultsets using ADO, but not with the inherent ACCESS commands.

Based on a button being pressed, I am trying to pass SQL to a database table with invoice data, (Dates and Value pairs) and then retrive the MAX Date, and based upon that date, create a new SQL statement and pass that value into a second QUERY.

Here is the problem I have,

ASSUME a basic SQL STATEMENT Like this:

"SELECT MAX(TBLINVOICE.DATE_INVOICE) as MAXDATE
FROM TBLINVOICE"

How do I retrieve the value from the first Query. MAXDATE, so that I can programically manipulate it?

I just need to see some code on how to retrieve the value from the result set.

Thanks


Steve

 

[lameid]

I would probably take the lazy way out on this one...

dmax("DATE_INVOICE", "TBLINVOICE")

There is alos a DMin and Dlookup function.

Alternately you could open it as a recordset. Just get your connection form the currentproject function (I haven't written enough ADO cold write as most of my data is JET so I stick with DAO)...

currentproject.connection

DAO...

Dim RS as DAO.Recordset
Dim dt as Date
Set RS = Currentdb.Openrecordset("SELECT MAX (TBLINVOICE.DATE_INVOICE) as MAXDATE " & _
    "FROM TBLINVOICE")

If Not RS.EOF Then
    dt = RS!MAXDATE
End If

To execute an action query...

Docmd.runSQL [i]<SQL Statement>[/i]

 

[sesproul]

thanks - I can definately use DAO if needed, how is the assignment done in the first code example?

dmax("DATE_INVOICE", "TBLINVOICE")


or does access just understand which tables are local to it, and the command accesses the table directly

i.e.

dim dtDate
dtDate = dmax("DATE_INVOICE", "TBLINVOICE")

 

[lameid]

Exactly it understands what tables are local... It will treat linked tables as local as well. The caveat here is the Database Engine is client side so if you are working with say SQL Server, Oracle, MYSQL etc., Access may do a full table scan as opposed to a more intelligent request. If you play here, you will want to check out "SQL Pass-Through Queries" or just use an ADO recordset.

Also generally speaking the "D" functions (Dmax etc.) are slow in comparison to using a query with a join... Some people will want to use the D functions on forms or reports which is usually a bad idea for performance reasons.

Noteworthy is SQL statements are a lot faster than recordsets so do as much in a SQL statement as possible.

Coming from VB, it may surprise you to know that Select statements against Jet data are generally updatable like views in SQL server (obviously you can't edit an aggregate query).

 

[sesproul]

Thanks for the help. This is just what I was looking for. I needed the basic starting points to get the ball rolling.


I'll look into these passthru queries, because I do have linked MYSQL tables that will ultimately be the backend of a PHP enabled web frontend. I populate the data into ACCESS first, then push the data into MYSQL. (It's just easier to build simple forms in ACCESS to get the application functionality created.) When I have manipulated the MYSQL directly, it does take a long time.


Steve

 

[lameid]

I forgot to mention something... SQL Pass Through Queries are not updateable... They may use an update statement to update data but the resulset is not updateable. They are great for reporting and a myriad of other things .