TransferSpreadsheet method question

[Mike]

I have an Access 2003 database which uses linked SQL-server tables as and
ODBC data source.
Sometimes I export data from Access queries to Excel like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryDataForExport", "D:\AccessExport.xls", Yes

It works fine for 1 000-2 000 records. But if the query returns 120 000
records I get the following error:

Connection failed:
SQLState: '01000'
SQL Server Error: 233
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.

It looks like some timeout occurs.
Is there any idea why it happens?

P.S. I cannot use DoCmd.OutputTo method because I get unreadable characters
in Excel file, although it returns 120 000 records without any problem.

 

[Mike]

Please read "12 000 records" instead "120 000 records"

 

[Jul B.]

Hey Mike - I'm not an expert by any means but this happened to me all of the
time until I finally figured it out. Most of my tables are SQL tables and
I routinely pull bazillions of records. All you need to do is increase the
ODBC timeout max. To do this, when you are in design mode of your query,
right mouse click and choose Properties. About 1/2 way down, there is an
ODBC timeout selection - I think the default is 150 (seconds) - I usually
increase mine to 600 and am good to go. Try that - hope this helps!

Please read "12 000 records" instead "120 000 records"

I have an Access 2003 database which uses linked SQL-server tables as and
ODBC data source.
Sometimes I export data from Access queries to Excel like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryDataForExport", "D:\AccessExport.xls", Yes

It works fine for 1 000-2 000 records. But if the query returns 120 000
records I get the following error:

Connection failed:
SQLState: '01000'
SQL Server Error: 233
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.

It looks like some timeout occurs.
Is there any idea why it happens?

P.S. I cannot use DoCmd.OutputTo method because I get unreadable characters
in Excel file, although it returns 120 000 records without any problem.