Solved

Err 3167- Record is deleted.

Posted on 2011-09-09
24
343 Views
Last Modified: 2012-08-14
Hello Experts,

I don';t know why, but wth
strSQL = "SELECT fldReservID, fldUserInit, fldPickupDate, fldLName, fldFamQty, fldAdultQty, fldSenQty, fldStudQty, fldChildQty, fldInfQty, fldCommStat, fldTripType FROM tblReservations WHERE (fldTripType <> 'E')  AND ( (fldReservComment LIKE  '*Sommer*') OR (fldTransComment LIKE  '*Sommer*')  OR (fldCommComment LIKE  '*Sommer*') OR (fldLName LIKE  '*Sommer*') ) "

I keep getting
3167- Record is deleted.

At
With CurrentDb.OpenRecordset(strSQL)

Any help will be appreciated.
0
Comment
Question by:APD_Toronto
[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
  • 10
  • 7
  • 5
  • +1
24 Comments
 
LVL 75
ID: 36514387
1) Try a Compact & Repair on this db.

2) Is this code in a Form and is tblReservations in the Recordsource for this form?

mx
0
 

Author Comment

by:APD_Toronto
ID: 36514409
mx,

1) Didnt help

2) The form has no recordsource.  I populate everything manually
0
 
LVL 75
ID: 36514428
If you take that SQL and run it directly as a query in the query designer, what happens ?

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 61

Expert Comment

by:mbizup
ID: 36514505
Can you post the rest of the code or a sample database to show the context?

This error is probably being caused by something somewhere else in your code.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36514549
<With CurrentDb.OpenRecordset(strSQL)>
?
I'm confused as to why"Set" is not being used here...?
Like so:
With CurrentDB
   set rst=.Openrecordset(strSQL)


This is why you should always post *All* of your code
...perhaps something is awry in your decalrations  (are you declaring the recordsetexplicitly as DAO?)

Because Code like this works fine for me...

Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "Select * FROM tblStudent"
With CurrentDb
    Set rst = .OpenRecordset(strSQL)
   
    rst.MoveLast
    rst.MoveFirst
    MsgBox rst.RecordCount
End With

rst.Close
Set rst = Nothing

JeffCoachman
0
 

Author Comment

by:APD_Toronto
ID: 36514565
huh-

mx, i tried running the SQL alone as a query, i get the same Record is deleted. without number, or debug, etc. which triggers the VBA editor.

This means its the query, but what?
0
 
LVL 75
ID: 36514573
"I'm confused as to why"Set" is not being used here...?"

No need ... shorter syntax ... I use it all the time :-)

Also, I think you want to get the RecordCount before you do a MoveFirst .... in your example.

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36514583
"This means its the query, but what?"

Well ... I've seen 'Record is deleted'  before because the DB was corrupted !

If you are certain there is nothing wrong with the query (no typos, etc) ... then I would create a new blank db, and Import all objects (except any linked tables) into the new db container.

And even before that ... open the VBA editor ... Menu >> Debug >> Compile.

Any errors ?  If so, resolve ... but probably a long shot.

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36514598
APD_Toronto,

Can you post a sample DB?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36514602
<I think you want to get the RecordCount before you do a MoveFirst>

I just threw that in there as something for the code to do...
0
 
LVL 75
ID: 36514675
ooook ....

Short syntax example ... with full on Intellisense:

    With CurrentDb.OpenRecordset("tblBoag2000", dbOpenDynaset)
        .MoveLast
        MsgBox "Recs = " & .RecordCount
        .MoveFirst
        .AddNew
        ![Jeff] = "EE Expert Genius Dude"
        .Update
        .Close
    End With

mx
0
 

Author Comment

by:APD_Toronto
ID: 36514701
but guys, its not the VBA code as running the query alone produces the same error.
0
 
LVL 75
ID: 36514710
I know ... my last comment was a side note to Jeff ... sorry.

Try what I suggested @ http:#a36514583

mx
0
 

Author Comment

by:APD_Toronto
ID: 36514712
FYI - I just pasted the query into my backend, which has no forms, no code, no queries (except this one, same error.  So, nothing to do with VBA
0
 
LVL 75
ID: 36514743
Yes ... right ... nothing to do with VBA ... sio try what I suggested @ http:#a36514583 - on your BACKEND then ... assuming it's an Access db ...

In fact, did you try a Compact & Repair on the BE ?

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36514751
or, ...as a wise man is often quoted as saying...

"A decompile may be needed here..."

;-)

0
 

Author Comment

by:APD_Toronto
ID: 36514770
MX - VERY STRANGE...  As per your suggestion, i opened a blank Access file and copied all tables, but i got the error again, but I thought from the query so i copied just SQL text, i got tblReservations missing.  I just copied  tblReeservations, and when I went to paste I got Record is deleted just by copying,

This confirms that the backend DB is corrupted,  tblReservations specifically, but how can this be when other components work fine and this is THE main table.  More so, how can i fix this?
0
 

Author Comment

by:APD_Toronto
ID: 36514776
should i backup the live DB as percaution?
0
 

Author Comment

by:APD_Toronto
ID: 36514803
I got it!  phew.

i copied/pasted structure only, then i selected all records and pasted manually.

Why did this happen?
0
 
LVL 75
ID: 36514883
Why?  If only we all knew.

Possibly network issues, accidental unexpected shutdown of FE ... and so on.

I strong, highly recommend doing a Compact & Repair (backup first) on the BE *everyday* the db is used.  I have a one-click automated utility that does this every night on 25 BE's.  It's the best possible preventive, preemptive maintenance on an Access db.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36514911

A Timing issue perhaps?
Not sure without all the details...


Just curious
Why the "manual", copy/paste instead of using an INSERT query...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36514913
Joe,

Did you lose power yesterday...
0
 
LVL 75
ID: 36514931
No ... and I don't work for THAT power company, fortunately ... or PG&E.

It's BAD NEWS if one dude and take down 5 mil peeps with one simple action ... if it turns out that is really what happened!  The Grid is Fragile !!

mx
0
 
LVL 75
ID: 36514951
"and" >> can
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

690 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