Access hangs..?

[Kujen]

Hi all,

On my form I have a Combobox1_AfterUpdate() that shows records and with some summary fields :
...
Me.Txt1 = DSum"[Field1]", "MyTbl", "[Field2] ='" & Combobox1 & "'"
...

Combobox1 is populated with 'Field2' from 'MyTbl'.
'MyTbl' is a linked table from SQL-server.

For some reason Access takes up to 100 x the time or 'hangs' when I use this code.
But only If choose records from Combobox1, as a filter, that are updated by SQL-server.

I have no problem with other not updated records.
If I delete the above code then I can run the rest of the code.

I'm logged in as 'sa' (system.admin) on SQL-server.

The difference between not updated and updated is that
not updated looks like: 'Name '
and updated looks like: 'Name'

Not updated records have spaces after visible characters.

Any idea why records without spaces is a problem?

Kent J.

 

[boblarson]

Using Domain Aggregate function (DCount, DSum, etc.) on a SQL Server connection will cause the entire table data to need to be transferred across the network to the mdb file to do the aggregates.

Use a stored procedure with a function ON SQL Server itself for something of this nature. Your speed will improve drastically.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com