Need to set pass-thru query property

[Tom]

I'll bet I'm making this a lot harder than it has to be...

In this particular case I want to change the "ODBC connect str" property of my
pass-thru query. (And I'd like to know how to do it so I can any other property
which I might need to change.) Here's my code (GULP):

Public Function fncSetQryProp(strQryName As String, strPrpName As String, _
strPrpValue As String)
On Error GoTo Err_SetConnStrProp

Dim DAOqrydef As DAO.QueryDef
Dim DAOprp As DAO.Property

Set DAOqrydef = DAOdbs.QueryDefs(strQryName)

With DAOqrydef

DAOprp.Name = strPrpName
DAOprp.Value = strPrpValue

.Properties.Refresh

End With

Exit_SetConnStrProp:
On Error Resume Next
Exit Function

Err_SetConnStrProp:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& _
Err.Description, , "SetConnStrProp"
Resume Exit_SetConnStrProp

End Function

If someone could please help, I'd sure appreciate it.

Thanks in advance,

Tom

 

[Douglas J. Steele]

Try

DAOqrydef.Properties(strPrpName) = strPrpValue

instead of

With DAOqrydef

DAOprp.Name = strPrpName
DAOprp.Value = strPrpValue

.Properties.Refresh

End With


Where have you declared and instantiated DAOdbs?