Opdater felter i Access

Her er et eksempel du kan plukke fra:


Public Sub InsertUpdateDelete()

    Dim objCommand As ADODB.Command
    Dim rsData As ADODB.Recordset
    Dim lRecordsAffected As Long
    Dim lKey As Long
    Dim szConnect As String
   
    On Error GoTo ErrorHandler
   
    ' Create the connection string.
    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Files\Northwind.mdb;" & _
                "Mode=Share Exclusive"
   
    ' Create the Command object we'll use for all three queries.
    Set objCommand = New ADODB.Command
    objCommand.ActiveConnection = szConnect
   
    '**** INSERT a new record into the database **************
    ' Load the SQL string into the Command object.
    objCommand.CommandText = "INSERT INTO Shippers(CompanyName, Phone) " & _
                            "VALUES('Air Carriers', '(205) 555-1212');"
    ' Execute the SQL statement.
    objCommand.Execute Recordsaffected:=lRecordsAffected, _
                      Options:=adCmdText Or adExecuteNoRecords
    ' Check for errors. Only one record should have been affected.
    If lRecordsAffected <> 1 Then Err.Raise Number:=vbObjectError + 1024, _
          Description:="Error executing INSERT statement."
       
    ' Retrieve the primary key generated for our new record.
    objCommand.CommandText = "SELECT @@IDENTITY;"
    Set rsData = objCommand.Execute(Options:=adCmdText)
    ' Check for errors. The recordset should contain data.
    If rsData.EOF Then Err.Raise Number:=vbObjectError + 1024, _
          Description:="Error retrieving primary key value."
    ' Store the primary key value for later use.
    lKey = rsData.Fields(0).Value
    rsData.Close
   
    '**** UPDATE the record we just created ******************
    ' Load the SQL string into the Command object.
    objCommand.CommandText = "UPDATE Shippers " & _
                            "SET Phone='(206) 546-0086' " & _
                            "WHERE ShipperID=" & CStr(lKey) & ";"
    ' Execute the SQL statement.
    objCommand.Execute Recordsaffected:=lRecordsAffected, _
                      Options:=adCmdText Or adExecuteNoRecords
    ' Check for errors. Only one record should have been affected.
    If lRecordsAffected <> 1 Then Err.Raise Number:=vbObjectError + 1024, _
          Description:="Error executing UPDATE statement."
   
    '**** DELETE our record from the database ****************
    ' Load the SQL string into the Command object.
    objCommand.CommandText = "DELETE FROM Shippers " & _
                            "WHERE ShipperID = " & CStr(lKey) & ";"
    ' Execute the SQL statement.
    objCommand.Execute Recordsaffected:=lRecordsAffected, _
                      Options:=adCmdText Or adExecuteNoRecords
    ' Check for errors. Only one record should have been affected.
    If lRecordsAffected <> 1 Then Err.Raise Number:=vbObjectError + 1024, _
        Description:="Error executing DELETE statement."
   
ErrorExit:

    ' Destroy our ADO objects.
    Set objCommand = Nothing
    Set rsData = Nothing
    Exit Sub
   
ErrorHandler:
    MsgBox Err.Description, vbCritical
    Resume ErrorExit
End Sub

Ser ud til at virke.
Tal for hjælpen.

velbekomme :-)