?
Solved

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

Posted on 2013-05-30
6
Medium Priority
?
766 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
[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
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 52

Accepted Solution

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

/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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 …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

649 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