Member Avatar

I am developing a VB6 application which connects with an Access 2010 database.
I want the application to execute queries stored in the database.
Some of the queries include Access functions in field names.
For example- FieldName: IIf(Nz([AnotherFieldName],0)=0,3,[AnotherFieldName])"
Is there a way in VB6 to execute the queries natively in the Access database?
A code example would be very helpful.

Member Avatar

IIf(Nz([AnotherFieldName],0)=0,3,[AnotherFieldName])

I'm not sure what the above is for...

In vb6, open connection. open recordset. code should look similar to this -

If rs!Fieldname1 = rs!fieldname2 Then
    ''code here to handle returned boolean of true
    Msgbox "field1 is equal to field2"
End If
Member Avatar

do you mean executing triggers what we have in other database engine ?

Edited by rishif2
Member Avatar

@Andre,

The NZ function allows for substituting a Value for a Null. Something like this:

IIf(Nz([AnotherFieldName],0)=0,3,[AnotherFieldName])

is analogous to:

If [AnotherFieldName] is Null Then
   [AnotherFiledName] =0
End If

If  [AnotherFieldName]  = 0 Then
    Return 3
Else
    Return [AnotherFieldName]
End If

OP:

The expression should be processed by the database engine as it is stored in Access, so I do not believe it should be any different than running any other selection query. Say the Access Query is named Q1, the the SQL query should be something like: "Select * From Q1"

Member Avatar

Hi,

Try something like this...

"Select IIF(IsNull(MyFldName), 0, MyFldName) From MyTable"

Regards
Veena