Access 2007 ADP: incompatible ADO code

[Tom van Stiphout]

The code below works in Access 2000, not in Access 2007.
ADO returns that it supports AddNew, but when I call that method it gives me
an error: "Object or provider is not capable of performing requested
operation". That seems a contradiction.
The exact same code in the exact same ADP running on A2000 works fine.
rs.Update on existing records works fine.

Insights, anyone?

Sub TestAddNew()
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "exec SelectCandidatesForReportScoring @intExamID=4115,
@intCandidateExamID=-1, @intExamTestTypeID=1", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

'This produces expected rows
'Debug.Print rs.GetString

'This says it supports AddNew
Debug.Print "Supports AddNew: " & rs.Supports(adAddNew)

'This fails with above error.
rs.AddNew
'TODO: Fill out required fields.
rs.Update

rs.Close
End Sub

-Tom.

[The Frog]

Hi Tom,

I had a strange error like this working with an Access 97 Database and
some ADO code that I was taking the other way (from 2k back to 97). I
found that the MDAC version installed on the machine was corrupt /
broken. I re-installed the latest MDAC and the issue seemed to go
away. I did also have to remake the references again too but since
then I have had no issues with it.

I dont know if this will help or not, but it probably cant do any
harm :-)

Cheers

The Frog

[Sascha Trowitzsch]

Hi Tom,

Which server version are you running?
To me it looks like if the thrown error would be somewhere located in the
provider engine. Are you using SQL Native Client? In this case I'd first try to
install SQL2005 SP2 and experiment with this package:
http://www.microsoft.com/downloads/d...1-9e8b9d835cdc
Another attempt would be not to use currentproject.connection but to create a
new one and test if the same error occurs. Maybe there are some connection
settings that affect this behaviour?

Sascha

"Tom van Stiphout" <tv******************@kinetik-it.comschrieb im Newsbeitrag
news:13*************@corp.supernews.com...

The code below works in Access 2000, not in Access 2007.
ADO returns that it supports AddNew, but when I call that method it gives me
an error: "Object or provider is not capable of performing requested
operation". That seems a contradiction.
The exact same code in the exact same ADP running on A2000 works fine.
rs.Update on existing records works fine.

Insights, anyone?

Sub TestAddNew()
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "exec SelectCandidatesForReportScoring @intExamID=4115,
@intCandidateExamID=-1, @intExamTestTypeID=1", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

'This produces expected rows
'Debug.Print rs.GetString

'This says it supports AddNew
Debug.Print "Supports AddNew: " & rs.Supports(adAddNew)

'This fails with above error.
rs.AddNew
'TODO: Fill out required fields.
rs.Update

rs.Close
End Sub

-Tom.