asked on

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.

Rey Obrero (Capricorn1)

upload a copy of the db and the excel file you are trying to import.

jlcannon

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jlcannon

ASKER

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.