PRB: Recordset Based on a View does not Resync

• Microsoft SQL Server version 7.0
• Microsoft ODBC Driver for SQL Server, versions 3.6, 3.7
• Microsoft OLE DB Provider for SQL Server, version 7.0
• ActiveX Data Objects (ADO), version 2.1 SP2

SYMPTOMS

The ADO Resync method does not refresh the recordset if the recordset is based on a view.

CAUSE

The Metadata from a View is not returned, only the metadata on the base tables is returned. Therefore, the CCE(Client Cursor Engine) does not know how to generate the appropriate SQL commands to resync views.

RESOLUTION

Use the Requery method instead.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new Visual Basic Standard EXE project. Form1 is added by default.



2. From the Project menu, click References, and select the Microsoft ActiveX Data Objects.



3. Place two Command buttons on Form1, Command1 and Command2.



4. Paste the following code into the declaration section of Form1, modify the ConnectionString accordingly.




Option Explicit

Private Sub Command1_Click()
On Error GoTo CREATE_ERROR

Dim aConn As New ADODB.Connection
aConn.Open "PROVIDER=SQLOLEDB;SERVER=<Server_name>;INITIAL CATALOG=pubs;USER ID=<my_uid>;PASSWORD=<my_pwd>;"
On Error Resume Next   ' Ignore errors if View doesn't already exist
aConn.Execute "DROP VIEW TitleInfoView", , adCmdText ' Drop it in case we're recreating it
Err.Clear
On Error GoTo CREATE_ERROR
aConn.Execute "CREATE VIEW TitleInfoView AS SELECT titleauthor.*, titles.title_id as ID, titles.title AS Title FROM titleauthor, titles where titleauthor.title_id = titles.title_id", , adCmdText

MsgBox "New SQL Server View created successfully.", vbInformation
Exit Sub

CREATE_ERROR:
  MsgBox "Error encountered attempting to Create new MS SQL Server View." & vbCrLf & vbCrLf & "Error# = " & Err.Number & vbCrLf & "Error Description = " & Err.Description & vbCrLf & "Error Source = " & Err.Source & vbCrLf & "Error Line = " & Erl
  MsgBox Err.Description

End Sub

Private Sub Command2_Click()
On Error GoTo UPDATE_ERROR
Dim aConn As New Connection
aConn.CursorLocation = adUseClient
aConn.Open "provider=sqloledb;server=<server_name>;database=pubs;uid=<my_uid>"

Stop  ' Force debug mode

Dim aRs As New Recordset

aRs.LockType = adLockBatchOptimistic
aRs.CursorLocation = adUseClient
'Only get static
aRs.CursorType = adOpenStatic


aRs.Open "SELECT * FROM TitleInfoView", aConn, , , adCmdText
Set aRs.ActiveConnection = Nothing  ' Disconnect it


aRs.Properties("Update Resync") = adResyncNone
aRs.Properties("Unique Table") = "titleauthor"

Debug.Print

Debug.Print "*** BEFORE UPDATE ****"

Debug.Print "Title_ID = " & aRs.Fields("title_ID")
Debug.Print "Title = " & aRs.Fields("title")

'alternate between the next two lines of code to see the behavior.

aRs.Fields("title_ID") = "PS1372"
'aRs.Fields("title_ID") = "BU1111"

Set aRs.ActiveConnection = aConn
aRs.UpdateBatch


Debug.Print
Debug.Print "*** AFTER UPDATE ***"
Debug.Print "Title_ID = " & aRs.Fields("title_id")
Debug.Print "Title = " & aRs.Fields("title")

Debug.Print
Debug.Print
Debug.Print "*** EXPLICIT RESYNC ***"
Set aRs.ActiveConnection = aConn  ' Reconnect it
aRs.Resync  ' Explicitly issue a resync

Debug.Print "Title_ID = " & aRs.Fields("title_id")
Debug.Print "Title = " & aRs.Fields("title")

Debug.Print
Debug.Print
Debug.Print "*** EXPLICIT REQUERY ***"

aRs.Requery  ' Explicitly issue a Requery
aRs.MoveFirst
Debug.Print "Title_ID = " & aRs.Fields("title_id")
Debug.Print "Title = " & aRs.Fields("title")

Exit Sub  ' Exiting with no errors

UPDATE_ERROR:
  MsgBox "Error encountered during Update operation." & vbCrLf & vbCrLf & "Error# = " & Err.Number & vbCrLf & "Error Description = " & Err.Description & vbCrLf & "Error Source = " & Err.Source & vbCrLf & "Error Line = " & Erl

End Sub


Private Sub Form_Load()
Command1.Caption = "Creat View"
Command2.Caption = "Update SQL Server"
End Sub 

5. Run the Project.



6. Click Create View.



7. Click Update SQL Server.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Q190108 PRB: Error Updating adUseClient Cursor Based on MDB Query

© Microsoft Corporation November 4, 1999, All Rights Reserved.
Contributions by Terrell D. Andrews, Microsoft Corporation

Additional query words:

Keywords : kbole kbADO kbDatabase kbMDAC kbSQLServ kbSQLServ700 kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.1 SP2,3.6,3.7,7.0; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbprb