Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005

    Unanswered: Form Load - How to Code a Connection String To SQL Server 2000

    Hi everyone I am new to access and I was looking for some help with creating a connection string in my access application to connect to a SQL Server 2000 database. The application that I created currently connects to my SQL Server database using a linked table which connects through an ODBC connection that I created on my PC (administrative tools > Data Sources). The problem is that the query executed by my application times out after 1 minute. I have been looking around and it looks like I can control the timeout setting through a connection string but I am not familiar with doing this in my application. My thoughts are to add the connection string to the form load event of my application but I need help with the syntax. See below for additional information.

    database - SQL Server 2000
    users are on a domain and authenticated on the SQL Server using a trusted connection so a username and password is not required
    database server - SQL1
    Database name - TRANSPORT
    ODBC connection on my PC - TRANSPORT

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    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-03-07 at 23:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Provided Answers: 10
    Home | Blog

  4. #4
    Join Date
    Nov 2005
    Thanks for the excellent suggestions pkstormy. Your first suggestion of changing the ODBC timeout property as well as changing the recordset type of the query to Snapshot did the trick. My report which previously took almost a minute to open now opens in seconds.

    Thank you for the connection string link as well georgev. That site will definitely come in handy one day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts