Update Access DB connection through Excel

[JTBorton]

Excel 2010, Access 2010
I'm very new to ADO and I'm trying to update a record in a connected database. The code executes fine, but it is not changing anything. Can anyone show me where I am making a mistake? The offending code is the in Change event of the txtFlakeName textbox.

udtFlakeTitles is a user-defined type (or structure) that has all of the column titles for each field in a particular table in the database. I got tired of trying to remember all of the column titles for each table and wanted to group them together in an easy-to-manage location.

Here is the code in the user-form which is trying to implement the change to the database

Private Sub txtFlakeName_Change() 
    Dim strFlake As String
    Dim rstFlake As Recordset
    
    'Update the selected polymer flake name
    If blnLoading Then: Exit Sub
    strFlake = Trim(txtFlakeName.Text)
    If strFlake = vbNullString Then: Exit Sub
    Set rstFlake = [COLOR=blue]SelectedFlake()[/color]
    'rstFlake.Fields(udtFlakeTitles.FlakeName) = strFlake
    [highlight]rstFlake.Update(udtFlakeTitles.FlakeName) = strFlake[/highlight] [COLOR=darkgreen]'(Works, but nothing happens)[/color]
    lstFlake.List(lstFlake.ListIndex, 1) = strFlake
    Set rstFlake = Nothing
End Sub

Public Function [COLOR=blue]SelectedFlake()[/color] As ADODB.Recordset
    Dim rstFlake    As New Recordset
    Dim lngID       As Long
    
    lngID = lstFlake.Value
    rstFlake.Open Source:="SELECT tblFlakeTypes.*, tblFlakeTypes.ID FROM tblFlakeTypes" _
        & " WHERE (((tblFlakeTypes.ID)=" & lngID & " ));", ActiveConnection:=[COLOR=orange][b]PropertyDB[/b][/color], _
        CursorType:=adOpenDynamic, LockType:=adLockBatchOptimistic        
    rstFlake.MoveFirst
    Set SelectedFlake = rstFlake
    
    Set rstFlake = Nothing
End Function

Background code in public modules

Public Property Get [COLOR=orange][b]PropertyDB()[/b][/color] As ADODB.Connection
    If ThisWorkbook.cnnDatabase Is Nothing Then
        Call ThisWorkbook.[COLOR=red]ConnectDB[/color]
    End If
    
    Set PropertyDB = ThisWorkbook.cnnDatabase
End Property

Public Property Get [COLOR=purple]DatabaseFileLocation()[/color] As String
    DatabaseFileLocation = Trim(wksDatabaseInfo.Cells(1, 2))
End Property

Background code in the ThisWorkbook class module

Public WithEvents cnnDatabase As ADODB.Connection

Public Sub [COLOR=red]ConnectDB()[/color]
    Set cnnDatabase = New ADODB.Connection
    
    cnnDatabase.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False; Data Source=" & [COLOR=purple]DatabaseFileLocation()[/color] & ";"
End Sub

Public Sub DisconnectDB()
    If cnnDatabase Is Nothing Then
        Exit Sub
    End If
    
    cnnDatabase.Close
    Set cnnDatabase = Nothing
End Sub

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[PHV]

I'd replace this:
rstFlake.Update(udtFlakeTitles.FlakeName) = strFlake
with this:
rstFlake.Update udtFlakeTitles.FlakeName, strFlake

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

Okay I tried it as you said, but I'm still same result: the code executes, but the database remains unchanged. Could it be a permission issue?

Private Sub txtFlakeName_Change()
    Dim strFlake As String
    Dim rstFlake As Recordset
    
    If blnLoading Then: Exit Sub
    strFlake = Trim(txtFlakeName.Text)
    If strFlake = vbNullString Then: Exit Sub
    Set rstFlake = SelectedFlake()
    rstFlake.Fields(udtFlakeTitles.FlakeName) = strFlake
    [highlight]rstFlake.Update udtFlakeTitles.FlakeName, strFlake[/highlight]
    lstFlake.List(lstFlake.ListIndex, 1) = strFlake
    Set rstFlake = Nothing
End Sub

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

Got it. I was using batch locking for the records, so even though it updates the recordset on the client side, it does not send the updates until a BatchUpdate is called.

Private Sub txtFlakeName_AfterUpdate()
    Dim strFlake As String
    Dim rstFlake As Recordset
    
    If blnLoading Then: Exit Sub
    strFlake = Trim(txtFlakeName.Text)
    If strFlake = vbNullString Then: Exit Sub
    Set rstFlake = SelectedFlake()
    rstFlake.Fields(udtFlakeTitles.FlakeName) = strFlake
    rstFlake.Update udtFlakeTitles.FlakeName, strFlake
    [highlight]rstFlake.UpdateBatch AffectRecords:=adAffectCurrent[/highlight]
    lstFlake.List(lstFlake.ListIndex, 1) = strFlake
    Set rstFlake = Nothing
End Sub

The can also be solved by changing the locking from dLockBatchOptimistic to adLockOptimistic, in which case the BatchUpdate is not necessary.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]