• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

Error importing excel data into access 2007

I have an access database that none of the code has changed in and has been working fine for over a year now all of a sudden when I click the import data button that reads info from an excel sheet into the database it is generating errors. the first one is as follows:
there is a box that pops up and the title is Action Failed and
Macro Name: Rearrange_Data
Condition: True
Action name: OpenQuery
Arguments: qryAppendPlantInformationTemp, Datasheet, Edit
Error Number: 2950

And it has 3 options Step; Stop All Macros; Continue and the only option that is not greyed out is Stop all macros. so I click that one then another smaller box pops up that says:
"There was an Error #3197 because the Microsoft Office Access database engine stopped the process because you and another user are attempting to change the same data at the same time.

Then sometimes the 2nd error is something about the property was not found. I cannot figure out why all of a sudden it quit working.. and help would be great.
0
jlcannon
Asked:
jlcannon
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db and the excel file you are trying to import.
0
 
jlcannonAuthor Commented:
Sorry, I am not permitted to upload copies per my company policy. The routine for import is as follows.

Public Function GetExcelData(datafile As Variant)

    GetExcelData = True
On Error GoTo Err_GetExcelData
    Dim dbs As Database
    Dim rs As Recordset
    Dim mydb As Database
    Dim plntrs As Recordset
    Dim plantArray As Variant
    Dim Histrs As Recordset
    Dim i As Integer
    Dim mydatafile As String
   
 
    mydatafile = datafile
    
     
    If IsEmpty(mydatafile) Then
        MsgBox "I am sorry, but you must have a data file to run!"
        Exit Function
    End If
    Set dbs = OpenDatabase(mydatafile, False, False, "Excel 5.0;")
    
        
    'Gets named range in excel spreadsheet and converts to recordset.

    Set rs = dbs.OpenRecordset("Incoming_Service")
    
    Set mydb = CurrentDb()
    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_1", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop


    Set rs = dbs.OpenRecordset("Buildings")

    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_2", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    
    Set rs = dbs.OpenRecordset("Transformers")

    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_3", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    
    Set rs = dbs.OpenRecordset("MCC_Medium_Voltage")

    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_4", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("MCC_Low_Voltage")

    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_5", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("Switchgear")

    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_6", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("Bus_Duct")

    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_7", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
     Set rs = dbs.OpenRecordset("Motors")

    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_8", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("Field_Cabling_Area")
    
    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_9", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("PreAudit_Information")
    
    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_10", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("Critical_Power_Systems")
    
    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_11", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("VFD_Systems")
    
    Set Histrs = mydb.OpenRecordset("temp_import_stage_1_12", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             .Update
            End With
            rs.MoveNext
    Loop
   
    Set rs = dbs.OpenRecordset("Plant_Information")
    
    Set Histrs = mydb.OpenRecordset("temp_import_plant_data", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             ![F12] = rs.Fields(11).Value
             .Update
            End With
            rs.MoveNext
    Loop
    Set rs = dbs.OpenRecordset("Plant_Information2")
    
    Set Histrs = mydb.OpenRecordset("temp_import_plant_data_1", dbOpenDynaset)
    rs.MoveFirst
    Do While Not (rs.EOF)
            With Histrs
             .AddNew
             ![F1] = rs.Fields(0).Value
             ![F2] = rs.Fields(1).Value
             ![F3] = rs.Fields(2).Value
             ![F4] = rs.Fields(3).Value
             ![F5] = rs.Fields(4).Value
             ![F6] = rs.Fields(5).Value
             ![F7] = rs.Fields(6).Value
             ![F8] = rs.Fields(7).Value
             ![F9] = rs.Fields(8).Value
             ![F10] = rs.Fields(9).Value
             ![F11] = rs.Fields(10).Value
             ![F12] = rs.Fields(11).Value
             .Update
            End With
            rs.MoveNext
    Loop
   
    rs.Close
    Histrs.Close
    Set rs = Nothing
    Set Histrs = Nothing
    Set dbs = Nothing
    Set mydb = Nothing
     MsgBox "Import Complete"
    
end_GetExcelData:
    Exit Function
    
Err_GetExcelData:
    Select Case Err.Number
        Case 3421
            MsgBox "One of the input values is missing or of incorrect type!"
            GetExcelData = False
            Resume end_GetExcelData
        Case Else
            MsgBox "Problem with GetExcelData " & " - " & Err.Number & " - " & Err.Description
            GetExcelData = False
            Resume end_GetExcelData
    End Select
   
End Function

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
1. do a compact and repair of the db.
2. is the db application located in a trusted location ?
3. is the excel file located in a trusted location?
0
 
jlcannonAuthor Commented:
I would imagine it has to do with the trusted locations but i am not able to add a network location as trusted due to security policy.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now