Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

Err 3167- Record is deleted.

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
APD Toronto
Asked:
APD Toronto
  • 10
  • 7
  • 5
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
APD TorontoAuthor Commented:
mx,

1) Didnt help

2) The form has no recordsource.  I populate everything manually
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
If you take that SQL and run it directly as a query in the query designer, what happens ?

0
Technology Partners: 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!

 
mbizupCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
APD TorontoAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
mbizupCommented:
APD_Toronto,

Can you post a sample DB?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
APD TorontoAuthor Commented:
but guys, its not the VBA code as running the query alone produces the same error.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I know ... my last comment was a side note to Jeff ... sorry.

Try what I suggested @ http:#a36514583

mx
0
 
APD TorontoAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
or, ...as a wise man is often quoted as saying...

"A decompile may be needed here..."

;-)

0
 
APD TorontoAuthor Commented:
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
 
APD TorontoAuthor Commented:
should i backup the live DB as percaution?
0
 
APD TorontoAuthor Commented:
I got it!  phew.

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

Why did this happen?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:

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


Just curious
Why the "manual", copy/paste instead of using an INSERT query...
0
 
Jeffrey CoachmanMIS LiasonCommented:
Joe,

Did you lose power yesterday...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"and" >> can
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now