Recent content by RayProud

mp9, <<It may help to open rsx as a snapshot recordset, e.g.>> Tried that but the diference was minimal. Looks like I've got it as efficient as it's going to get. I should add one thing to this. Despite XP SP2 being installed it has not enabled LongFilenameCaching. I'm awaiting a Hotfix from...

Andy, Why would I need to check that EOF and BOF are both TRUE? Would they ever return different values? Up to now I've always used EOF=False to mean that a record was present. I don't need to return the record - just determine if it exists - hence the Select Count(*) which I guess would be...

mp9, I've tried DCount now and the difference is marginal. 263 secs vs 266 secs with Select Count(*). All this with XP / Access 2003 and an empty database. NT4 / Access 97 did it in 166 secs on a populated one! The price of progress eh! Cheers.

Thanks PHV and mp9. Both should have been indexed but with all the converting and renaming for the testing CaseNumber wasn't indexed. They're both indexed now but I really need to put a timer routine in to accurately measure performance. I'll do that and also change to DCount and see what...

I've discovered why I thought FindFirst was quicker than select count(*). In short it isn't. I was comparing the performance of two databases and the one with FindFirst was empty whereas mine had 40,000 existing records. When I cleared mine down FindFirst was very quick (logical as there was...

Hi mp9, Thanks for those tips. I've already got those set and it's not helped. I've changed to Select Count(*) but the improvement was minimal. Here's a snippet from my code... strSQLFind = "SELECT Count(*) as X FROM tblRedLiab WHERE Staff_No = '" & rst!PayNo & "' AND CaseNumber = " &...

I've converted an Access 97 database to Access 2003 and the performance when using Select Count is dreadful. I've had to revert to FindFirst which is much much faster. I don't understand this because in Access 97 it was just the opposite. I'm holding data in a local table on the front-end and...

Just a follow-up to my earlier post. I've discovered the PC in question is running Microsoft Jet 3.6 and not 4.0 as Microsoft recommend. Could this be the problem and are there any other known issues with Jet 3.6 and Access 2003 I should be aware of? I'm trying to get my IT support people to...

I have a problem with a query that works in Access 97 but doesn't with the same database converted to Access 2003 (SP1). I display a form that requests two pieces of info from the user - a Start Sequence Number and End Sequence Number. These are entered into text boxes. The user presses the...

Hi PH, That did the trick. Thank you very much. Certainly deserving of a star :-)

Hi PHV, I'm checking the UnRead status okay but I'm having a problem with the Move call. I'm getting 'Type Mismatch' on this line molMail.Move (olFolderDeletedItems) Do I need to include another Set command similar to the one I have for the Inbox? ... Set molMAPI =...

Hi Tom, Thank you very much! I was a bit apprehensive when I saw the code was for 2K and although I didn't have one of the references the one I did have was obviously okay. A couple of other questions... 1. The code you suggested is reading all messages in the Inbox. I'm only interested in...

Hi, I'm working on an Access 97 database and one of the requirements is to read the Inbox on the PC where the database is located and to send a reply to the sender. We're using Outlook 98. I have managed to send a message from Access but I can't work out how to read the Outlook Inbox. I'm...

Hi all, I'm using Access97. I have a form where the user enters either a staff number of part of a staff number and I then use the ApplyFilter command to retrieve the matching record(s). But, I can't find how to sort the returned records. The form is bound to a query where the sort order is by...

I've found a resolution to this problem so I thought I'd share it with you just in case it helps anyone else. The problem was the length of the filename being generated. It must not exceed 37 characters excluding ".XLS". If it does then that error is generated. Phew! I can relax again...