Selasa, 19 Juli 2011

RE: [MS_AccessPros] Timeout on SQL Query (to SQL tables) built and launched in Access

Mike,

You might want to click the (?) Help button and enter "Pass-Through Query" for a more complete description. Basically a pass-through is a special query that sends a SQL statement directly to your back-end database. It might return records or run a stored procedure or whatever. It is typically much more efficient than a query based on tables linked from the same back-end database.

The SQL syntax must match the requirements of the back-end database. For instance GetDate() comparable to Now(), IsNull() is similar to Nz(), dates are delimited with single quotes rather than #,... You can't reference controls on forms or other Access objects. I don't find this too much of a limitation since I use code to modify the SQL property of the pass-through as needed.

You can use the records returned from this query to append to a local temp table.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: mikewolfstone@yahoo.com
Date: Tue, 19 Jul 2011 09:11:07 -0700
Subject: Re: [MS_AccessPros] Timeout on SQL Query (to SQL tables) built and launched in Access


What is a pass-through query? Also, this is an 'Insert into' query into a local temp table.

From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Monday, July 18, 2011 2:45 PM
Subject: RE: [MS_AccessPros] Timeout on SQL Query (to SQL tables) built and launched in Access

Have you considered using a pass-through query? This resulting recordset is not updateable but it's much much more efficient.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: mikewolfstone@yahoo.com
Date: Mon, 18 Jul 2011 21:24:54 +0000
Subject: [MS_AccessPros] Timeout on SQL Query (to SQL tables) built and launched in Access

I keep getting a SQL timeout error (#3146) on a query built in Access and launched in Access against a couple of SQL Server (2005) tables.
I have tried setting timeout values several ways, but none seem to work.
Does anyone have a foolproof ODBC way to set the timeout to 0 so the query will not timeout?
My most recent attempt is: CurrentDb.QueryTimeout = 600 ' tried 0 also
The query is launched as: docmd.runsql sqlstring, false
Any thoughts?
Thanks

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]


[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Tidak ada komentar:

Posting Komentar