Access Pass-through Query to SQL Server

[Kirk P.]

I'm getting a "Query timeout expired" error when running my Access
pass-through query. The query works when I retrieve a small date range, but
when I expand the date range I get the timeout error.

I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native
Client}; Server=10.20.10.149,5151; Connect Timeout=0;
Database=master;UID=myUID;PWD=mypassword;

Is there something I need to add to the connect string syntax to allow the
query to complete?

 

[Bob Barrows]

I'm getting a "Query timeout expired" error when running my Access
pass-through query. The query works when I retrieve a small date
range, but when I expand the date range I get the timeout error.

I've got this in the ODBC Connect String property: ODBC;Driver={SQL
Native Client}; Server=10.20.10.149,5151; Connect Timeout=0;
Database=master;UID=myUID;PWD=mypassword;

Is there something I need to add to the connect string syntax to
allow the query to complete?

It's not in the connect string - it's in the query properties: it's the
ODBC Timeout property.
However, as Dale says, you should probably work on optimizing the query.
Perhaps an index on the backend database would help performance, if the
problem is not the sheer number of records being returned. If the
latter, you should probably consider filtering to get a more manageable
result set. It's really not a good idea to be pulling the whole table
across the wire very often.

 

[Kirk P.]

The query runs fine when running it through SQL Server Management Studio, but
your comment on the ODBC timeout property of the Access query was right on.
I had that setting to 0 for the pass-through query. But the pass-through
query is the source for an append query, and the append query had the setting
to 60. Changing it to 0 in the append query solved the problem.

Thanks!

 

[Bob Barrows]

I would like to suggest using a different technology to tackle this
problem. Given the amount of time needed for the task, it does not sound
suitable for passthrough queries. Either Get External Data or a SQL SSIS
package would likely be more efficient.

 

[MC_FRx_99]

I'm having a similar problem with Time-Outs as follows:

The Error message I'm getting from the Query is:
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

I'm doing an Append to a Local Access table from an MS-SQL 2000 Data Source.
(Using Access 2003, which I just converted to "Access 2003 Format" using the
DB tools.)

The SQL Code is as follows:
INSERT INTO ITEMSALE_L ( IS_DATE, IS_ITEM, IS_QTY, IS_SALES, IS_COMPANY )
SELECT dbo_ITEMSALE.IS_DATE, dbo_ITEMSALE.IS_ITEM, dbo_ITEMSALE.IS_QTY,
dbo_ITEMSALE.IS_SALES, dbo_ITEMSALE.IS_COMPANY
FROM dbo_ITEMSALE
WHERE
(((Month([dbo_ITEMSALE].[IS_DATE]))=[Forms]![Period_Choice]![Form_Period])
AND ((Year([IS_DATE]))=[Forms]![Period_Choice]![Years_List]));

The Form noted is one I created.

I looked all over the DB and the Query, yet couldn't find where to modify
the Time-out Setting.

I did see one in my SQL 2005 SQL Server Management Studio for that
particular Server. However, I am loath to make any changes there.

Is it correct that if I make a change, it will only be for MY connection to
that server?

Is there anywhere else to make a change to the Time-out setting? Currently
it is set for 600 Seconds (10 Minutes), although I'll get the Time-Out error
Before 10 Minutes has passed.

Hope that I've been specific enough in my posting. I'll be monitoring this
issue if anyone needs any additional information.

Regards,
J. Falk

 

[MC_FRx_99]

Like Roseann Rosannadanna said: "Never Mind!"

When researching Pass-Through Queries, I found the way to change the
Time-Out Properties on the given Query.

My other question is: As a relative newcomer to Access, would there be an
advantage to using a "Pass-Through Query"? Is the overall concept that
instead of sending MS-Access Commands to the SQL Database, that instead I'm
sending a direct SQL Query?

Thanks in advance for any assistance you can give.

I'm having a similar problem with Time-Outs as follows:

The Error message I'm getting from the Query is:
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

I'm doing an Append to a Local Access table from an MS-SQL 2000 Data Source.
(Using Access 2003, which I just converted to "Access 2003 Format" using the
DB tools.)

The SQL Code is as follows:
INSERT INTO ITEMSALE_L ( IS_DATE, IS_ITEM, IS_QTY, IS_SALES, IS_COMPANY )
SELECT dbo_ITEMSALE.IS_DATE, dbo_ITEMSALE.IS_ITEM, dbo_ITEMSALE.IS_QTY,
dbo_ITEMSALE.IS_SALES, dbo_ITEMSALE.IS_COMPANY
FROM dbo_ITEMSALE
WHERE
(((Month([dbo_ITEMSALE].[IS_DATE]))=[Forms]![Period_Choice]![Form_Period])
AND ((Year([IS_DATE]))=[Forms]![Period_Choice]![Years_List]));

The Form noted is one I created.

I looked all over the DB and the Query, yet couldn't find where to modify
the Time-out Setting.

I did see one in my SQL 2005 SQL Server Management Studio for that
particular Server. However, I am loath to make any changes there.

Is it correct that if I make a change, it will only be for MY connection to
that server?

Is there anywhere else to make a change to the Time-out setting? Currently
it is set for 600 Seconds (10 Minutes), although I'll get the Time-Out error
Before 10 Minutes has passed.

Hope that I've been specific enough in my posting. I'll be monitoring this
issue if anyone needs any additional information.

Regards,
J. Falk

 

[Bob Barrows]

Like Roseann Rosannadanna said: "Never Mind!"

When researching Pass-Through Queries, I found the way to change the
Time-Out Properties on the given Query.

My other question is: As a relative newcomer to Access, would there
be an advantage to using a "Pass-Through Query"? Is the overall
concept that instead of sending MS-Access Commands to the SQL
Database, that instead I'm sending a direct SQL Query?

That's exactly the advantage.