Refreshlink for SQL too slow

[Neal]

I am attempting to refresh links to a backend SQL database. Would like to
have capability to test connection and also be able to switch backend
databases when necessary. When experimenting with this, determined that
refreshing the links is painfully slow (seems to vary from 1 - 60 seconds per
table depending on size of table), whereas just deleting the table and
recreating is much faster (maybe 1-2 seconds per table). Would prefer to
refreshlink over delete/create. Per previous posts on this, I tried to keep
backend open via dbODBC in code below (did not make a difference; maybe
because backend is SQL and not mdb). Any thoughts on why performance is so
poor?

Public Sub Refresher()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim strServer As String
Dim strDatabase As String
Dim intCounter as integer
Dim dbODBC As Database

strServer = "MyServerName"
strDatabase = "MyDbName"

strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";INTEGRATED SECURITY=SSPI"

Set db = CurrentDb()
Set dbODBC = OpenDatabase("", False, False, strConnect)

For intCounter = 0 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(intCounter)
If tdf.SourceTableName <> "" Then
Debug.Print "Refresh table: " & tdf.Name
tdf.Connect = dbODBC.Connect
tdf.RefreshLink
Debug.Print "Done table: " & tdf.Name
End If
Next intCounter

Set tdf = Nothing
Set db = Nothing
Set dbODBC = Nothing

End Sub

 

[Klatuu]

Speed issues:
Network Bandwidth
Network Activity
Number of tables
Configuration of SQL Server
It goes on.
It should not be that slow.

 

[Neal]

The tests I am running now are against SQL Server 2005 express edition
installed on my local machine, using a copy of the production database. So
at least that rules out network activity and bandwidth. Once on-site I will
be able to test in production environment using SQL standard on a separate
server. There are 800 tables in the backend datbase; I am only currently
linking with 26 of them in the frontend.

It is helpful to know that you are confirming the slow speed is abnormal.
The thing that's go me puzzled is why delete/create is fast but refresh is
slow.

 

[Klatuu]

I really don't know
I would be curious to see what the performance differences between the
Express and Standard editions.

 

[Maurice]

It could be because the indexes have to be rebuild...

 

[Marshall Barton]

The tests I am running now are against SQL Server 2005 express edition
installed on my local machine, using a copy of the production database. So
at least that rules out network activity and bandwidth. Once on-site I will
be able to test in production environment using SQL standard on a separate
server. There are 800 tables in the backend datbase; I am only currently
linking with 26 of them in the frontend.

It is helpful to know that you are confirming the slow speed is abnormal.
The thing that's go me puzzled is why delete/create is fast but refresh is
slow.

This page might provide some insight:
http://www.granite.ab.ca/access/splitapp/details.htm