baanitem is a combobox with an existing value
when the rowsource is changed, that value is no longer valid, so I
want to clear it

the baanItem.listIndex in the first example takes 2+ minutes and
returns -1

strSql = "SELECT t_item, t_dsca" & _
" FROM qryImcItem" & _
" WHERE " & strWhere & _
" ORDER BY t_item"
baanItem.rowSource strSql

If (Nz(baanItem, "") <> "") Then
debug.print time
If (baanItem.ListIndex < 1) Then
baanItem = ""
End If
debug.print time
End If


using a recordset, the response is instantaneous
strSql = "SELECT t_item, t_dsca" & _
" FROM qryImcItem" & _
" WHERE " & strWhere & _
" ORDER BY t_item"
baanItem.rowSource strSql

If (Nz(baanItem, "") <> "") Then
strSql = "SELECT t_item" & _
" FROM qryImcItem" & _
" WHERE " & strWhere & _
" AND t_item = '" & baanItem & "'"
debug.print time
Set rs = currentRS(strSql)
If (rs.EOF) Then
baanItem = ""
End If


debug.print time
End If

so I can only assume that the delay is due to a long list, yet the
rowsource contains
only 500 records - which doesn't seem large ??


and once the rowsource is set, if I paste a value into baanItem that
is valid, again a 2+ min delay to display
what was pasted

is this normal ?
what is a work around ? wait for x characters to be typed into
baanitem before setting rowSource ?

On Oct 4, 10:22*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
qryImcItem is a sql server view, linked as a linked-table

if I create a table, with the same fields as qryImcItem,
and I insert the 500+ records from sql server into that local table,
and my rowSource is based on the table
this statement is instantaneous
If (baanItem.ListIndex < 1) Then
baanItem = ""
End If

and selecting an item from the list by typing it in is also
instantaneous

which appears to indicate that even though the data for the control
should be loaded locally within the control by,
baanItem.rowSource = strSql

ms-access is still accessing data from the sql server, thus the slow
performance

is my accessment correct ?
if so, is there a way, to load the data into the control (ie memory)
and not have to get it from sql server ?

It sounds like the view contains many more records and your criteria is not
being applied on the SQL Server side, causing many more records to be
transferred to Access, where Access does the filtering.

Use SQL Profiler to see what statement Access is sending to SQL Server.

You say that qryImcItem is a linked SQL Server view. Is it the actual linked
view and you renamed it in Access? Or a query based on the view? It can make
a difference.

Here is an excellent article on Access/SQL Server applications
http://msdn.microsoft.com/en-us/library/bb188204.aspx

--

AG
Email: npATadhdataDOTcom


"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On Oct 4, 10:22 am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
qryImcItem is a sql server view, linked as a linked-table

if I create a table, with the same fields as qryImcItem,
and I insert the 500+ records from sql server into that local table,
and my rowSource is based on the table
this statement is instantaneous
If (baanItem.ListIndex < 1) Then
baanItem = ""
End If

and selecting an item from the list by typing it in is also
instantaneous

which appears to indicate that even though the data for the control
should be loaded locally within the control by,
baanItem.rowSource = strSql

ms-access is still accessing data from the sql server, thus the slow
performance

is my accessment correct ?
if so, is there a way, to load the data into the control (ie memory)
and not have to get it from sql server ?

On Oct 8, 6:33*am, "agiamb" <NOSPAMagi... (AT) newsgroup (DOT) nospam> wrote:
thanks for the link, it is very informative

qryImcItem is a linked sql server view that is used as a combobox
rowsource