OpenRecordset and ODBC linked tables

abenitez77

Registered User.
Local time
Today, 12:25
Joined
Apr 29, 2010
Messages
141
I am using the function below to check if my Linked ODBC tables are good. It works, but when it gets to tables that have a lot of columns, it takes about 5 seconds to return. I am checking about 56 tables and it could take up to 10 - 15 seconds. I tried using "Select 1 from tablename" and also tried "Select top 1 fieldname from tablename" as the source in Openrecordset() but it took the same amount of time. How could I speed this up ?

Function IsODBCConnected(TableName As String) As Boolean
If Not TableExists(TableName) Then Exit Function

Dim rst As DAO.Recordset

On Error Resume Next
Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
IsODBCConnected = (err.Number <> 3151)
End Function
 
Try adding

WHERE 1=0
 
Keep the first recordset open to maintain the connection while you test the others.
 
Keep the first recordset open to maintain the connection while you test the others.
Here is an example on what Galaxiom is trying tell you

psudocode:

Code:
[COLOR=red]Dim blnConnected As Boolean
Dim dbConnection As DAO.Recordset
[/COLOR]
Sub MainLink()
  'Loop ODBCTables
 ...your loopcode goes here
 ' end loop
blnConnected = False
dbConnection = Nothing
End Sub
  
Function IsODBCConnected(TableName As String) As Boolean

If Not TableExists(TableName) Then Exit Function
Dim rst As DAO.Recordset
On Error Resume Next
Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)

IsODBCConnected = (err.Number <> 3151)
 
 [COLOR=red]'Setup a persistant connection to ODBC  
If blnConnected = False Then
   Set dbConnection = rst
   blnConnected = True
End if 
[/COLOR]
 End Function
 
I'm going to try this later on today. I'll let you know if it helped.
 
Actually, there are already connections to that server/db before I do the test, so I don't think adding another connection will change anything. The issue is with tables with large number of columns or with columns that have varchar greater than 254. They are converted to memo in ms access when attached.
 
Did you try adding a WHERE clause that prevents any records from being returned?
 
Yes, I even tried opening an empty table with over 50 columns and it took longet than a table with half the fields that has records.
 

Users who are viewing this thread

Back
Top Bottom