?
Solved

Err 3167- Record is deleted.

Posted on 2011-09-09
24
Medium Priority
?
345 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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
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 …
Suggested Courses

801 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