Frothingslosh
Premier Pale Stale Ale
- Local time
- Today, 04:34
- Joined
- Oct 17, 2012
- Messages
- 3,276
Okay, on one form in an app I'm building, I'm getting notifications that records are showing #DELETED# in all fields after a data change.
I've narrowed down the cause to the form being based on a SQL Server view, and the specific data change immediately causing the record to be filtered out by the view, which Access interprets as being deleted.
My original fix was to replace the view with a query, but the form's performance becomes unacceptably sluggish (which was the reason for the view in the first place). When based on the view, performance is quick and instantaneous, while when based on a query, the form populates around 2 lines per second, with 12 lines to fill in, and it re-does the entire population every time you scroll up or down.
One solution I found was to change to using an ADODB connection instead, but while the view is updateable, I cannot got the connection to be updateable. No matter what I do, it is always non-updateable.
Yes, the view contains the a designated primary key, which is the PK of the primary table it's based on. There are two fields total from other tables, but none of those may be changed by the user. One (called RecoveryName) is displayed and is a business requirement in the form, and the other two are passed to the front end to allow users to filter by whomever the records are assigned to for processing.
Sorry I can't post a screen shot, but I'd have to edit so much out that it would be completely unhelpful. Basically, you have Recovery Name, Contract, Member first and last names, record status (a drop down with around a dozen different options), and a bunch of dates.
The code below opens up an ADODB recordset just fine - I just can't seem to make it updateable.
The commented code is from my various attempts at making this work correctly.
As I said, this opens a recordset perfectly fine, and the performance as the form is navigated is perfect, but I just cannot get this to result in an UPDATEABLE recordset.
If anyone can help me with this, I'll owe you an Olde Frothingslosh.
I've narrowed down the cause to the form being based on a SQL Server view, and the specific data change immediately causing the record to be filtered out by the view, which Access interprets as being deleted.
My original fix was to replace the view with a query, but the form's performance becomes unacceptably sluggish (which was the reason for the view in the first place). When based on the view, performance is quick and instantaneous, while when based on a query, the form populates around 2 lines per second, with 12 lines to fill in, and it re-does the entire population every time you scroll up or down.
One solution I found was to change to using an ADODB connection instead, but while the view is updateable, I cannot got the connection to be updateable. No matter what I do, it is always non-updateable.
Yes, the view contains the a designated primary key, which is the PK of the primary table it's based on. There are two fields total from other tables, but none of those may be changed by the user. One (called RecoveryName) is displayed and is a business requirement in the form, and the other two are passed to the front end to allow users to filter by whomever the records are assigned to for processing.
Sorry I can't post a screen shot, but I'd have to edit so much out that it would be completely unhelpful. Basically, you have Recovery Name, Contract, Member first and last names, record status (a drop down with around a dozen different options), and a bunch of dates.
The code below opens up an ADODB recordset just fine - I just can't seem to make it updateable.
Code:
Private Sub UpdateRecordset()
'FINDME
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim WhereClause As String
Dim SQL As String
' If Me.RecordSource = "" Then
' Me.RecordSource = "qryMembershipConfirmationData"
' Else
' Me.Requery
' End If
'Apply filter to ProcessorID if required.
If Me.cboProcessorID.Value <> "(ALL)" Then
WhereClause = " WHERE ProcessorBadgeID = '" & Me.cboProcessorID.Value & "'"
End If
'Apply filter to RecoveryID if required.
If Me.cboRecoveryLookup.Value <> -1 Then
If WhereClause = "" Then
WhereClause = " WHERE "
Else
WhereClause = WhereClause & " AND "
End If
WhereClause = WhereClause & "RecoveryID = " & Me.cboRecoveryLookup.Value
'Add additional restrictions if the form was opened by the UWS and has an OpenArg.
If Nz(Me.OpenArgs, "") <> "" Then
WhereClause = WhereClause & " AND DateDiff('d', MembershipChangeSubmitted, Date()) >= 14"
End If
End If
' If Not rs Is Nothing Then
' rs.Close
' Set rs = Nothing
' End If
'
' If Not cn Is Nothing Then
' cn.Close
' Set cn = Nothing
' End If
SQL = "SELECT * FROM VW_MembershipConfirmationData" & " " & Trim(WhereClause) & ";"
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
' Set .ActiveConnection = cn
' cn.Mode = adModeReadWrite
' .Source = "SELECT * FROM VW_MembershipConfirmationData" & " " & Trim(WhereClause) & ";"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
End With
rs.Open SQL, cn
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
' Me.RecordSource = "SELECT * FROM VW_MembershipConfirmationData" & " " & Trim(WhereClause) & ";"
End SubThe commented code is from my various attempts at making this work correctly.
As I said, this opens a recordset perfectly fine, and the performance as the form is navigated is perfect, but I just cannot get this to result in an UPDATEABLE recordset.
If anyone can help me with this, I'll owe you an Olde Frothingslosh.
