error while attempting to open recordset

[Stelvio Gori - DecisionWorks consulting]

Hi everyone and thanks in advance for your help.

I'm trying to open and update an Access recordset using ADO, by using the
following code:

************
Public Sub UpdateAutore()
Dim rs As Recordset
Dim strP As String

' utilizzo un blocco non read-only per poter modificare il record

Set rs = New ADODB.Recordset
rs.Open "Prova1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic


rs.MoveFirst
strP = rs.Fields("Campo1")
' Debug.Print strP
rs.MoveNext

' esegue loop sul recordset
Do Until rs.EOF
If IsNull(rs.Fields("Campo1").Value) = True Then
rs.Edit
rs.Fields("Campo1").Value = strP
' Debug.Print rs.Fields("Campo1")
Else: rs.Edit
strP = rs.Fields("Campo1").Value
End If
rs.MoveNext
Loop
rs.Close

End Sub
************

but I get an error in code line 5 (rs.Open ....): "Impossible to find method
or data member".
I'm trying to use this code reported on a Access Guide: I can open the
recordset when usind DAO but I get the problem when using ADO (as it is in
the code above).
Could you help?

Thanks,

Stelvio

 

[Van T. Dinh]

Try changing the dec to:

Dim rs As ADODB.Recordset

and make sure the ADO (2.X) Library is included in your References.

 

[Tom Wickerath]

To add to Van's answer, see an article I wrote on this topic:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Stelvio Gori - DecisionWorks consulting]

Thanks Van and Tom,
I don't know what exactly caused the error but:
- I disabled DAO control in References
- dim as ADODB.Recordset

and now the Sub works perfectly.

At this point, what code have I to use to call it from a Form button? I
tried DoCmd.OpenModule but it actually opens the editor instead of running
the subroutine.

Thanks again,
SG


Il 28-04-2006 6:06, nell'articolo
(e-mail address removed), "Tom Wickerath" <AOS168b
AT comcast DOT net> ha scritto:

 

[Tom Wickerath]

Hi Stelvio,

You can use something like this, for a command button named "cmdUpdateAutore":

Private Sub cmdUpdateAutore_Click()
On Error GoTo ProcError

UpdateAutore

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdUpdateAutore_Click..."
Resume ExitProc
End Sub


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi Stelvio,

You can use something like this, for a command button named "cmdUpdateAutore":

Private Sub cmdUpdateAutore_Click()
On Error GoTo ProcError

UpdateAutore

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdUpdateAutore_Click..."
Resume ExitProc
End Sub


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Stelvio Gori - DecisionWorks consulting]

That was perfect. Sometimes easy answers are difficult to get,
thanks
SG


Il 28-04-2006 7:47, nell'articolo
(e-mail address removed), "Tom Wickerath" <AOS168b
AT comcast DOT net> ha scritto:

 

[Tom Wickerath]

You're very welcome.

Good luck on your project!


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________