?
Solved

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

Posted on 2013-05-30
6
Medium Priority
?
792 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 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
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.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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