A97 & SQL2K - Recordset not updatable

  • Thread starter Thread starter Ron Hinds
  • Start date Start date
R

Ron Hinds

A97 FE <-> SQL2K BE. Using a QueryDef created on the fly using SQ
Pass-Through when an operation only involves server tables. Works great for
"Execute" statements, and when returning a recordset that I don't try to
modify. But I always get an error "object is read only" whenever I try to
use the "rs.Edit" method. I can't seem to find a property that will make it
updatable, either. Is it even possible? Here is the function I use to
execute the SPT's:

Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionally returns
records

On Error GoTo Error_SQLExecute

Dim qdf As QueryDef
Dim errAny As error

Set qdf = DBEngine(0)(0).CreateQueryDef("")

qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL

If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
End If

Exit_SQLExecute:
Set qdf = Nothing
Exit Sub

Error_SQLExecute:
If DBEngine.Errors.Count > 1 Then
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " =
" & errAny.Description, vbCritical, "Error " & errAny.Number
Next
Else
MsgBox Error, vbCritical, "Error " & Err & " - SQLExecute"
End If

Resume Exit_SQLExecute

End Sub
 
Ron said:
A97 FE <-> SQL2K BE. Using a QueryDef created on the fly using SQ
Pass-Through when an operation only involves server tables. Works great for
"Execute" statements, and when returning a recordset that I don't try to
modify. But I always get an error "object is read only" whenever I try to
use the "rs.Edit" method. I can't seem to find a property that will make it
updatable, either. Is it even possible? Here is the function I use to
execute the SPT's:
Click to expand...
< SNIP >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The recordset has to include PKs, FKs and required fields of the tables
you wish to update. If linking SQL'r tables to Access the SQL'r tables
require a PK or a unique column (usually a TimeStamp data type) or
Access will not allow updates, 'cuz it won't know which unique record to
update.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQH2J0oechKqOuFEgEQJuRgCfa8ZLLrsIpmma6ecdXXr8U6TZWVsAn0Fi
krCBOvOhgUo/gxX9OfLHU/rR
=uxPF
-----END PGP SIGNATURE-----
 
Back
Top