Help to update a value in an SQL 2000 table from Access code

[richardb]

I could use help please debugging my attempt to update a value in an MS SQL
table. My code attempt (below) yields "illegal operation" before reach
"debug.print point a". I can put the SQL string into a pass through query and
it works. Thank you.

Public Sub SetEpisodeFacility(EpisodeID As String, ProvCode As String)

On Error GoTo ErrorHandler
Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset
Dim sqltext As String, strConnect As String, Facility$
Set mydb = CurrentDb
Set myq = mydb.CreateQueryDef("")

Select Case ProvCode
Case "N04", "NE04"
Facility$ = "J"
Case "NB04"
Facility$ = "B"
Case "NM01", "NM10", "NM60", "NM61"
Facility$ = "M"
Case "RMB"
Facility$ = "LHG"
Case Else
Facility$ = ""
End Select

sqltext = "UPDATE InsuranceA SET FacilityCode = '" & Facility$ & "' " & _
"WHERE EpisodeID = '" & EpisodeID$ & "' AND LEN(FacilityCode) = 0 ;"

strConnect = "ODBC;DSN=PPM_700;;" & _
"Network=DBMSSOCN;Trusted_Connection=Yes"

With myq
.ReturnsRecords = False
.Connect = strConnect
.SQL = sqltext
Set myrs = .OpenRecordset
Debug.Print "point a"
End With

With myrs
.Update
.Close
End With

SetEpisodeFacilities_Exit:
Set myrs = Nothing
myq.Close
Set myq = Nothing
Set mydb = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Number & " - " & Error$, vbCritical, "Error in
SetEpisodeFacilities"
Resume SetEpisodeFacilities_Exit

End Sub