Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Cause of run time error

Trying to use the below code (which was provided to me) to import excel spreadsheet data into an access table.
I get a Runtime error 3219, "Operation not allowed in the context" and the rst.close line is where the error occurs.

What is wrong please?  I am using Access 2000 with SP3.


Private Sub ImportFile(ByVal pfilename As String)
On Error GoTo cleanup
    MsgBox "Demo Version by Coder Techzone will import only 50 data rows", vbOKOnly + vbInformation

    Dim xlapp, xlwbk, xlsht
    Dim r As Long, br As Long, cnt As Long
    cnt = 0
   
    Set xlapp = CreateObject("Excel.application")
  ' xlapp.Visible = True
   
    Set xlwbk = xlapp.workbooks.Open(pfilename, False, True)
   
    r = 13
   
    Dim rst As New ADODB.Recordset
   
    rst.Open "select top 1 * from tbldetail", Access.CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
   
    Set xlsht = xlwbk.sheets(1)
   
    br = 0
   
With xlsht
    While br < 5 And cnt < 50
        If IsEmpty(.cells(r, 2)) Then
            br = br + 1
            GoTo NextRow
        Else
            br = 0
        End If
       
       
        rst.AddNew
        rst.Fields("MOVE_NUM") = .Range("B1")
        rst.Fields("EMP_NAME") = .cells(r, 2)
        rst.Fields("ID_NUM") = .cells(r, 3)
        rst.Fields("1_MLC_ID") = .cells(r, 4)
        rst.Fields("1_DATA_JK") = .cells(r, 5)
        rst.Fields("1_WKSP_ID") = .cells(r, 6)
        rst.Fields("2_MLC_ID") = .cells(r, 7)
        rst.Fields("2_DATA_JK") = .cells(r, 8)
        rst.Fields("2_WKSP_ID") = .cells(r, 9)
        rst.Fields("ADJUST_INFO") = .cells(r, 10)
        rst.Fields("PC_NUM") = .cells(r, 11)
        rst.Fields("PRTR_TYP_1") = .cells(r, 12)
        rst.Fields("MOVE_PHONE") = .cells(r, 13)
        rst.Fields("PH_NBR") = .cells(r, 14)
        rst.Fields("FAX_NBR") = .cells(r, 15)
        rst.Fields("MODEM") = .cells(r, 16)
        rst.Fields("NEWSPAPERS") = .cells(r, 17)
        rst.Fields("PERS_DATA") = .cells(r, 18)
        rst.Fields("HOST_PTR") = .cells(r, 19)
        rst.Fields("PC_NUM_2") = .cells(r, 20)
'        rst.Fields("Network Color") = .cells(R, 21)
        rst.Update
        cnt = cnt + 1
NextRow:
         r = r + 1
       
    Wend
End With

    MsgBox "Records Imported " & cnt, vbOKOnly + vbInformation, "Data Imported"

cleanup:
    On Error Resume Next
    rst.Close<<<<<<<<<<<<  Yellow highlight is here
    Set rst = Nothing
    xlwbk.Close (False)
    Set xlwbk = Nothing
    xlapp.Quit
    Set xlapp = Nothing
End Sub
0
snyperj
Asked:
snyperj
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
Data-ManCommented:
Are you sure the error occurs on that line.

The 'On Error Resume Next' would bypass any lines of code that caused an error.

Can you single step through the code and see what happens?

Mike
0
 
Arthur_WoodCommented:
also, there is no reason to use rst.close, at that point in your code.  1) your next line sets rst to nothing (which would automatically close it), and 2) since you are about to end the routine, the rst object will then go out of scope, and be removed from memeory in any case.

AW
0
 
GordonPrinceCommented:
I'm with Data-Man on finding the error. Try this

at top of sub, disable the On Error Resume Next
on error goto ErrorProc

then put the code in here

then at the bottom

    Exit Sub
ErrorProc:
    If MsgBox("[" & Err.Number & "] " & Err.Description & vbNewLine & vbNewLine & "Debug?", vbQuestion + vbYesNo) = vbYes Then
        Stop
        Resume
    End If

See if this doesn't point you to the problem.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GordonPrinceCommented:
Now I'm thinking that it's the "On Error Resume Next" that's exposing the problem. That won't fail, but an error has occured somewhere else. When the error occurs, the error handler you've defined jumps to "cleanup:". The first statement is "On Error Resume Next", which doesn't fail. But the first statement that tries to refer to rst stops with the error. My hunch is that rst wasn't set in the first place, but the error just triggers jumping to "cleanup:", so you don't know what caused the error.

The msgbox with the Stop and Resume after it should take you back to the line causing the original error.
0
 
GRayLCommented:
Just remove the rs.close line
0
 
GRayLCommented:
Help says an alternative to rs.close is set rs = nothing.  
0
 
snyperjAuthor Commented:
One of the fields in the table where the records were being appended was not big enough to receive the data.  Thanks.
0
 
snyperjAuthor Commented:
One of the fields in the append table was not big enough to handle the imported data.  The field did not hold data on all imports so the error was only happening when it did contain data.  Thanks for the code that led me to the true error.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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