Solved

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

Posted on 2013-05-30
6
704 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now