dhodgdon
Posts: 80
|
| Posted: 09/30/2005, 6:51 AM |
|
I do this using the Execute Method. Because I don't write SQL very well, I build the query in Access. I then copy the SQL that Access generates and run it in the Execute Method. I found that it is easiest if you assign the SQL to a function variable first and run the Execute Method using the variable. Keep in mind that you will have to edit the SQL to make it work, like double quoting the " character, etc.
There is one caveat to this though. Access has functions that are a superset of ODBC functions and will not work if you use ODBC as your database connection. I get around this by using the JET database engine on the server instead of ODBC. For a more detailed discussion on this, I entered detailed information in the Tips and Solutions section of this forum titled "Sums, Averages and other calculated values" http://forums.codecharge.com/posts.php?post_id=59537. If you don't use Access specific functions you can continue to use ODBC.
To accomplish what you want to do, you would place the Execute Method in an event that occurs after the data is written to the database, (eg. Server After Update or Server Before Unload).
Here is example code based on Access generated SQL for an Update Query. This SQL must use the JET engine because of use of the Access DAvg function. I double quoted all " characters and used the &_ wrapping append character to make the code more readable.
davgfunction = "UPDATE Sessions INNER JOIN Results ON Sessions.SessionID = Results.SessionID SET " &_
"Sessions.OverallQuality = DAvg(""[ovrallsesquality]"",""results"",""[sessionid]='" & urlsession & "'""), " &_
"Sessions.OverallClarity = DAvg(""[ovrallclarity]"",""results"",""[sessionid]='" & urlsession & "'""), " &_
"Sessions.OverallRefMaterials = DAvg(""[ovrallrefmaterials]"",""results"",""[sessionid]='" & urlsession & "'""), " &_
"Sessions.OverallPresMaterials = DAvg(""[ovrallpresmaterials]"",""results"",""[sessionid]='" & urlsession & "'""), " &_
"Sessions.OverallPrework = DAvg(""[ovrallprework]"",""results"",""[sessionid]='" & urlsession & "'""), " &_
"Sessions.OverallSpeaker = DAvg(""[ovrallspkrquality]"",""results"",""[sessionid]='" & urlsession & "'""), " &_
"Sessions.OverallUsefulness = DAvg(""[ovrallusefulness]"",""results"",""[sessionid]='" & urlsession & "'""), " &_
"Sessions.NumOfResponses = DCount(""[ovrallsesquality]"",""results"",""[sessionid]='" & urlsession & "'"") " &_
"WHERE (((Sessions.SessionID)=""" & urlsession & """));"
dbconnection.open
dbconnection.Execute davgfunction
dbconnection.close
_________________
Regards,
David Hodgdon
|
