Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cause of run time error

Posted on 2006-11-11
8
Medium Priority
?
346 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

971 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