Modifying pass-through query at runtime

[Brian]

I need to send through this pass-through query to a DB2 database via ODBC:

UPDATE <StaticTableName> SET <StaticFieldName> = CAST(<OtherStaticFieldName>
AS BLOB) WHERE <StaticNameOfPrimaryKey> = <DataThatChangesAtRuntime>

Everything is static, including the ODBC connect string, except the last
item in the Where statement (DataThatChangesAtRuntime). How can I pass this
value to the pass-through query at runtime?

Or, alternatively, is there a way to place the entire pass-through SQL
statement in VBA, including the ODBC connect string?

 

[Brian Wilson]

I need to send through this pass-through query to a DB2 database via ODBC:

UPDATE <StaticTableName> SET <StaticFieldName> =
CAST(<OtherStaticFieldName>
AS BLOB) WHERE <StaticNameOfPrimaryKey> = <DataThatChangesAtRuntime>

Everything is static, including the ODBC connect string, except the last
item in the Where statement (DataThatChangesAtRuntime). How can I pass
this
value to the pass-through query at runtime?

Or, alternatively, is there a way to place the entire pass-through SQL
statement in VBA, including the ODBC connect string?

You can just modify the SQL property of the querydef. There is also a
..Connect property but if you say this doesn't need to be altered then you
can use the following function. I hope it is clear how you would call it:


Public Function DefineQuery(strQuery As String, _
strSQL As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQuery)

qdf.SQL = strSQL

DefineQuery = True

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

 

[Brian]

Thanks. That did it.

You can just modify the SQL property of the querydef. There is also a
..Connect property but if you say this doesn't need to be altered then you
can use the following function. I hope it is clear how you would call it:


Public Function DefineQuery(strQuery As String, _
strSQL As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQuery)

qdf.SQL = strSQL

DefineQuery = True

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function


.