July 30, 2003 at 10:08 am
1) Have you run the code in Query Analyzer and seen any difference?
2) If you have, look at the execution plan and see where the most processing time is being taken up.
3) Can you convert to a stored procedure?
July 30, 2003 at 11:01 am
I have tried running it as a stored proc and gotten the same result. I will run it in query analyzer next and see if I can see anything. Thanks.
July 30, 2003 at 11:12 am
It ran fine (82 seconds) in query analyzer, so I'm not sure the execution plan will hold any answers to why it hangs when the same query is posed to the database via the .adp access program.
The query is written in a module as ADO.
Thanks for your help!
July 30, 2003 at 11:25 am
82 seconds is still a really long time for a simple query. Regardless, what is the ADO code you are executing? There are 2 timeouts (I know, it's confusing); one for the connection and one for the command object. Go ahead and post some code, if possible.
July 30, 2003 at 11:47 am
The majority of that time (82 seconds) seemed to be from scanning clustered index.
Code:
Sub ReleaseHold()
Dim cmd1 As New ADODB.Command
Set cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = "delete from swbt_summary"
cmd1.Execute
cmd1.CommandText = "Insert into swbt_summary SELECT eventcode, compcode, ltrim(rtrim(swbt_hold.lettercode)) lettercode, CONVERT(varchar, filedate, 101) filedate, 'swbt_hold' tblname, COUNT(*) COUNT, 'lettercode' as code FROM swbt_hold WHERE release = 0 GROUP BY eventcode, compcode, swbt_hold.lettercode, filedate order by lettercode"
cmd1.Execute
The error is 'Run time error (bunch of numbers) Timeout Expired'
Thanks!
July 30, 2003 at 9:19 pm
before you execute, add the following:
cmd1.CommandTimeout = 0
This will cause the command to not timeout. The default timeout period in Access is 30 seconds.
I would also suggest verifying that the fields eventcode, compcode, lettercode, filedate and release are indexed in the swbt_hold table. (These are the fields in your WHERE and GROUP BY clauses).
July 31, 2003 at 6:29 am
Hmm, I've never seen an Insert statement like that...would someone enlighten me as to why you would need a Group By / Order By in an Insert Statement? I'm not being sarcastic here =P.
July 31, 2003 at 7:49 am
quote:
Hmm, I've never seen an Insert statement like that...would someone enlighten me as to why you would need a Group By / Order By in an Insert Statement? I'm not being sarcastic here =P.
You need the GROUP BY because it's a SELECT statement taking details into a summary table. The ORDER BY is useful if subsequent SELECTs from the summary table have no ORDER BY clause and the author wants a default ordering. This, of course, assumes that the inserted into summary table is a HEAP, and will not reorder the inserted rows based on its clustering key.
Edited by - jpipes on 07/31/2003 07:50:20 AM
July 31, 2003 at 8:00 am
Ok, I think I read it wrong, I thought the statement was only Adding one Row to a Table...sorry about that.
July 31, 2003 at 8:18 am
Thank you very much! Setting the command timeout to zero seems to have solved the problem.
July 31, 2003 at 8:22 am
No problem - glad I could help.
I am the dba for my company and we run our business on a SQL database with an ADP front end. I have had to learn a lot of tricks to keep this working correctly (including this one).
A side effect of this timeout thing is how it affects Access reports - the default timeout in Access is 30 seconds, as I mentioned earlier. If you are running a command or opening a recordset, you can get around that with the CommandTimeout setting. However, if you are basing a report on a recordset, you cannot override the timeout property (bummer).
The only workaround I have been able to come up with is to open a recordset based on a command and then assign that recordset to the recordsource of the report.
Anyway, enough rambling. Have fun!
July 31, 2003 at 8:30 am
Actually, for your Recordset Issue, all you should have to do is set the "CommandTimeOut" Property of your Connection Object to = 0 or a very high number as well. I usually try to keep my Connection and Command TimeOuts to be the same thing in my programs so I can determine if it's the Connection or the Command that is timing out.
July 31, 2003 at 8:33 am
*The only workaround I have been able to come up with is to open a recordset based on a command and then assign that recordset to the recordsource of the report.*
I have actually had to do that myself as well in order to make the report load properly. Its always nice to know I'm not the only one scratching my head with these products 🙂
July 31, 2003 at 8:41 am
The problem with the timeout issue on the connection for an Access ADP is that the Project is the connection and I have not found a way to set the timeout on that.
In case you don't use ADPs, they are 'always connected' to the SQL Server. You are not establishing a connection to it each time you need access.
If you were, you could set the timeout of the connection object.
This is something that MS should absolutely address in Access for Office System 2003 if they want more people to use Access projects.
I myself and moving more and more to Web based VB.NET front ends, and away from Access front ends (of course, then I lose the wonderful Access Reporting Engine - have to see how Reporting Services will be!!)
July 31, 2003 at 8:51 am
I have been delving heavily into VB.NET myself as a way to get away from Access, b/c of its 'issues' - like the ones you mention.
I just hope they get their reporting up to a better level, or upgrade access, to address some of these needs! I know they have to be pretty common issues given the typical business uses of data.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply