Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: you and another user are attempting to change the same data

    Good afternoon DBAs,

    Here is my situation. So far, my group has been using MS Access succesfully but because of the increase in usage and size, we decided to move the main tables to a MySQL server so we would keep the front end MS Access (with the nice forms and features).

    The migration was done using the MySQL wizard and we selected the InnoDB engine. We had to restore some key elements (like relationships and cascading deletes) but overall the new tandem works fine (forms connect to the tables succesfully with add/update/delete). MySQL tables are linked to Access through a DSN file using a root like MySQL account called "admin".

    We have a VB routine in Access (modules) that allows us to import text files which are parsed, loaded to ADODB recordsets and then added to the tables in MySQL through a "update" procedure.

    Now, the VB stops at the update with the error: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

    I think the error comes from the fact that the script can't use the existing DSN file connection. We do use "CurrentProject.Connection" in VB but that is not enough.

    I get the same error when I shut down the MySQL server while Access is up...

    Any ideas?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you explored ADODB locktypes yet? Your issue might be in there somewhere...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    Good catch. I used adLockOptimistic... Just changed it to adLockBatchOptimistic and it works.

    Teddy, you're good. Thanks

    Code:
    Function ServerIDLookup() As Long
        
        Dim rsHardware As New ADODB.Recordset
        Dim rsStage As New ADODB.Recordset
        Dim sName As String
        
        rsHardware.Open "tbl_hardware", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        rsStage.Open "tbl_importstaging", CurrentProject.Connection, adOpenStatic, adLockOptimistic
        
        rsStage.MoveFirst
        rsStage.Filter = "Section = #PSINFONormal# AND SubSection = #PSINFOSystemName#"
        'sName = UCase(Trim(rsStage("SubValue")))
        sName = Trim(rsStage("SubValue"))
    
        rsHardware.Find ("ServerName Like #" & sName & "#")
        
        If rsHardware.EOF Then
            rsHardware.AddNew
            rsHardware("ServerName") = sName
            rsHardware.Update
        End If
        
        Dim stageValue
        
        rsStage.Filter = "Section = #PSINFONormal# AND SubSection = #Processors#"
        If Not rsStage.EOF Then
            stageValue = rsStage("SubValue")
            If (Not IsNull(stageValue)) And (Not stageValue = "") Then
                rsHardware("ProcessorCount") = stageValue
            End If
        End If
        
        rsStage.Filter = "Section = #PSINFONormal# AND SubSection = #ProcessorSpeed#"
        If Not rsStage.EOF Then
            stageValue = rsStage("SubValue")
            If (Not IsNull(stageValue)) And (Not stageValue = "") Then
                rsHardware("ProcessorSpeed") = stageValue
            End If
        End If
        
        rsStage.Filter = "Section = #PSINFONormal# AND SubSection = #ProcessorType#"
        If Not rsStage.EOF Then
            stageValue = rsStage("SubValue")
            If (Not IsNull(stageValue)) And (Not stageValue = "") Then
                rsHardware("ProcessorType") = stageValue
            End If
        End If
        
        rsStage.Filter = "Section = #PSINFONormal# AND SubSection = #PhysicalMemory#"
        If Not rsStage.EOF Then
            stageValue = rsStage("SubValue")
            If (Not IsNull(stageValue)) And (Not stageValue = "") Then
                rsHardware("RAM") = stageValue
            End If
        End If
        
        rsStage.Filter = "Section = #PSINFONormal# AND SubSection = #VideoDriver#"
        If Not rsStage.EOF Then
            stageValue = rsStage("SubValue")
            If (Not IsNull(stageValue)) And (Not stageValue = "") Then
                rsHardware("VideoDriver") = stageValue
            End If
        End If
        
        rsStage.Filter = "Section = #PSINFONormal# AND SubSection = #PSINFOFixedDriveVolumeInfo#"
        If Not rsStage.EOF Then
            stageValue = rsStage.RecordCount
            If (Not IsNull(stageValue)) And (Not stageValue = "") Then
                rsHardware("DriveNumber") = stageValue
            End If
        
            Dim totalDriveMegabytes As Double
            totalDriveMegabytes = 0#
            
            Do While Not rsStage.EOF
                driveInfo = rsStage("DelimitedSubMatches")
                If (Not IsNull(driveInfo)) And (Not driveInfo = "") Then
                    driveSplit = Split(driveInfo, "|")
                    
                    If driveSplit(7) Like "GB" Then
                        totalDriveMegabytes = totalDriveMegabytes + (CDbl(driveSplit(6)) * 1024#)
                    Else
                        If driveSplit(7) Like "TB" Then
                            totalDriveMegabytes = totalDriveMegabytes + (CDbl(driveSplit(6)) * 1024# * 1024#)
                        Else
                            If driveSplit(7) Like "KB" Then
                                totalDriveMegabytes = totalDriveMegabytes + (CDbl(driveSplit(6)) / 1024#)
                            Else
                                If driveSplit(6) Like "" Then
                                    driveSplit(6) = 0
                                End If
                                totalDriveMegabytes = totalDriveMegabytes + CDbl(driveSplit(6))
                            End If
                        End If
                    End If
                End If
                rsStage.MoveNext
            Loop
            'rsHardware("DriveTotalSize") = totalDriveMegabytes / 1024# 'make it GB for now TODO: find out the units to use
             rsHardware("DriveTotalSize") = totalDriveMegabytes
        End If
        
        rsHardware("ServerName") = sName
        rsHardware.Update
        
        ServerIDLookup = rsHardware("IDServer")
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •