I recently built a program that connects to a remote SQL Server database. I discovered that the cursor type makes a huge difference in how fast the program runs. Initially, I used the adOpenStatic cursor. In some cases, the program took over 30 seconds to run seven simple queries and to render the results to some text boxes on an Access form. I changed the cursor type to adOpenForwardOnly, and the program took less than a second to run from then on. I have a "cleantext" function that scrubs the value entered by the user before it is used in the queries. Below is a simplified version of the code:
Dim cnx As ADODB.Connection
Set cnx = New ADODB.Connection
Dim serverName As String
Dim databaseName As String
Dim userId As String
Dim pwd As String
Dim strSQL as string
' DATABASE CONNECTION
serverName = "TheServerName"
databaseName = "TheDatabaseName"
userId = "TheUserId"
pwd = "ThePassword"
Set cnx = New ADODB.Connection
cnx.Open "Driver={SQL Server};Server=" & serverName & ";Database=" & databaseName & ";Uid=" & userId & ";Pwd=" & pwd & ";"
Dim rsADO As ADODB.Recordset
Set rsADO = New ADODB.Recordset
strSQL="SELECT a.field1, a.field2, a.field3" & _
" FROM someTable a" & _
" WHERE a.field1 = '" & cleantext(me.txtId.value) & "'"
rsADO.Open strSQL, cnx, adOpenForwardOnly
Do While Not rsADO.EOF
strDisplay = strDisplay & rsADO!field2 & " | " & rsADO!field3 & vbCrLf
rsADO.MoveNext
Loop
rsADO.Close
' Display the values in a scrollable text box
Me.txtHDHPosJobCodes.Value = strDisplay
strDisplay = ""
'CLOSE THE REMOTE DATABASE CONNECTION
Set rsADO = Nothing
cnx.Close
Set cnx = Nothing
