January 21, 2004 at 9:48 am
I have an access 2000 program that links to tables in SQL server 2000. While the program is adding huge amounts of data( > 100,000 rows) then I get the following error :
>>>>[Microsoft][ODBC SQL Server Driver][SQL Server] Timeout expired
Where can I find the setting to change the timeout specification ?
(When there are fewer rows the processing goes through without an error.)
January 22, 2004 at 7:04 am
Sounds like you're using a query, open it in design view click the Properties button (make sure it says Query Properties not field properties) and set the ODBC Timeout to something bigger. Access defaults all queries to 60 seconds. It will save the new setting so you won't have to set each time.
Caution though that if you set it too high you may not be alerted to the query taking longer than is acceptable. Just because you can let it run for 10 minutes or 30 minutes doesn't mean the user will apprecaite it and there may be a rethink required on the query.
Hope that helps
K
January 22, 2004 at 8:58 am
Sorry, I thought this might be a SQL server question and that is why I posted it in this discussion group.
Thanks for your response , kelseyv......
Actually I do not have a query but the following command that causes the timeout error:
Call DoCmd.TransferDatabase(acExport, "ODBC Database", _
"ODBC;DSN=Volume_Control;UID=user1;PWD=user!", _
acTable, "tblFilename" & processPane.ListItems(ctr), "tblTemperooeste", False, False)
I do not see any way to add a timeout parameter in this function. Would Appreciate any help.
January 22, 2004 at 12:15 pm
I know I've had to take db.execute SQL statements and turn them into queries with higher timeout settings to get them to run.
You may be looking at the same thing because this is an access talking to SQL issue. The only other timeout setting I know of is CurrentDb.QueryTimeout but I don't think this would apply to the transfer statement.
You may need to link the sql table and create an append query. You can alter the queries sql and timeout setting via code if it needs to be dynamic.
That's about all I can suggest. Hope you get it working
K
January 22, 2004 at 12:31 pm
Thanku for your response. I will try the options that you have mentioned .
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply