ACCESS the woes of it all

I was given an interesting project not too long ago.  A project that is best suited for working in an TSQL environment, but it was to be created in ACCESS.  While the source tables come from MS SQL server, the various queries on the data are stored in an ACCESS project.  I needed to be able to send perameters to an ACCESS query and an SQL stored procedure (relatively the same approach).  Sounds straight forward yes?  Not exactilly, even the Access 2000 Developers Handbook Vol 1 and Vol 2 only hinted at the approach to take.  Finally in an obscure blog dated 1999, I found the solution…and I pass that on to anyone who enjoys reading blogs…
This is a simple example, in VBA, of how to gather, in this case a single return value, from an ACCESS query.  The key for all of this, lies in the use of the DAO object.  I hope that if anyone else is working in ACCESS and is creating an MSSQL / ACCESS query hybrid…while the documentation for accessing the SQL stored procedure is easy to find…this was a challange.  Really, the main problems come in decerning when to use the DAO or ADODB objects.
Private Function GetEffectDate(strEmployee As String, strPosition As String) as String
    Dim db As DAO.Database
    Dim qdf As QueryDef, rst As DAO.Recordset
        Set db = CurrentDb()     ‘ Tie into the Access Database engine…
        Set qdf = db.QueryDefs("qry_SelectEffectDate")      ‘ DBEngine(0)(0).QueryDefs("qry_SelectEffectDate")
         qdf.Parameters("employee") = Trim(strEmployee)   ‘ Now, set the parameters as defined in the query.
         qdf.Parameters("position") = Trim(strPosition)
        On Error Resume Next
        Set rst = qdf.OpenRecordset(dbOpenDynaset)       ‘ run the query with the variables
        If rst.RecordCount = 0 Then                                    ‘ view the result set
        GetEffectDate = Null
        GetEffectDate = rst("effect_date")                         ‘ get the value we queried for.
        End If
        Set qdf = Nothing
        Set db = Nothing
        Set rst = Nothing
End Function
What does the  query look like?  qry_SelectEffectDate contains the following TSQL information.  From <Queries> <Design View> <SQL View>
PARAMETERS [employee] Short, [position] Text ( 255 );
SELECT TOP 1 First(RR.effect_date) AS effect_date
FROM dbo_rogerrabbit AS RR
WHERE (((RR.employee)=[employee]) AND ((RR.position)=[position]))
GROUP BY RR.effect_date
ORDER BY RR.effect_date DESC;
This entry was posted in Computers and Internet. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s