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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I got this to work by adding .execute in the "with myq" loop and eliminating
everything having to do with the myrs record set. I guess when you execute an
action query you don't open a record set (which makes sense because it
doesn't return anything). Comments are welcome anyway if anyone sees that i
still don't have the right idea. Thanks...

"richardb" wrote:

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


.