can someone tell me what is wrong with this

#1

It was originally done in access 2003 and works fine.
now it does not work in access 2007 and after an update it will not
work in access 2003 either.

I am confused

Private Sub TableDeleteRecords(strTableName As String)
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset(strTableName)

If rst.RecordCount = 0 Then Exit Sub
rst.MoveFirst
Do While Not (rst.EOF)
rst.Delete
rst.MoveNext
Loop
rst.Close
End Sub

when you pass any file to this rst.recordcount is always equal to 153.
I even passed it a table with nothing in it and it still says 153.

#2

On Aug 19, 9:52*am, sparks <spa... (AT) home (DOT) com> wrote:

Quote:

It was originally done in access 2003 and works fine.
now it does not work in access 2007 and after an update it will not
work in access 2003 either.

I am confused

Private Sub TableDeleteRecords(strTableName As String)
Dim rst As Recordset

* Set rst = CurrentDb.OpenRecordset(strTableName)

* If rst.RecordCount = 0 Then Exit Sub
* rst.MoveFirst
* Do While Not (rst.EOF)
* * rst.Delete
* * rst.MoveNext
* Loop
* rst.Close
End Sub

when you pass any file to this rst.recordcount is always equal to 153.
I even passed it a table with nothing in it and it still says 153.

I thought one always needed to

rst.movelast
rst.movefirst

first to get the correct recordcount..

#3

christianlott1 (AT) yahoo (DOT) com wrote:

Quote:

On Aug 19, 9:52 am, sparks <spa... (AT) home (DOT) com> wrote:
It was originally done in access 2003 and works fine.
now it does not work in access 2007 and after an update it will not
work in access 2003 either.

I am confused

Private Sub TableDeleteRecords(strTableName As String)
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset(strTableName)

If rst.RecordCount = 0 Then Exit Sub
rst.MoveFirst
Do While Not (rst.EOF)
rst.Delete
rst.MoveNext
Loop
rst.Close
End Sub

when you pass any file to this rst.recordcount is always equal to
153. I even passed it a table with nothing in it and it still says
153.

I thought one always needed to

rst.movelast
rst.movefirst

first to get the correct recordcount..

Only if using a dynamic or keyset cursor. If using a static cursor, then the
number of records is known when the recordset is populated.
The thing is, when using a non-static cursor, RecordCount should contain -1,
since the total number of records is not known until the last record is
navigated to. So I'm very puzzled that it is always containing 153. To the
OP: have you verified that strTableName contains what you think it contains?
Add a
Debug.Print strTableName
line to verify it. Or add a breakpoint to step through the sub and inspect
the variable's value.

That said, I am really puzzled why a recordset is being used here to do the
job of a DELETE query. The sub should be rewritten to this:

Private Sub TableDeleteRecords(strTableName As String)
Dim sql as String
sql = "DELETE * FROM " & strTableName
debug.print sql 'can be commented out when not debugging
currentdb.execute sql, dbFailOnError
End Sub

#4

<christianlott1 (AT) yahoo (DOT) com> wrote

Quote:

On Aug 19, 9:52 am, sparks <spa... (AT) home (DOT) com> wrote:
It was originally done in access 2003 and works fine.
now it does not work in access 2007 and after an update it will not
work in access 2003 either.

I am confused

Private Sub TableDeleteRecords(strTableName As String)
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset(strTableName)

If rst.RecordCount = 0 Then Exit Sub
rst.MoveFirst
Do While Not (rst.EOF)
rst.Delete
rst.MoveNext
Loop
rst.Close
End Sub

when you pass any file to this rst.recordcount is always equal to 153.
I even passed it a table with nothing in it and it still says 153.

I thought one always needed to

rst.movelast
rst.movefirst

first to get the correct recordcount..

Microsoft help recommends you NOT use rst.movelast unless you need to know

at that moment, the number of records in the recordset. rst.RecordCount will
be non 0 when a recordset is opened with 1 or more records.

#5

It appears that you're missing an "End If" statement....
Fred

On Aug 19, 7:52*am, sparks <spa... (AT) home (DOT) com> wrote:

Quote:

It was originally done in access 2003 and works fine.
now it does not work in access 2007 and after an update it will not
work in access 2003 either.

I am confused

Private Sub TableDeleteRecords(strTableName As String)
Dim rst As Recordset

* Set rst = CurrentDb.OpenRecordset(strTableName)

* If rst.RecordCount = 0 Then Exit Sub
* rst.MoveFirst
* Do While Not (rst.EOF)
* * rst.Delete
* * rst.MoveNext
* Loop
* rst.Close
End Sub

when you pass any file to this rst.recordcount is always equal to 153.
I even passed it a table with nothing in it and it still says 153.

#6

sparks <sparks (AT) home (DOT) com> wrote in
news:j1us47tesvk4idlienlvohgcqgv078t7tc (AT) 4ax (DOT) com:

Quote:

It was originally done in access 2003 and works fine.
now it does not work in access 2007 and after an update it will
not work in access 2003 either.

I am confused

Private Sub TableDeleteRecords(strTableName As String)
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset(strTableName)

If rst.RecordCount = 0 Then Exit Sub
rst.MoveFirst
Do While Not (rst.EOF)
rst.Delete
rst.MoveNext
Loop
rst.Close
End Sub

when you pass any file to this rst.recordcount is always equal to
153. I even passed it a table with nothing in it and it still says
153.

There are so many things wrong with this code, it's difficult to
know where to begin.

First off, you really shouldn't use CurrentDB like this, as it goes
out of scope and the Recordset you've created may or may not be
valid after that. Were I doing a deletion this way (I wouldn't, of
course), I'd code it like this:

Private Sub TableDeleteRecords(strTableName As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDB
Set rst = db.OpenRecordset(strTableName)

If rst.RecordCount <> 0 Then
rst.MoveFirst
Do While Not (rst.EOF)
rst.Delete
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Now, as I said, I'd NEVER do this! It makes no sense whatsoever to
walk a table and delete each record one-by-one when you can do the
same thing with a single SQL statement. Here's how I'd code this:

Private Sub TableDeleteRecords(strTableName As String)
On Error GoTo errHandler
Dim db As DAO.Database

Set db = CurrentDB
db.Execute("DELETE * FROM " & strTableName, dbFailOnError)
Debug.Print db.RecordsAffected & " records deleted."

exitRoutine:
Set db = Nothing
Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in TableDeleteRecords()"
Resume exitRoutine
End Sub

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#7

"christianlott1 (AT) yahoo (DOT) com" <christianlott1 (AT) yahoo (DOT) com> wrote in
news:b67393ea-1e33-4542-8fcb-0df29c236b50 (AT) l8g2000prd (DOT) googlegroups.com
:

Quote:

I thought one always needed to

rst.movelast
rst.movefirst

first to get the correct recordcount..

But you don't NEED an accurate recordcount -- you only need to know
if the recordset is empty, and in DAO, any non-empty recordset will
return 1 or greater as the Recordcount. So, testing for RecordCount
= 0 is absolutely sufficient to tell you if the recordset is empty.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#8

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in
news:j2m372$u3f$1 (AT) dont-email (DOT) me:

Quote:

Only if using a dynamic or keyset cursor. If using a static
cursor, then the number of records is known when the recordset is
populated. The thing is, when using a non-static cursor,
RecordCount should contain -1,

Not in DAO -- that's ADO.

Quote:

since the total number of records is not known until the last
record is navigated to.

The RecordCount property of the DAO recordset returns 0 for an empty
recordset and a non-zero number for a non-empty recordset. Thus,
testing for RecordCount = 0 is absolutely sufficient to determine if
the recordset is empty or not.

Quote:

So I'm very puzzled that it is always containing 153.

This makes no sense to me, either.

Quote:

To the
OP: have you verified that strTableName contains what you think it
contains? Add a
Debug.Print strTableName
line to verify it. Or add a breakpoint to step through the sub and
inspect the variable's value.

That said, I am really puzzled why a recordset is being used here
to do the job of a DELETE query. The sub should be rewritten to
this:

Private Sub TableDeleteRecords(strTableName As String)
Dim sql as String
sql = "DELETE * FROM " & strTableName
debug.print sql 'can be commented out when not debugging
currentdb.execute sql, dbFailOnError
End Sub

One should never use dbFailOnError without an error handler.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#9

David-W-Fenton wrote:

Quote:

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in
news:j2m372$u3f$1 (AT) dont-email (DOT) me:

Only if using a dynamic or keyset cursor. If using a static
cursor, then the number of records is known when the recordset is
populated. The thing is, when using a non-static cursor,
RecordCount should contain -1,

Not in DAO -- that's ADO.

Hmm, not how I remember it, but I'll take your word for it.

Quote:

One should never use dbFailOnError without an error handler.

True. I had assumed the Op had neglected to include it, but we all know the
result of making assumptions don't we.

#10

"zuckermanf (AT) gmail (DOT) com" <zuckermanf (AT) gmail (DOT) com> wrote in
news:2c4372a7-d570-481a-95e5-29e63ca9953b (AT) p37g2000prp (DOT) googlegroups.co
m:

Quote:

It appears that you're missing an "End If" statement....
Fred

If rst.RecordCount = 0 Then Exit Sub

is a complete if block, and does not need an end if.

putting the statement(s) on a separate line does require the end if:
If rst.RecordCount = 0 Then
Exit Sub
End if

Bob

Quote:

On Aug 19, 7:52*am, sparks <spa... (AT) home (DOT) com> wrote:
It was originally done in access 2003 and works fine.
now it does not work in access 2007 and after an update it will
not work in access 2003 either.

I am confused

Private Sub TableDeleteRecords(strTableName As String)
Dim rst As Recordset

* Set rst = CurrentDb.OpenRecordset(strTableName)

* If rst.RecordCount = 0 Then Exit Sub
* rst.MoveFirst
* Do While Not (rst.EOF)
* * rst.Delete
* * rst.MoveNext
* Loop
* rst.Close
End Sub

when you pass any file to this rst.recordcount is always equal to
153. I even passed it a table with nothing in it and it still
says 153.

--
Bob Q.
PA is y I've altered my address.