[dba-VB] Command Objects within Data Environments

I like to handle this type of task using static functions, rather than
rebuilding the SQL. Write a static function called CurrentEmployee() or
somesuch, then write the SQL something like:

SELECT someColumns FROM tblEmps WHERE EmployeeID = CurrentEmployee()

Then all you need to do is assign a new value to the static function and
requery.

Here's the template I use when creating static functions. Just follow the
directions.

Static Function CurrentX(Optional lngNew As Long) As Long
'Copy this template each time you need a new set/get function
'Then Replace "X" with the name of your object, i.e.,"Employee"
'Replace all in current proc and you're done.

    Dim lngCurrent As Long
    Select Case lngNew
        Case Is < 0
            lngCurrent = 0
        Case Is > 0
            lngCurrent = lngNew
        Case Else
            'just return the current value
    End Select
    
    CurrentX = lngCurrent
    #If conDebug = 1 Then
        Debug.Print "Current X: ", CurrentX
    #End If
End Function

To assign a value, call it like this:

CurrentX(12345) 

To obtain the current value elsewhere, write:

? CurrentX()

Hth,
Arthur

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: June 16, 2003 11:06 AM
To: dba-vb at databaseadvisors.com
Subject: [dba-VB] Command Objects within Data Environments


To all,

I have a DataEnvironment (DEEmploy), which has a command tblEmps which uses
a SQL Statement to get all the records from tblEmps like so :

SELECT * FROM tblEmps

Now I have reached a point where I only want to bring one employee back from
the back-end, so what I need to know is can I build a SQL statement on the
fly (something like (SELECT TOP 1 * FROM tblEmps) or (SELECT * FROM tblEmps
WHERE (Criteria)) ) and assign that select statement to the Command object
tblEmps so that it only returns employees that meet the criteria thats been
built on the fly....

Thanks in advance for any help

Paul

__________________________________________________________________________
Join Freeserve http://www.freeserve.com/time/

Winner of the 2003 Internet Service Providers' Association awards for Best
Unmetered ISP and Best Consumer Application.


_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com