Solved

Setting Currentproject.Connection.Commandtimeout

Posted on 2002-04-11
9
1,229 Views
Last Modified: 2008-02-20
I have a report with the recordsource set to the name of a valid stored procedure.  I am assuming that in this situation, the connection that is used is currentproject.connection and that the timeout value for executing this report's recordsource is 30 seconds, the value of currentproject.connection.commandtimeout.

On an XP machine (using Office XP with SP1 applied), the report timeouts at 30 seconds.  On 95/98/Me machines (using Office 2000 with SR-2 applied), the report executes just fine and, by the way, takes longer than 30 seconds to run.

Is it possible to change the value of currentproject.connection.commandtimeout to 0 so I don't have to worry about this timeout?  I tried through code with no success.  Maybe there is another perspective to try ... help!  Thanks!
0
Comment
Question by:KKWohlgemuth
9 Comments
 
LVL 3

Expert Comment

by:dapperry
ID: 6935224
The commandtimeout is read-only for open connections. Therefore, the CurrentProject.connection.commandtimeout will always be read-only. A couple of things you might want to consider:

1) I'm not sure if this is the same thing, but you can change the value of the connect timeout of the project by clicking on File | Connection... and then changing it in the Advanced tab of the dialog.

2) Of course you could always make a new connection, set the commandtimeout to 0, set the connectionstring to currentproject.connection.connectionstring, and then open the new connection object.

Hopefully, one of these suggestions will work for you.

:) dapperry
0
 

Author Comment

by:KKWohlgemuth
ID: 6935263
daperry,

Thanks for your suggestions...

1) This was one of the first things I played with.  It seems to me though, that this is simply a timeout for the project file trying to establish its connection with the identified SQL Server database.

2) This is along the lines of some things I pondered/tried.  But how can I set the reports and forms to use this new connection object?  There seems to be internal .adp connection and command objects that aren't exposed for developers like myself to modify except through Microsoft's interfaces within the project file environment.

Unless I am misunderstanding something, it appears my problem still lingers on ... thanks anyhow!
0
 

Author Comment

by:KKWohlgemuth
ID: 6935303
daperry,

Thanks for your suggestions...

1) This was one of the first things I played with.  It seems to me though, that this is simply a timeout for the project file trying to establish its connection with the identified SQL Server database.

2) This is along the lines of some things I pondered/tried.  But how can I set the reports and forms to use this new connection object?  There seems to be internal .adp connection and command objects that aren't exposed for developers like myself to modify except through Microsoft's interfaces within the project file environment.

Unless I am misunderstanding something, it appears my problem still lingers on ... thanks anyhow!
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 3

Expert Comment

by:dapperry
ID: 6936673
Hi,
      Try this bit of code, in your report open event:

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

con.ConnectionString = CurrentProject.Connection.ConnectionString
con.CommandTimeout = 0
con.Open

strSQL = "SELECT * FROM MyTable"
Set rst = con.Execute(strSQL)

Me.Recordset = rst

'Close up shop
Set rst = Nothing
Set con = Nothing

Haven't had time to test it, but it should work.

:) dapperry
0
 

Author Comment

by:KKWohlgemuth
ID: 6945704
dapperry, thanks again for your suggestion ... setting a report's recordset isn't allowed, unfortunately. In your above sample code, "me.recordset = rst" isn't a valid statement.
-----
Specifically, I want to change the "internal default connection's commandtimeout value" from it's current value of 30 to 0.
-----
Maybe there's a registry setting I could alter ... I don't know ... I need to find an answer!  I'm increasing the points to 500 ... Thanks in advance for any help!
0
 

Author Comment

by:KKWohlgemuth
ID: 6945733
whoops, can't exceed 300 points, sorry
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7103229
for KKWohlgemuth

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7129954
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0
 
LVL 3

Expert Comment

by:garyb2008
ID: 25159070
Its been a while for this question, but try setting OLE/DDE Timeout to 0 under tools/options
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question