How to simulate an ODBC time-out error?

Question

I am testing the error-handling of an Access-VBA controlled process:

1. A script in an Access 'controller' DB starts.
2. The script starts a macro in a 2nd Access file (the 'database').
3. The macro in the 'database' file runs a bunch of maketable queries.
4. These queries pull from tables linked to an ODBC source (SQL-Server actually).

When this process runs in the early morning hours, sometimes the queries time out. Today, I've updated the error-handling in the controller script, so I want to simulate a time-out error.

I've looked at the ODBC administrator and Advanced options in MS Access, but I'm not finding what I need. Ideas?

Answer 1

Open your macro in design view. Under the View menu, select Properties. It should be a Timeout property, set it to a short value and test.

Answer 2

Within your SQL Queries add the following statement - it should cause a timeout.

--waits for 5 mins
    WaitFor Delay '00:05'

Or if you don't want to amend existing queries you can run this over one of the tables that the macro queries. This will lock the table for 3 mins

begin transaction

Select *
From MyTable with (TABLOCKX)
--wait for 3 min
WaitFor Delay '00:03'

rollback transaction

Answer 3

If your queries are modifications, you can add a trigger which invokes WAITFOR. Described here.

Answer 4

re: sometimes the queries time out.

Make sure your query property for ODBC timeout is set to zero so it doesn't generate an error but continues running.