Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cause of run time error

Posted on 2006-11-11
8
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 18

Assisted Solution

by:Data-Man
Data-Man earned 800 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 1200 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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
 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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