troubleshooting Question

ADO recordset problem

Avatar of naqayya
naqayya asked on
Visual Basic Classic
14 Comments1 Solution342 ViewsLast Modified:
I use the following code in an Access Project (.adp) to open and edit a recordset:

----------------------------------------------------------

Set myCmd = New ADODB.Command
            myCmd.ActiveConnection = "Provider = SQLOLEDB; Data Source = myServer; Initial Catalog = myDB; User ID = user; Password=pwd;"
            myCmd.CommandType = adCmdStoredProc
           
            For i = txtFrom To txtTo
               
                myCmd.CommandText = "sp_mySP"
                myCmd.Parameters("@myPar") = i
               
                Set rs = New ADODB.Recordset
                rs.CursorType = adOpenDynamic
                rs.LockType = adLockOptimistic

                Set rs.Source = myCmd
                rs.Open
               
                If rs.EOF = False Then
                    With rs
                        Do While Not .EOF
                            myIDs(y) = !ID
                            y = y + 1
                            !RcptPrintDate = Date
                            .Update
                            .MoveNext
                        Loop
                    End With
                   
                    myCmd.CommandText = "sp_mySP2"
                    myCmd.Parameters("@myPar") = i
                    myCmd.Execute
                   
                    myCmd.CommandText = "sp_mySP3"
                    myCmd.Parameters("@myPar") = i
                    myCmd.Execute
                   
                Else
                    myErr(X) = i
                    X = X + 1
                End If
                rs.Close
                Set rs = Nothing
            Next i

----------------------------------------------------------

txtFrom and txtTo are integers.

I am having problems running this code on clients. The code is fine if there is only 1 loop in the For...Next loop, but if there is more than 1 loop I get an error on the 2nd loop at the line:

rs.Open

The error is "The requested properties cannot be supported".

Can you please tell me what the problem is and how to rectify it? Thanks.
ASKER CERTIFIED SOLUTION
aikimark
Get vaccinated; Social distance; Wear a mask
Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros