.recordcount is always doubled

[Bill Raterink]

I have the following code that should import "selected" file names from a
table.
there are 370 files names in the table & the "Limit" is being st to 740.
Any ideas why. Thanks.

' Import each selected file
Set db = CurrentDb()
Set rs = db.openrecordset("SelectedFiles")

With rs
.MoveFirst
Limit = .RecordCount
.MoveFirst
For Item = 1 To Limit - 1
If !FDSSelected Then
Import.StatusBarText = "Importing " & _
!FDSFullPath & "\" & !FDSFileName
Call ImportSelectedFile(!FDSFullPath, !FDSFileName,
Me.SCOGroup)
.Edit
!FDSSelected = False
.Update
FDSFNameList = FDSFNameList & !FDSFileName & " "
End If
.MoveNext
Next Item
End With

Set rs = Nothing
Set db = Nothing

 

[David H]

As I recall, you have to .MoveLast first to get the record count. The code
below has two .MoveFirsts.

I assume that 'Selected Files' is a table in the DB? How many records are in
the actual underlying table?

 

[David H]

What a minute - SCO Group? You're not going to sue me are you?

 

[Bill Raterink]

Self Check Out, NOT Santa Cruz Oper. ;-)
& I tried the moveLast, it didn't help. There are actually 370 lines in the
table, and the debug.print of rs.recordcount shows 740 as soon as I open it.
Thanks.

 

[JimBurke via AccessMonster.com]

I'm not sure if you can get an accurate RecordCount with OpenRecordset.
Here's what I've always done that's worked for me:

rst.Open "yourQuery/TableName,, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.MoveLast
rst.MoveFirst
myCount = rst.RecordCount

This is using an ADODB recordset. Not sure if you're using ADO or DAO - I
don't know if the open parms are the same for DAO or not.

 

[JimBurke via AccessMonster.com]

Whoops, there's a typo that you may miss on first look. should be

rst.Open "Query/TableName", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

I'm not sure if you can get an accurate RecordCount with OpenRecordset.
Here's what I've always done that's worked for me:

rst.Open "yourQuery/TableName,, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.MoveLast
rst.MoveFirst
myCount = rst.RecordCount

This is using an ADODB recordset. Not sure if you're using ADO or DAO - I
don't know if the open parms are the same for DAO or not.

I have the following code that should import "selected" file names from a
table.

[quoted text clipped - 26 lines]

Set rs = Nothing
Set db = Nothing

 

[Bill Raterink]

Jim,
Thanks for the help, but I'm not Access literate enough to know if I'm using
ADO or DAO. Also, I don't know what the connection, Keyset, & lockoptimistic
parameters on your open represent. This program has worked for years, and
all I changed was the import spec to add a field at the beginning of the
record. The spec actually SKIPS this field so the code could stay the same.
I don't think the Spec chg is the issue, since if I set a break point & chg
the Limit back to 370, the program works fine. I just can't see how or why
the record count is doubling. Thanks, -Bill.

I'm not sure if you can get an accurate RecordCount with OpenRecordset.
Here's what I've always done that's worked for me:

rst.Open "Query/TableName", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.MoveLast
rst.MoveFirst
myCount = rst.RecordCount

This is using an ADODB recordset. Not sure if you're using ADO or DAO - I
don't know if the open parms are the same for DAO or not.

I have the following code that should import "selected" file names from a
table.

[quoted text clipped - 26 lines]

Set rs = Nothing
Set db = Nothing

 

[David H]

Right silly me. SCO Group doesn't have the respect, credit or cash to even
hire an undergrad still working on their Paralegal degree.

 

[Bill Raterink]

David, I think you're safe for a while yet. ;-)
Also, I clicked TWICE to double the actual entries in the table to 740, and
when I checkboxed the one to imprt, I did NOT get the 3021 error like before.
I assume this is because I did not run off the end of the table. I got a
different error in the actual report, but I'm calling this progress.
So it appears that my db.recordcount doesn't ALWAYS double. Only on the
first time. Any ideas? Thanks, -Bill.

 

[Steve Sanford]

I Lost you post for a while .........

From your example, I can't tell if the variables have been declared. You
could be affecting a variable that is global.

Looking at the code, it really doesn't matter how many records there are,
only how many records there are where [FDSSelected] = TRUE. If you select
only the records where [FDSSelected] = TRUE, the code will complete sooner.

I've modified your code (untested but should run) - try this on a copy.......

'-------------------------------------------------
Option Compare Database
Option Explicit 'should always have this line


'Public Sub t2()
'you must have a reference set for
' Microsoft DAO 3.6 Object Library
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim sSQL As String

' Import each selected file
Set db = CurrentDb()

sSQL = "SELECT *"
sSQL = sSQL & " FROM SelectedFiles"
sSQL = sSQL & " WHERE [FDSSelected] = TRUE"
Set rs = db.OpenRecordset(sSQL)

'check for records
If Not rs.BOF And Not rs.EOF Then
With rs
.MoveFirst
Do While Not .EOF
Import.StatusBarText = "Importing " & _
!FDSFullPath & "\" & !FDSFileName
Call ImportSelectedFile(!FDSFullPath, !FDSFileName, Me.SCOGroup)
.Edit
!FDSSelected = False
.Update
FDSFNameList = FDSFNameList & !FDSFileName & " "
.MoveNext
Loop
End With
End If

'clean up
rs.Close
Set rs = Nothing
Set db = Nothing

'End Sub
'-------------------------------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Jim,
Thanks for the help, but I'm not Access literate enough to know if I'm using
ADO or DAO. Also, I don't know what the connection, Keyset, & lockoptimistic
parameters on your open represent. This program has worked for years, and
all I changed was the import spec to add a field at the beginning of the
record. The spec actually SKIPS this field so the code could stay the same.
I don't think the Spec chg is the issue, since if I set a break point & chg
the Limit back to 370, the program works fine. I just can't see how or why
the record count is doubling. Thanks, -Bill.

I'm not sure if you can get an accurate RecordCount with OpenRecordset.
Here's what I've always done that's worked for me:

rst.Open "Query/TableName", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.MoveLast
rst.MoveFirst
myCount = rst.RecordCount

This is using an ADODB recordset. Not sure if you're using ADO or DAO - I
don't know if the open parms are the same for DAO or not.

I have the following code that should import "selected" file names from a
table.
[quoted text clipped - 26 lines]
Set rs = Nothing
Set db = Nothing

 

[Bill Raterink]

Steve, Glad you found me... & thanks for the code, I'll incorporate it into
my module. Yes, my fields are declared. This was a Q&D app that just saved
my coworker (Also Bill) about 17 hrs a week, and this has worked since 2003.
That's what makes it so wierd. Also, when I copied the "broken" .mdb file to
my own laptop, it worked fine. This pushed me to thinking it was an
environmental issue, but neither of us have had any updates (that we know of
;-)

You're right about the "how many records" not being an issue, but since this
was a daily file (365/366 is all there'd be) and speed was NOT an issue, I
just processed all files looking for the selected flag. Not as eloquent as
your code, but didn't require SQL statements either. So I figured it was a
fair trade off.

Secondly, how do I determine the Library ref you mentioned below...
'you must have a reference set for Microsoft DAO 3.6 Object Library
Dim db As DAO.Database...Is this different than CurrentDb?
Dim rs As DAO.Recordset....Are the DAO. actually needed?
I assume that if MY Access was ref'ing DAO & his was ADO, I'd have much
worse problems, but I don't know how to check. Thanks, -Bill.

 

[Steve Sanford]

Hi Bill,

Comments inline.....

Steve, Glad you found me... & thanks for the code, I'll incorporate it into
my module. Yes, my fields are declared. This was a Q&D app that just saved
my coworker (Also Bill) about 17 hrs a week, and this has worked since 2003.
That's what makes it so wierd. Also, when I copied the "broken" .mdb file to
my own laptop, it worked fine. This pushed me to thinking it was an
environmental issue, but neither of us have had any updates (that we know of
;-)

You're right about the "how many records" not being an issue, but since this
was a daily file (365/366 is all there'd be) and speed was NOT an issue, I
just processed all files looking for the selected flag. Not as eloquent as
your code, but didn't require SQL statements either. So I figured it was a
fair trade off.

You don't really need the SQL, you could create a query and use it to get
the same recordset.

Secondly, how do I determine the Library ref you mentioned below...
'you must have a reference set for Microsoft DAO 3.6 Object Library
Dim db As DAO.Database...Is this different than CurrentDb?
Dim rs As DAO.Recordset....Are the DAO. actually needed?

If you have both the Microsoft DAO 3.6 Object Library and the Microsoft
ActiveX Data Objects 2.1 Library references set, you need to be explicit
about which library to use. If not specified, the ADO library is used. Or you
can uncheck the reference to the ADO library.

I never use ADO and usually uncheck the reference, but as a matter of habit,
I always use DAO.Database & DAO.Recordset.

I assume that if MY Access was ref'ing DAO & his was ADO, I'd have much
worse problems, but I don't know how to check. Thanks, -Bill.

Yes, I would think the code would throw an error, not double a variable.

One thing, you were setting the recordset to Nothing without closing it
first, leaving it in memory. Maybe when the code was run a second time,
somehow the previous recordset record count was added to the new recordset
record count. Yea, reaching but......???

To check references:

When viewing a code page, in the menu bar, click on TOOLS/REFERENCES.



HTH

 

[Bill Raterink]

Thanks Steve, I knew it was in there but I just couldn't find it. It seems I
have BOTH libraries checked, the DAO 3.6 AND the ActiveX Data Objects 2.6.
The DAO comes first in the list, if that matters. I'll put DAO. on all my
references just as a precaution.

Also, The good & the bad .mdb files are the same. I can't check the other
Bill's box as he's gone, but can I assume that the Tools/Ref settings FOLLOW
the .MDB, or are they MS OFFICE installation dependent. IOW, should I expect
his box to be the same as mine. Thanks, -Bill.

 

[David W. Fenton]

=?Utf-8?B?QmlsbCBSYXRlcmluaw==?=

I knew it was in there but I just couldn't find it. It seems I
have BOTH libraries checked, the DAO 3.6 AND the ActiveX Data
Objects 2.6. The DAO comes first in the list, if that matters.
I'll put DAO. on all my references just as a precaution.

Are you using both? If so, why in the world?

 

[Douglas J. Steele]

The References follw the MDB file, but that doesn't mean you can assume that
the references will work on his box just because they work on yours.
References are tempermental: if either you or Bill have a different version
of a particular reference, you can run into problems. For a detailed
discussion, see http://www.accessmvp.com/DJSteele/AccessReferenceErrors.html

 

[Bill Raterink]

I'm not really using both, that's what I'm set up for. Most of what I do is
help other people, so I don't know which one they'll need. As long as you
don't think having both checked is a problem, I'll leave it. I just can never
remember how to get there to change them when I need to. Thanks for the help.

 

[Bill Raterink]

Thanks for the info Doug, I think Bill & I should always have the same level,
but you're probably right in that I shouldn't depend on it. Good to know
that the refs follow the mdb. Thanks, -Bill.

 

[David W. Fenton]

=?Utf-8?B?QmlsbCBSYXRlcmluaw==?=

As long as you
don't think having both checked is a problem, I'll leave it.

I think it *is* a problem, unless you meticulously disambiguate all
your uses of the overlapping objects, like Recordsets:

Dim rs1 As DAO.Recordset
Dim rs2 AS ADODB.Recordset

If you don't need both, don't have both.

Indeed, you can use a lot of ADO without needing to have a
reference. Take a look at the code the Switchboard wizard generates
-- it's ADO but doesn't need a reference to work because it uses
late binding. This is good coding practice.

In an Access application with a Jet back end or using ODBC linked
tables to a server back end, your default library should be DAO.
When you need ADO, you can use late binding (you shouldn't need it
very often).