Solved

Err 3167- Record is deleted.

Posted on 2011-09-09
24
333 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
  • 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
 
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 - Access MVP) 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now