can someone tell me what is wrong with this

#11

Thats the way I do it. The OP's way is just slow hard (and pointless)
work. Let the JET engine do the work for you, its much much faster and
much much easier to debug - and also as an aside there is basically only
one way it could fail and that is if the table doesnt exist (eg/ you
spell the table name wrong or something).
--
Cheers

The Frog

#12

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

Quote:

David-W-Fenton wrote:
"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.

I never encountered a recordcount of -1 until I tried ADO -- DAO
never produced that number.

That said, I can't recall ever coding a DAO recordset as anything
other than a dynaset, but the other two types should return accurate
recordcounts, so surely they'd never return -1, either, which is not
a valid recordcout.

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.

Everybody posts .Execute strSQL, dbFailOnError, but nobody ever
mentions the error handler or includes it. I think it's important to
do so, as otherwise, it can get pretty ugly.

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

#13

Changed it to a dynaset and it works.
Thanks for the tip.

funny how this has worked for about 7 or 8 years then it just quit
working.

even tracing it on an empty table at recordcount it would show 153.

must be something in an update to try and force users to update to
newer version. Break it and force sales
I am beginning to hate microcrap.

On Fri, 19 Aug 2011 09:52:35 -0500, sparks <sparks (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.

#14

What tha

ok I changed it to a dynaset and it worked.

moved over to another computer with the old code and it worked.
THIS JUST GETS STRANGER AND STRANGER

On Mon, 22 Aug 2011 09:05:19 -0500, sparks <sparks (AT) home (DOT) com> wrote:

Quote:

Changed it to a dynaset and it works.
Thanks for the tip.

funny how this has worked for about 7 or 8 years then it just quit
working.

even tracing it on an empty table at recordcount it would show 153.

must be something in an update to try and force users to update to
newer version. Break it and force sales
I am beginning to hate microcrap.

On Fri, 19 Aug 2011 09:52:35 -0500, sparks <sparks (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.

#15

Stop beating your head against the wall with this. You should be using a
delete query, not a recordset, for this task, anyways. Change it to the code
I recommended (add an error handler) and move on to the next task.

Lastly, I suggest you use EOF* to test for an empty recordset instead of
depending on recordcount anyways.
If rst.EOF Then Exit Sub

* You only need to test for EOF with a recordset you have just opened. No
need to perform a Movefirst - if a recordset contains records, it will
automatically point to the first one when opened. If you have performed any
record navigation or record deletion, you need to check for BOF in addition
to EOF:
If rst.EOF AND rst.BOF Then Exit Sub

sparks wrote:

Quote:

What tha

ok I changed it to a dynaset and it worked.

moved over to another computer with the old code and it worked.
THIS JUST GETS STRANGER AND STRANGER

On Mon, 22 Aug 2011 09:05:19 -0500, sparks <sparks (AT) home (DOT) com> wrote:

Changed it to a dynaset and it works.
Thanks for the tip.

funny how this has worked for about 7 or 8 years then it just quit
working.

even tracing it on an empty table at recordcount it would show 153.

must be something in an update to try and force users to update to
newer version. Break it and force sales
I am beginning to hate microcrap.

On Fri, 19 Aug 2011 09:52:35 -0500, sparks <sparks (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.

#16

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

Quote:

Changed it to a dynaset and it works.

But your original code was a dynaset, because it specifies no
recordset type (dynaset is the default).

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

#17

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

Quote:

ok I changed it to a dynaset and it worked.

moved over to another computer with the old code and it worked.
THIS JUST GETS STRANGER AND STRANGER

Is Win7 SP1 or Office 2010 SP1 involved in any of these cases?

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

#18

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in
news:j2tog3$ljk$1 (AT) dont-email (DOT) me:

Quote:

Lastly, I suggest you use EOF* to test for an empty recordset
instead of depending on recordcount anyways.

I strenuously disagree with this. A DAO recordset's Recordcount is
completely reliable at all times for determining if a recordset is
EMPTY -- it is never zero when records are returned.

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

#19

On 24/08/2011 22:18:02, "David-W-Fenton" wrote:

Quote:

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

Changed it to a dynaset and it works.

But your original code was a dynaset, because it specifies no
recordset type (dynaset is the default).

Not the slightest bit of help.... but interesting

http://en.wikipedia.org/wiki/153_(number)

Phil