Solved

Cause of run time error

Posted on 2006-11-11
8
312 Views
Last Modified: 2008-03-10
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
Comment
Question by:snyperj
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 18

Assisted Solution

by:Data-Man
Data-Man earned 200 total points
ID: 17923763
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17924125
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
 
LVL 4

Accepted Solution

by:
GordonPrince earned 300 total points
ID: 17924838
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
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17924843
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
ID: 17926589
Just remove the rs.close line
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17926613
Help says an alternative to rs.close is set rs = nothing.  
0
 

Author Comment

by:snyperj
ID: 17930284
One of the fields in the table where the records were being appended was not big enough to receive the data.  Thanks.
0
 

Author Comment

by:snyperj
ID: 17930301
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now