Updating a single record using ADODB 1

[Aelstro]

I've searched and searched the forum but have come up with nothing. I'm sure it is simple and I just missed something basic.

I have a table. In this table is a record.
I know what record it is by field name and row.

Using ADODB I want to update that single record.

i.e. NameTable
Name
Row 1 "BOB"

Change Row 1 of Name to &quot;Tim&quot; <- Pseudo code

Thank you for any and all help

 

[mikevh]

This seems more like the sort of thing you'd do with an
action query. Does this record have a key that you can use
to select it? Then you could say

UPDATE YourTable
SET Name = &quot;Tim&quot;
WHERE ID = (Whatever);

However, if you wanna do it using ADO, this may give you
some ideas. (I'm not an ADO expert. I'm still using
DAO mostly.)

Sub updatefirstrec()
    'Changes the field [day of week] in first record from
    'table "Sheet3b" to "Tim"
    
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection

    Dim strTblName As String
    Dim strFldName As String
    Dim strNew As String

    strTblName = "sheet3b"
    strFldName = "day of week"
    strNew = "Tim"
    
    Set rs = New ADODB.Recordset
    Set cnn = CurrentProject.Connection
    
    rs.Open Source:=strTblName, _
        ActiveConnection:=cnn, _
        CursorType:=adOpenForwardOnly, _
        LockType:=adLockOptimistic
        
    rs.MoveFirst
    rs.Fields(strFldName) = strNew
    rs.Update
    
    rs.Close
    cnn.Close
    
    Set rs = Nothing
    Set cnn = Nothing
    
End Sub

Hope this helps.

 

[Aelstro]

I think that may have given me a clue. I'll probably need to moveFirst and then for each row moveNext in a repeating loop. Seems like that would take a long time though.

The record I need to alter is actually at place 649 out of 740. I just gave the simple example so I wouldn't have to type 740 rows of information.

I guess what I'm looking for is a way to do the update query you suggested, but without the &quot;ID = whatever&quot;, and instead have a &quot;Row = 649&quot;. Any query method that let one do it by row and column number would suffice

 

[mikevh]

You really should not count on the position of a
particular record in a table. Tables are considered
&quot;unordered&quot; collections of records. If you
really must move to the 649th record, look into
the &quot;Move&quot; method of ADO recordsets.

Is there some other way to uniquely identify this
record you want to change? Is it for example, the
only one with the name &quot;Bob&quot;? Or do we want to
change all records with the name &quot;Bob&quot;?
Then, modifying my previous example slightly, you
could say

UPDATE YourTable
SET Name = &quot;Tim&quot;
WHERE Name = &quot;Bob&quot;;

 

[Aelstro]

Some more detail on the project: The field in the table is a list of invoices. Each invoice number can have multiple lines, so the invoice numbers can be repeated. Other than the repeats, the invoice number is unique.

So I am trying to write a script that goes through the Invoice table and updates the invoice number to include &quot;-1&quot;, &quot;-2&quot;, or &quot;-3&quot; for the line number on the invoice making it the primary key for the rest of the program.

Hence why I need to sort through and change it by rownumber (to match what we were doing previously in Excel, which uses row numbers in its formulas)

Thank you again for your time and efforts.

 

[mikevh]

So if your invoice numbers were like this

00001
00001
00001
00002
00003
00003
00004

then you'd want to end up with something like

00001-1
00001-2
00001-3
00002-1
00003-1
00003-2
00004-1

Is that right?

 

[Aelstro]

That is correct except the first entry would be left alone, second entry gets the first -1.

I think I found a solution. Here it is:

Public Function funFillArray(ByVal strTable As String, Optional ByVal intFieldNumber As Integer = 0)


'Created By: Robert Johnson
'Modified By: Kenneth Lines
'Usage: funFillArray(&quot;Table1&quot;,3)
'Output: Changes a table to create unique entries when dupicates are present

Dim Db As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQLstr As String
Dim varData As Variant
Dim Concatenation As String
Dim Temp As String
Dim TempArray(4) As String
Dim CombinedLength As Integer
Dim intCount, CountIndex, IntI As Integer

Set Db = CurrentProject.Connection

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
SQLstr = &quot;SELECT * FROM &quot; + strTable
rst.Open SQLstr, Db, adOpenStatic, adLockOptimistic, -1

IntI = 1
CountIndex = 1

varData = rst.GetRows(10000) 'Sets varData to get up to 10,000 rows but varData will only use what
'it needs or up to 10,000
intCount = UBound(varData, 2) + 1 'Gives the number of rows in varData
Rem moves forwards from first through rows and updates the change
rst.MoveFirst
For IntI = 1 To intCount - 1 Step 1
If Not (IsNull(rst.Fields(4))) Then
TempArray(CountIndex) = rst.Fields(4)
rst.MoveNext
TempArray(CountIndex + 1) = rst.Fields(4)
If (varData(intFieldNumber, IntI - 1) = TempArray(CountIndex + 1)) Then
Rem put OldValue + &quot;-1&quot; back into RST
rst.Fields(4) = CStr(varData(intFieldNumber, IntI - 1)) + &quot;-&quot; + CStr(CountIndex)
rst.Update
CountIndex = CountIndex + 1
Else
CountIndex = 1
End If
End If
Next IntI
End Function

 

[mikevh]

Fine. I kinda think it's a mistake to leave the first entry alone, since then you'll have id's in 2 different formats that you'll have to take into consideration when
working with this field, though.

Here's my 2 cents worth of code:

Sub updateinvoice()
  Dim rs As ADODB.Recordset
  Dim cnn As ADODB.Connection
  
  Dim strField As String
  strField = "invoice"
  Dim strSQL As String
  
  Dim oldinv As String
  Dim counter As Integer
  
  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  
  strSQL = "SELECT * FROM tblInvoice ORDER BY " & strField
  
  rs.Open Source:=strSQL, _
    ActiveConnection:=cnn, _
    CursorType:=adOpenForwardOnly, _
    LockType:=adLockOptimistic, _
    options:=adCmdText

  While Not rs.EOF
    With rs.Fields(strField)
      If .Value = oldinv Then
        counter = counter + 1
        .Value = .Value & "-" & Format(counter)
        rs.Update
      Else
        counter = 0
        oldinv = .Value
      End If
      rs.MoveNext
    End With
  Wend
  
ExitHere:
  rs.Close
  cnn.Close
  If Not (rs Is Nothing) Then Set rs = Nothing
  If Not (cnn Is Nothing) Then Set cnn = Nothing
  
  Exit Sub
  
HandleError:
  MsgBox "Error " & Err.Number & vbCrLf & Err.Description
  Resume ExitHere
  
End Sub

 

[Aelstro]

I tried to update rst.fields(strField).value and all I got were errors about BOF or EOF being true.

I'm sure it was something simple I did wrong.

Anyway, thank you ever so much for your diligence. I never would have solved it without your code and suggestions.

Kenneth

 

[mikevh]

If you're going to use that code I posted you'll need to adjust table and field names to match what you've actually got in your table. Should also add
On Error Goto HandleError
as first line in body of sub -- I've got error-handling code
but no way to get to it. My goof.
Thanks for the star.