?
Solved

VBA Run-time error 2046 'DeleteRecord'

Posted on 2011-02-15
7
Medium Priority
?
1,839 Views
Last Modified: 2012-05-11
I just converted a 3-yr old Access 2003 database to 2010 for testing.  I have a piece of VBA code I use often that checks temporary tables to make sure they were properly emptied after use and, if not, deletes all the records so they are ready for the next use.

In the 2003 version, the code looked like this and worked without any problems:

Dim db As Database
Dim table_RST As Recordset
Set db = CurrentDb
Set table_RST = db.OpenRecordset ("tbl_TempTable", dbOpenDynaset)

If (table_RST.RecordCount > 0) Then
  table_RST.MoveFirst
  Do While (Not table_RST.EOF)
    DoCmd.Delete
    table_RST.MoveNext
  Loop
End If

In Access 2010, I now get a error that the "DoCmd.Delete" command is not vaild.  So, I did a little looking around and tried replacing that line with the following two lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

When I try to run this, I get a Run-time error 2046 saying that the "command or action 'DeleteRecord' isn't available now."

All my forms are unbound and I use VB Scripting almost exclusively to move data from tables to forms, so I don't think it is a record lock problem.  But, I don't know how to correct it.  Any suggestions?  Thanks!
0
Comment
Question by:jmcclosk
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 75
ID: 34898854
That code does not even compile in A2003, so I do see how it ever worked.

Delete is not a Method of DoCmd.

I think you need

table_RST.Delete

mx
0
 
LVL 28

Expert Comment

by:omgang
ID: 34898911
I agree with 'MX but why are you enumerating the recordset if you simply want to delete all the records.  For that matter, why even check if the table contains records first, simply execute a delete query (DELETE * FROM tbl_TempTable).
OM Gang
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 34899014
Yes ... if your goal is to delete all records, all you need is one line of code:


    CurrentDb.Execute "DELETE tbl_TempTable.* FROM tbl_TempTable", dbFailOnError


mx
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:jmcclosk
ID: 34899035
What a bonehead I am!  I copied some code to another form and didn't select everything, so I typed the rest from memory.  Obviously, my memory is not so good.  DatabaseMX got me back on track.  

However, omgang raises a good point, which may be a new question to write up.  I taught myself VBA.  I have no doubt "roughed out" some things that can be done better.  I tried entering that command you gave me, although it looks more like SQL than VBA and I got a compile error.  How could i wipe out all records in the table with a single command instead of going one by one?
0
 
LVL 28

Expert Comment

by:omgang
ID: 34899061
See 'MX's second post.  That's the command you need.
OM Gang
0
 

Author Closing Comment

by:jmcclosk
ID: 34899069
Thank you for updating my self-learned skills to be more efficient!
0
 
LVL 28

Expert Comment

by:omgang
ID: 34899072
You're only a bonehead if you don't continue to learn and improve.
OM Gang
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

762 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