In the query design if you view the query properties, there is an ODBC TimeOut property (which is usually 0). You can also set the RecordSet Type of the query to Snapshot (which helps if you don't need to update the records in that query.)
If you're having a timeout problem, there can be different reasons for this. There could be something it's trying to evaluate in the query on the SQL tables/values (an overly complex equation or expression) and it just can't do it (how big is your recordset?) (having a lot of dlookups in the query can be query killers.) If it's form related, having multiple comboboxes on the form can be killers. Or perhaps there is a bad value in one of the records which it's trying to evaluate and causing a timeout. Have you tried setting parameters/criteria to grab certain records in the query and see if you can narrow it down where it timesout only on certain records, continually narrowing it down until you can find if it's a specific record causing the problem.
Can you open the table ok? Can you open another table ok? Can you design a query for another table and is it ok? In your query causing a timeout, how many relational tables do you have linked? Linking tables incorrectly can cause a timeout. If you can't open any SQL Server linked tables directly (even small size ones), are you sure your loginID has permissions in SQL Server security? (usually you'd get a permissions error for this though.)
I've worked with linked SQL tables in the millions size and used to only get timeout errors when there were problems with the network being slow (or having multiple IIF statments in expressions) on the large table.
If your network is setup with daisy chained hubs verses a switchboard or there are slow network cards in the PC's, this can cause timeout errors.
Again, how big is the record size for the SQL tables. Do you have an adequate log file size (SQL Server) and are you allowing it to grow? INDEXING fields on SQL Server tables helps tremendously!! Especially ones which you are sorting on!! Have you refreshed the SQL Server linked tables (if you made any changes to the tables on SQL Server?) How fast and how much memory is on your SQL Server box?
If you get a chance, show us the SQL select statement for your query (is it an update/select/delete/crosstab/etc..type query) and tell us how big the table size is and possibly some info on your network setup and SQL Server box info.
Before you go the method of writing out some code to establish a connection, you may want to first find out the possible cause for your query timing out. There's nothing like writing a bunch of connection code only to get the same results because it's networked related.
Last edited by pkstormy; 10-04-07 at 00:23.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)