VBA Access - Slow Performance issue with remote linked table

[comptips]

I use the code at the bottom of this post to populate 7 text boxes on a form. The arguments passed to the sub are the text box control and a string in the form of "7/2008". This function is called 7 times, once for each text box.

Data is always configured through linked tables. If I run this code with all local data, i.e., the code and the data both reside on the computer where it is run, performance is good. If I run the code with data on a server remotely, performance is noticeably worse, to the extent of about 10-15 seconds. (Interesting enough, if I migrate the data to a MySQL database and use ODBC drivers, performance is excellent.)

Any suggestions on ways to improve the performance using linked tables located on a server?

Thanks, Scott

+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*+*

Private Sub GetMonthCount(ctlTextBox As Control, strMY As String)

Dim iUseMonth As Integer
Dim iUseYear As Integer
Dim iUseDate As Integer
Dim iMonLen As Integer
Dim sMonthStart, sMonthEnd As String

Dim rstCount As Recordset
Dim sSQL As String

iUseYear = Right(strMY, 4)

' Parse the month number
If Len(strMY) = 6 Then
iMonLen = 1
Else
iMonLen = 2
End If
iUseMonth = Left(strMY, iMonLen)

' Get the last day of the month
Select Case iUseMonth
Case 1, 3, 5, 7, 8, 10, 12
iUseDate = 31
Case 4, 6, 9, 11
iUseDate = 30
Case 2
iUseDate = 28 - ((iUseYear Mod 4) = 0)
End Select

' Set the strings for first of month and end of month
sMonthStart = "#" & iUseMonth & "/1/" & iUseYear & "#"
sMonthEnd = "#" & iUseMonth & "/" & iUseDate & "/" & iUseYear & "#"

' Point to the control so its value can be changed.
ctlTextBox.SetFocus

' Build the SQL string
sSQL = "SELECT [Name] FROM [tblMyTable] "
sSQL = sSQL & "WHERE ((([Date]) BETWEEN " & sMonthStart & " AND " & sMonthEnd & ") AND "
sSQL = sSQL & " ((Active)=True) AND ((Monitoring) Is Not Null));"

' Create the recordset and get the count of records
Set rstCount = New ADODB.Recordset
Dim strConnect As String

With rstCount
.ActiveConnection = CurrentProject.Connection
.Open sSQL, , adOpenStatic
ctlTextBox.Text = .RecordCount
End With

set rstCount = Nothing

End Sub

 

[genomon]

Have you indexed/primary keyed the linked tables?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[comptips]

Yes, indexes are in place.

 

[joebb3]

Take a look at this thread. It helped me LOADS! I was having a speed issue over intranet connections as well.

http://www.tek-tips.com/viewthread.cfm?qid=1451661&page=1

Hope it helps!

Joe