Ado Seek - Error 3251

When I try to use .addnew appers "Error 3251 This operation requested by the application is not supported by the provider".
Why? I must use this code to syncronize the recordset.
This method is OK for SqlServer?
There is a better method?

Dim rs As New ADODB.Recordset ' file xml
Dim rs2 As New ADODB.Recordset
Dim tmpfld As ADODB.Field
Dim sADO As String

sADO = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db2000.mdb" & ";Persist Security Info=False"
rs2.Index = "PrimaryKey"
rs2.CursorType = adOpenDynamic
rs2.Open "Tabella2", sADO, , , adCmdTableDirect
rs.Open App.Path & "\file.xml", "Provider=MSPersist"

rs.MoveFirst
While rs.EOF = False
     rs2.Seek Array(rs("CampoTesto")), adSeekFirstEQ
     If rs2.EOF Then
            rs2.AddNew  'THIS IS THE POINT!!!!!!!
     End If
     For Each tmpfld In rs.Fields
            'Debug.Print tmpfld.Name & " " & tmpfld.Value
            Select Case tmpfld.Name
               Case Else
                  If rs(tmpfld.Name).Type = 202 Then
                       If IsNull(rs(tmpfld.Name)) Then
                            rs2(tmpfld.Name) = ""
                         Else
                            rs2(tmpfld.Name) = rs(tmpfld.Name)
                       End If
                     Else
                       rs2(tmpfld.Name) = rs(tmpfld.Name)
                  End If
     End Select
     Next
     rs2.Update
     rs.MoveNext
  DoEvents
Wend

rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing  
maurizioAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TimCotteeConnect With a Mentor Commented:
Glad to help maurizio.

In terms of whether this is the best way or not, it is difficult to answer without knowing your precise requirements. If you are basically attempting to take a snapshot of your sql server table at regular intervals and there is a path to SQL from your access mdb i.e. they are on the same network then perhaps a more straightforward method would be to create an "Import Table" in access. This could be done easily through VB, including dropping the existing table and re-importing. Alternatively again you could create a "Link Table" in access which is effectively a window onto your SQL table.

If you do not have any way of doing either of these things as the SQL table is remote then the method you are using or some minor variation is probably as good as any.

0
 
TimCotteeCommented:
You have defaulted the values in the rs2.open line, it should read:

rs2.Open "Tabella2", sADO,adOpenDynamic ,adLockOptimistic, adCmdTableDirect

0
 
TimCotteeCommented:
You have set the cursor type to adOpenDynamic but have not set the locking so it defaults to adLockReadOnly
0
 
maurizioAuthor Commented:
Thanks for your comment, for me it' s the solution!

I accept comment as answer,
this code is used to synchronize a sql-server to access database send via email with xml-file. It's a correct method?there is a fast method in vb?

Thanks



 
0
All Courses

From novice to tech pro — start learning today.