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

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
programmherAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
If tbl_mjobs is a linked table, Index and Seek cannot be used.
Use FindFirst if so.

/gustav
0
 
pdebaetsCommented:
What line of code is highlighted when you get the error?
0
 
programmherAuthor Commented:
.Index = "loan_date" is the line that is hilighted
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
peter57rCommented:
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
 
programmherAuthor Commented:
Thanks.  I will try these suggestions.  Any idea how this ever worked?  My users tell me these errors didn't use to happen.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.