Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Err 3167- Record is deleted.

Posted on 2011-09-09
24
Medium Priority
?
347 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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