[RESOLVED] [Access VBA] Error while saving

[tommygrayson]

Hi guys!!!

I need help on Access.

I cannot find where the error is.

The error occurs during recordset.UpdateBatch and it says:

Invalid object name 'UPDATED'

And what the hell does Recordset.UpdateBatch do?

Thanks for any help you can give.

[Ecniv]

Check the help files but probably updates a batch of records.... guessing

Does it tell you what line it errored on?

Invalid object name 'UPDATED'

Seems like you are trying to use an object that you haven't defined.

Ensure you have tools>options>declare all variables ticked. (Puts option explicit at the top of the module and requires you to declare all variables you are using.)

[tommygrayson]

Check the help files but probably updates a batch of records.... guessing

Does it tell you what line it errored on?

Seems like you are trying to use an object that you haven't defined.

Ensure you have tools>options>declare all variables ticked. (Puts option explicit at the top of the module and requires you to declare all variables you are using.)

Nope. Still not working. Error mentioned above still works.

[tommygrayson]

Can UpdateBatch add a new record?

[Siddharth Rout]

Hi

Like Ecniv Suggested, If you browse thru the help file you will get see what update batch does
In you Access VBA Help, Simply type "UpdateBatch"

They also have a beautiful example on how to use the updatebatch command....

er well, i'll paste the entire data from the help file here

UpdateBatch Method
Writes all pending batch updates to disk.

Syntax
recordset.UpdateBatch AffectRecords

Parameters
AffectRecords (Optional). An AffectEnum value that indicates how many records the UpdateBatch method will affect.

Remarks
Use the UpdateBatch method when modifying a Recordset object in batch update mode to transmit all changes made in a Recordset object to the underlying database.

If the Recordset object supports batch updating, you can cache multiple changes to one or more records locally until you call the UpdateBatch method. If you are editing the current record or adding a new record when you call the UpdateBatch method, ADO will automatically call the Update method to save any pending changes to the current record before transmitting the batched changes to the provider. You should use batch updating with either a keyset or static cursor only.

Note: Specifying adAffectGroup as the value for this parameter will result in an error when there are no visible records in the current Recordset (such as a filter for which no records match).
If the attempt to transmit changes fails for any or all records because of a conflict with the underlying data (for example, a record has already been deleted by another user), the provider returns warnings to the Errors collection and a run-time error occurs. Use the Filter property (adFilterAffectedRecords) and the Status property to locate records with conflicts.

To cancel all pending batch updates, use the CancelBatch method.

If the Unique Table and Update Resync dynamic properties are set, and the Recordset is the result of executing a JOIN operation on multiple tables, then the execution of the UpdateBatch method is implicitly followed by the Resync method depending on the settings of the Update Resync property.

The order in which the individual updates of a batch are performed on the data source is not necessarily the same as the order in which they were performed on the local Recordset. Update order is dependent upon the provider. Take this into account when coding updates that are related to one another, such as foreign key constraints on an insert or update.

Example


UpdateBatch and CancelBatch Methods Example (VB)

This example demonstrates the UpdateBatch method in conjunction with the CancelBatch method.

'BeginUpdateBatchVB
Public Sub Main()
    On Error GoTo ErrorHandler
 
    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
 
    'connection and recordset variables
    Dim rstTitles As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String
    Dim strSQLTitles As String
     'record variables
    Dim strTitle As String
    Dim strMessage As String
    
    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
    
     ' open recordset for batch uodate
    Set rstTitles = New ADODB.Recordset
    strSQLTitles = "titles"
    rstTitles.Open strSQLTitles, Cnxn, adOpenKeyset, adLockBatchOptimistic, adCmdTable
    
    rstTitles.MoveFirst
    ' Loop through recordset and ask user if she wants
    ' to change the type for a specified title.
    Do Until rstTitles.EOF
        If Trim(rstTitles!Type) = "psychology" Then
            strTitle = rstTitles!Title
            strMessage = "Title: " & strTitle & vbCr & _
               "Change type to self help?"
            
            If MsgBox(strMessage, vbYesNo) = vbYes Then
                rstTitles!Type = "self_help"
            End If
        End If
    
        rstTitles.MoveNext
    Loop
    
    ' Ask the user if she wants to commit to all the
    ' changes made above.
    If MsgBox("Save all changes?", vbYesNo) = vbYes Then
        rstTitles.UpdateBatch
    Else
        rstTitles.CancelBatch
    End If
    
    ' Print current data in recordset.
    rstTitles.Requery
    rstTitles.MoveFirst
    Do While Not rstTitles.EOF
        Debug.Print rstTitles!Title & " - " & rstTitles!Type
        rstTitles.MoveNext
    Loop
    
    ' Restore original values because this is a demonstration.
    rstTitles.MoveFirst
    Do Until rstTitles.EOF
        If Trim(rstTitles!Type) = "self_help" Then
            rstTitles!Type = "psychology"
        End If
        rstTitles.MoveNext
    Loop
    rstTitles.UpdateBatch
 
    ' clean up
    rstTitles.Close
    Cnxn.Close
    Set rstTitles = Nothing
    Set Cnxn = Nothing
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rstTitles Is Nothing Then
        If rstTitles.State = adStateOpen Then rstTitles.Close
    End If
    Set rstTitles = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndUpdateBatchVB

Hope this helps...