 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2006Count records in a table
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? "=?Utf-8?B?TmljaG9sYXMgU2NhcnBpbmF0bw==?=" <NicholasScarpinato@discussions.microsoft.com> wrote in news:1E93EFD4-891D-4698-96C4-21DC163AEA6F@microsoft.com: > 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. 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?) > "=?Utf-8?B?TmljaG9sYXMgU2NhcnBpbmF0bw==?=" > <NicholasScarpinato@discussions.microsoft.com> wrote in [quoted text clipped - 12 lines] > > Tim F 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 [quoted text clipped - 10 lines] > 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? >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 [quoted text clipped - 8 lines] >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? 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 Sorry, missed the "b" in "bof": if rs.bof ... ^^^ TC Sheesh, third time lucky: Sorry, I missed the "b" in "bof": if rs.bof ... ^^^ TC >If it's empty, the MoveLast will fail. The proper check to see if a >recordset is empty, is: [quoted text clipped - 8 lines] >HTH, >TC 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] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.