Create a Read/Write Connection, Updateable recordset?

[HumanJHawkins]

Hi,

I have correct permissions and have successfully updated my data using
another method. However, the other method requires the field to be a
part of the recordset bound to my form (i.e. loads all records on
open). And the field is way to big to keep doing it this way.

So I want to create a separate connection as needed to update this
field. I am trying the following, and it keeps telling me that the
recordset is not updateable. (I can read but not write)

Function InputFile(sField As String, sFileName As String)
Dim FileConnection As New ADODB.Connection
Dim FileRecordset As ADODB.Recordset
Dim sFileSQL As String

' Connect with the current connection string so permissions are
' governed by app level settings. (Supports Windows
Authentication)
FileConnection.Mode = adModeReadWrite
FileConnection.Open CurrentProject.Connection

sFileSQL = "SELECT pkResource, " & sField & " " & _
"FROM Resource " & _
"WHERE pkResource = " & Me.pkResource

Set FileRecordset = FileConnection.Execute(sFileSQL)

FileRecordset.Find ("pkResource = " & Me.pkResource)

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile sFileName
FileRecordset.Fields(sField).Value = mstream.Read
FileRecordset.Update

' Clean up
FileConnection.Close
Set FileConnection = Nothing
Set FileRecordset = Nothing
End Function

Any help would be much appreciated.
Thanks!

 

[Victor Koch]

Hi HumanJHawkins,

Try this

Function InputFile(sField As String, sFileName As String)

Dim FileConnection As New ADODB.Connection
Dim FileRecordset As New ADODB.Recordset '<==
Dim sFileSQL As String

' Connect with the current connection string so permissions are
' governed by app level settings. (Supports Windows
Authentication)
FileConnection.Mode = adModeReadWrite
FileConnection.Open CurrentProject.Connection

sFileSQL = "SELECT pkResource, " & sField & " " & _
"FROM Resource " & _
"WHERE pkResource = " & Me.pkResource

FileRecordset.Open sFileSQL, FileConnection, adOpenDynamic,
adLockOptimistic, adCmdText '<===

if FileRecordset.EOF = False then

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile sFileName
FileRecordset.Fields(sField).Value = mstream.Read
FileRecordset.Update

End If

' Clean up
FileRecordset.Close '<===
FileConnection.Close
Set FileConnection = Nothing
Set FileRecordset = Nothing
End Function

 

[HumanJHawkins]

Hi HumanJHawkins,

Try this

<CUT>

Worked like a charm. Thanks!