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"

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) = ""
                            rs2(tmpfld.Name) = rs(tmpfld.Name)
                       End If
                       rs2(tmpfld.Name) = rs(tmpfld.Name)
                  End If
     End Select

Set rs = Nothing
Set rs2 = Nothing  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
You have defaulted the values in the line, it should read:

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

TimCotteeHead of Software ServicesCommented:
You have set the cursor type to adOpenDynamic but have not set the locking so it defaults to adLockReadOnly
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?


TimCotteeHead of Software ServicesCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.