opening ADODB recordset

[mikevh]

What am I doing wrong here? I'm trying to update one
field in a table. This compiles okay, but I'm getting a run-time error on the "rs.Open":

Error number -2147217904 (80040e10)
No value given for one or more required parameters.

I'm used to DAO, not ADO, but thought I'd give it a
try. I've been fooling with this for at least an hour,
just can't see what the problem is. Any help much
appreciated. Thanks.

Sub Update_SeqNo()
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim strSql As String
    Dim Cnt As Integer
    strSql = "Select * from AdAgeSort Order by Country, Zip, Bus-Ind, Ttl-Code;"
    Debug.Print strSql
    
    Set cnn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    rs.Open Source:=strSql, _
        ActiveConnection:=cnn, _
        CursorType:=adOpenForwardOnly, _
        LockType:=adLockPessimistic, _
        Options:=-1
    
    Cnt = 0
    While Not rs.EOF
        Cnt = Cnt + 1
        rs!SeqNo = Cnt
    Wend
    
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    
End Sub

 

[PauloRico]

I don't know if this relates to your error, but you will need to put

rs.MoveNext

within your loop. Currently it is endless (rs.EOF will never occur).

 

[cmmrfrds]

You can make an instance of the object at the same time as you declare it or do it later like you did with the recordset.

Dim cnn As New ADODB.Connection

or
Set cnn = New ADODB.Connection

Most of the time working in Access it is better to have a static client side cursor and lockoptimistic.
CursorType:=adOpenStatic, _
LockType:=adLockOptimistic, _

Also
rs.cursorlocation = adUseClient

 

[Norris68]

It sounds like the problem is with your SQL statement "Select * from AdAgeSort Order by Country, Zip, Bus-Ind, Ttl-Code;". Have you spelt all of the field names correctly?

BTW, for clarity I tend to put the SQL keywords in upper case e.g. "SELECT * FROM AdAgeSort ORDER BY Country, Zip, Bus-Ind, Ttl-Code;"

 

[DaltonVB]

Here is what I would do. If you put brackets around your sql string table name and field names, that will help. The rest is just style differences. I would use a for next loop.

Private Sub Update_SeqNo()
    Dim i as Integer
    Dim iRecCount As Integer
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim strSql As String

    strSql = "  SELECT * " _
           & "    FROM [AdAgeSort] " _
           & "ORDER BY [Country], " _
           & "         [Zip], " _
           & "         [Bus-Ind], " _
           & "         [Ttl-Code];"

    Set rs = New ADODB.Recordset
    Set cnn = CurrentProject.Connection
    rs.Open strSql, cnn, adOpenStatic, adLockReadOnly

    iRecCount = rsTable.RecordCount
    If rs.EOF Then
        Exit Function
    End If

    for i = 1 to iRecCount
        rs!SeqNo = i
        rs.MoveNext
        'Just in case
        If rs.EOF then
            Exit For
        End If
    Next

    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

 

[DaltonVB]

Sorry for the double post. I just realized there were a couple of logic errors in my code.

you need to open the recordset as somthing that can be written to and not "adLockReadOnly". I use "adLockBatchOptimistic".

Then, after you change the value, do an update.

    for i = 1 to iRecCount
        rs!SeqNo = i
        rs.UpdateBatch
        rs.MoveNext
        'Just in case
        If rs.EOF then
            Exit For
        End If
    Next