Solved

Access/VB Error:  the operation is not supported for this type of object

Posted on 2013-05-30
6
720 Views
Last Modified: 2013-06-06
Below is my code.  I keep getting this error: the operation is not supported for this type of object

How can I resolve this error?  


Public Function tone_Delete() As Boolean

'Deletes records in tbl_tone with a date less than the current date minus the number of days set as the pararmter in the tbl_mjobs table.

On Error GoTo ErrorHandler


Dim db As Database
Dim Pkg As Recordset
Dim Parms As Recordset
Dim d As Date
Dim pn As Long

pn = 0
d = 0

Set db = CurrentDb
Set Pkg = db.OpenRecordset("tbl_tone")
Set Parms = db.OpenRecordset("tbl_mjobs")

With Parms
    .Index = "PrimaryKey"
    .Seek "=", "tone_Limit"
    If .NoMatch = False Then
        pn = !Parm_Value
    End If
    'MsgBox (pn)
   
End With

d = Date - pn
 
 

With Pkg
    .Index = "LOAN_DATE"
    .MoveFirst
    Do Until !LOAN_DATE > d
        .Delete
        .MoveNext
    Loop
 
End With
 '

tone_Delete = True
Exit Function

ErrorHandler:

MsgBox Err.Number & vbCr & Err.Description, vbCritical, "Error in tone_Delete"
tone_Delete = False

End Function
0
Comment
Question by:programmher
6 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39209820
What line of code is highlighted when you get the error?
0
 

Author Comment

by:programmher
ID: 39209896
.Index = "loan_date" is the line that is hilighted
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 150 total points
ID: 39209994
If tbl_mjobs is a linked table, Index and Seek cannot be used.
Use FindFirst if so.

/gustav
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 77

Expert Comment

by:peter57r
ID: 39210063
instead of
Set Pkg = db.OpenRecordset("tbl_tone")
use
Set Pkg = db.OpenRecordset(" Select * from tbl_tone Order by [Loan_date]")

You can then delete the .index line.

And to avoid any possible ambiguity you should declare your recordsets  as DAO.Recordset
0
 

Author Comment

by:programmher
ID: 39210314
Thanks.  I will try these suggestions.  Any idea how this ever worked?  My users tell me these errors didn't use to happen.
0
 
LVL 57
ID: 39210478
<<Thanks.  I will try these suggestions.  Any idea how this ever worked?  My users tell me these errors didn't use to happen. >>

  A seek can only be used on a locally opened table.  If you recently split the DB into a FE/BE, then you need to make an adjustment to use a seek (by opening the remote DB - see code below) or you need to use FindFirst instead gustav said.

 But I think your problem is what peter mentioned; DAO vs ADO.  Did you recently switch Access versions?  If so, you probably have a reference set for ADO, which has a recordset object, but no index method.   You need to check the DAO reference and un-check the ADO reference if your not using ADO in code.  If you are, then your dims need to have a ADO or DAO prefix as Peter said.

Jim.


    '
    ' Got a key.  Does it need to be tested for unique?
    '
    If (rst1![UniqueKey]) Then
        If dbRemote Is Nothing Then
            Set wrk = DBEngine.Workspaces(0)
            Set dbCurrent = wrk.Databases(0)
            Set tdfAttached = dbCurrent.TableDefs(strTableName)
            strPath = tdfAttached.Connect
            strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
            Set dbRemote = wrk.OpenDatabase(strPath, False, True)
        End If
       
        Set rst2 = dbRemote.OpenRecordset(strTableName, DB_OPEN_TABLE)
        rst2.index = "PrimaryKey"
        rst2.Seek "=", intlKeyValue
        If Not (rst2.NoMatch) Then
            rst2.Close
            GoTo GetAKey
        Else
            rst2.Close
        End If
    End If
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

770 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