How to set the ODBCtimeout property in code

[Lisab]

Can I set the ODBCtimeout property in code (don't want to fool around with
the Registry settings)
-----------------------
I have code that loops through each table in my database and gets the number
of records in each table. It outputs the table name and number of records to
another table.

Some tables have over 1 million records and so I get an ODBC failed error
because the ODBC connection has timed out.

IS there a way to set the timeout property in my code? the following line
is where I get the timeout error

Set SourceRS = dbs.OpenRecordset(sqlStatement)

---------------------
Dim MyRecordCount As Double
With rsRecordCount
For Each tdf In dbs.TableDefs
'If the table name is a temporary or system table then ignore it
If Left(tdf.Name, 1) = "~" Or _
Left(tdf.Name, 4) = "MSYS" Or _
Left(tdf.Name, 3) = "tbl" Then
'~ indicates a temporary table
'MSYS indicates a system level table
'local table I don't want to include in the count
Else 'only process application Tables
'update tblTableRecordCounts with the tablename and record
count
sqlStatement = "SELECT Count(*) AS MyCount FROM " & tdf.Name
& ";"
tdf.ODBCTimeout.Value = 0
Set SourceRS = dbs.OpenRecordset(sqlStatement) '***ERROR
HERE**
With rsRecordCount
.AddNew
!TableName = tdf.Name
!TotalRecords = SourceRS!MyCount
.Update
End With
End If
Next tdf
End With
Set dbs = Nothing
rsRecordCount.Close
SourceRS.Close

 

[Alex Dybenko]

hi,
instead of using linked tables - suggest to use pass-through queries, and
there you can set odbc timeout property. Will also work faster

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[david]

That may not do what you want. ODBCtimeout may only apply to
ODBCdirect workspaces and to passthrough queries, that is, queries
that have a connection property that connects directly to the ODBC
database:

qdfStores.Connect = _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"



Apart from that, in my old version of Access, the default value of
ODBCtimeout
is given by the database property QueryTimeout, which is loaded from the
registry
value QueryTimeout.

You can change QueryTimeout without changing the registry:

CurrentDB.QueryTimeout = 5

If QueryTimeout has been renamed in a newer version of Access, to match the
query property, that would be

CurrentDB.ODBCtimeout = 5

(david)

 

[Lisab]

I cheated...
I created a query and set it's ODBCTimeout Property to 0
I reset the SQL for that query as I looped through the code
May not be ideal but it works
-------------
Set qDef = CurrentDb.QueryDefs("qryRecordCount")
..
..
..
qDef.SQL = "SELECT Count(*) AS MyCount FROM " & tdf.Name & ";"
Set SourceRS = qDef.OpenRecordset
With rsRecordCount
.AddNew
!TableName = tdf.Name
!TotalRecords = SourceRS!MyCount
.Update
End With
End If
Next tdf

 

[Lisab]

I tried using

dbs.QueryTimeout = 0

However, it did not work in my particular code. I think it would have
worked if I were creating a new Query to store in my queries collection.

 

[david]

I think that it would work if you were using an ODBCdirect
workspace, or a passthrough query (one with a connect property
pointing to the ODBC database).

(david)