Change Access Entry using Excel VBA

[Spurious]

Hi guys,

How do I change a certain entry in an Access table using Excel VBA?

Thanks

 

[Spurious]

Will it identify the record distinctly? Because I imagine you won't want to be updating other records with same field values.

Could I hide the Key, when I read out the data to the listbox? Usually the values are unique, but they might not be, so I want to make sure.

Your 2nd post:
Is lngRecordsAffected your query (ssql)?

 

[Jon von der Heyden]

lngRecordsAffected is to capture the number of records affected by the sql. In your case you can raise an error (or something) if lngRecordsAffected is greater than one.

So to try and compile this for you in a more tidy way, you could have this:

Private Sub CheckError(ByVal lngRecordsAffected As Long, _
                       ByVal lngExpected As Long, ByVal strDescription As String)
    If lngRecordsAffected <> lngExpected Then
        Call RaiseError(strDescription)
    End If
End Sub

Private Sub RaiseError(ByVal strDescription As String)
    Call Err.Raise(vbObjectError + 1024, , strDescription)
End Sub

Private Sub ExecuteCommand(ByVal command As ADODB.command, _
                           ByVal strCommandText As String)
    Dim lngRecordsAffected As Long
    Dim strDescription As String
    
    command.CommandText = strCommandText
    
    On Error Resume Next
        Call command.Execute(RecordsAffected:=lngRecordsAffected, _
                             Options:=CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
        strDescription = IIf(Err.Number <> 0, Err.Description, vbNullString)
    On Error GoTo 0
                         
    Call CheckError(lngRecordsAffected, 1, strDescription)
End Sub

Public Sub UpdateRecord(ByVal command As ADODB.command, ByVal strTableName As String, _
                        ByVal strField As String, ByVal strValue As String, ByVal lngKey As Long)
    Dim strCommandText As String
    
    strCommandText = "UPDATE " & strTableName & _
                     " SET " & strField & "=" & strValue & _
                     " WHERE [ID] =" & CStr(lngKey) & ";"
    
    Call ExecuteCommand(command, strCommandText)
End Sub

As for hiding the key... Perhaps you can assign the key value to the list box Tag property?

 

[RoryA]

I would put the key in the listbox but make that column zero width.

 

[Jon von der Heyden]

I would put the key in the listbox but make that column zero width.

Ah yes, much better. In fact mine is floored because putting in delimited string might yet exceed character length...

 

[Spurious]

Ok, after looking at your code, I am confused.

Is there not an easy way (without 3 subs) to change the values?
The records affected will definitely be only 1, because I changed the primary key that makes it impossible to get two records.

I got the connection to the database open, create the query and now only want to execute the command.
How do I do this?

 

[Jon von der Heyden]

Sorry I thought separating would make it a bit easier to compartmentalise and understand...

Something like:

Public Sub Example()
    Dim objCommand As ADODB.command
    Dim strConnection As String
    Dim strProvider As String
    Dim strSQL As String
    
    If Val(Application.Version) >= 12 Then
        strProvider = "Microsoft.ACE.OLEDB.12.0"
    Else
        strProvider = "Microsoft.Jet.OLEDB.4.0"
    End If

    strConnection = "Provider=" & strProvider & ";" & _
                    "Data Source=" & DBpath & ";" & _
                    "Persist Security Info=False"
    
    strSQL = "UPDATE blah blah"
    
    With objCommand
        .ActiveConnection = strConnection
        .CommandText = strSQL
        Call .Execute(RecordsAffected:=lngRecordsAffected, _
                      Options:=CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
    End With
End Sub

 

[Spurious]

I get an error that one of the parameters has no value.
lngRecordsAffected is undefined as I see it, is this correct?

This is how my code looks like:

'Neue Datenbank Verbindung aufbauen
Set jCN = New ADODB.Connection
jCN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
TableName = "NOS_Material_Uebersicht"
'Neues Command
Set jCom = New ADODB.Command
jQuery = "UPDATE " & TableName & _
        " SET [Brand]=" & Brand & "25, " & _
        "[Produktname]=" & Produktname & "Works" & _
        " WHERE [MaterialFarbe]=" & Material & Farbe & ";"
jCom.ActiveConnection = jCN
jCom.CommandText = jQuery
Call jCom.Execute(RecordsAffected:=lngRecordsAffected, _
                      Options:=CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
'Verbindungen schliessen
Set jCom = Nothing
jCN.Close
Set jCN = Nothing

 

[Spurious]

bump

 

[Jon von der Heyden]

Just define a long type for it. All it does is assign the value of records affected to it. It's an optional argument anyway so you can leave it out too.

 

[Spurious]

I got it to work, thanks for your MAJOR help!!!