Count records in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a function I'm coding which copies two files from the server to a
location on the local drive, imports them into Access (I have to copy the
files because they aren't saved as .txt files on the network, even though
they're in text format, so Access won't import them unless I rename them),
and then compares the two files. Then whatever records in the first file
match the account numbers in the second file are put into a new table and
exported to a .txt file. My problem is, the second file contains decline
codes, and I only need to match up the records that correspond to three codes
in this field. If none of those three codes exist, the function needs to exit
without attempting to export anything. Instead I end up exporting a blank
file. What I want to do is look at the export table and, if the table is
empty, exit the function. How do I do this?
 
Then whatever records in the first file
match the account numbers in the second file are put into a new table
and exported to a .txt file.
Click to expand...

I have no idea what Decline Codes are; but this sounds like a
straightforwad old-fashioned text processing problem. Have you looked at
PERL or sed/grep etc and getting rid of the database stage altogether? Come
to think of it, even Excel would probably crack this easier.

Just a thought


Tim F
 
Well, there's two problems with that scenario: One, we don't have perl or
sed/grep at work, and two, I wouldn't have the foggiest idea what to do with
them if we did. ;) And yes, I could do this the old way, which is open file
one in Excel, open file two in Excel, vlookup the accounts from file one in
file two, copy the matching records out to a new sheet, format it down to csv
format, save it, then rename it to what I need for our upload server. But
Access can do all that in a fraction of the time it takes me to do it
manually, so why not let it? And it only took me 4 hours to design the entire
database anyway. (Plus I need something that A> everybody at work has access
to and B> can be made to look pretty for the boss.)

The code works, and everything I need to do gets done, I just don't want
excess blank files lying around cluttering up my directories. And as simple
as it sounds, the process is rather complicated when you get down to all the
specifics. But the specifics aren't what I'm having the trouble with, like I
said before, everything works just like I need it to, except that I don't
know how to programmatically make the code stop when the table is empty. I
can do it with macros and queries, just not in code (not that it can't be
done, I just don't know how... although maybe if I do a macro and then
convert it to VB that will work?)
 
It's hard to help, unless you show us some code. There are dozens of
ways you might be doing what you say you are doing.

HTH,
TC
 
If you create a table or query in the process perhaps you could use some
variant of this code to check to see if you have records in the query?:

Sub AnythingThere()
Dim dbs As Database, rstDetail As Recordset, strQ As String strQ = "SELECT *
FROM tablename;"
Set dbs = CurrentDb
Set rstDetail = dbs.OpenRecordset(strQ)
rst.MoveLast
IF rstDetail.RecordCount = 0 Then
MsgBox "No records, trap for this error in your code."
End if
rstDetail.Close
Set dbs = Nothing
End Sub
 
I have a function I'm coding which copies two files from the server to a
location on the local drive, imports them into Access (I have to copy the
files because they aren't saved as .txt files on the network, even though
they're in text format, so Access won't import them unless I rename them),
and then compares the two files. Then whatever records in the first file
match the account numbers in the second file are put into a new table and
exported to a .txt file. My problem is, the second file contains decline
codes, and I only need to match up the records that correspond to three codes
in this field. If none of those three codes exist, the function needs to exit
without attempting to export anything. Instead I end up exporting a blank
file. What I want to do is look at the export table and, if the table is
empty, exit the function. How do I do this?
Click to expand...

If I understand you correctly, all you need to do is determine if the table is
empty, right?

Open a recordset on the table and check the RecordCount property.

<air code>
rs.MoveLast
If rs.RecordCount > 0 Then
' Insert export code here
Else
Exit Function
End if
</air code>

If you are making a recordset to create your table with, you can do the same
thing there and not and not even make the table in the first place.

HTH,
RD
 
If it's empty, the MoveLast will fail. The proper check to see if a
recordset is empty, is:

if rs.of AND rs.eof then msgbox "empty!"

Alternatively, the following syntax will grab the record count directly
out of the table header, without opening a recordset at all:

DCount ( "*", "MyTable" )

HTH,
TC
 
If it's empty, the MoveLast will fail. The proper check to see if a
recordset is empty, is:

if rs.of AND rs.eof then msgbox "empty!"

Alternatively, the following syntax will grab the record count directly
out of the table header, without opening a recordset at all:

DCount ( "*", "MyTable" )

HTH,
TC
Click to expand...

Yeah, I realized that right after posting. It's not necessary to "Move"
anywhere just to determine if the RecordCount is >0. That property is available
as soon as the recordset is opened. MoveLast should only really be used when
you know the recordset isn't empty AND you absolutely need an accurate record
count on a newly opened recordset. According to the DAO Reference, doing this
will slow down the application's performance.

As to the DA function, I never really got into using them. I suppose I should
look into it. I've seen a lot of discussion in here regarding performance hits
when using DLookup in loops. Are there similar hits with the other DA
functions?

Thanks,
RD
 
Sorry, I just saw your reply. My newsreader is giving me problems at
present.

For each table, MS Jet stores a "number of records" counter in the
table header block within the mdb file. So Jet can tell the # of
records in any table, just by looking at the header block for that
table. It does not need to open a recordset, or traverse the records in
the data blocks, or anything suchlike.

DCount has an optimization that will get the # of records in that way,
/if/ you use a quoted asterisk as the first parameter. So, I think you
will find that DCount("*","MyTable") is just about as fast as it gets,
with a Jet database.

HTH,
TC [MVP Access]
 
Back
Top