Form Loading take too much time

[tani1978]

Hello Friends,
I am using a Access Frotnend and SQL Server backend application. With opening the database a Form open which has a query as date source and that query reurns 25000 records. Sometimes the application just stucks and sometimes it takes more than 20 minutes to load the form. On the other side in MDB file the same form takes about 6 seconds to load.
What should I do to that the form should open quicker?

 

[genomon]

Try creating a view on SQL Server that returns the same records, then link it into Access & use it as the form's recordsource....

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[druer]

Do you have an index on fields that you might have in your criteria?
Is your SQL Server extremely active for inserts/updates on the table you are reporting against? Could record locks be being held that force your query to wait? Have you tried changing your query to a pass thru query and purposely telling it not to do any locking so it won't hold up on records that are in the middle of being updated perhaps?
When you query against the Access Database itself the data is already on your machine, however, bringing 25,000 records across your network could be the problem depending on the speed/activity on your network. Do you have a network administrator with any monitoring software that could tell you if the network is being overran with data?

Assuming your database is indexed, and not to incredibly busy with updates/inserts that would cause blocking to occur, the problem is most likely your network. If you can't resolve the network issues then consider modifying your form in some way that would limit the number of records that are being reported back to the user, since an end user can't really process 25,000 anyway.

 

[tani1978]

I converted the query to View as suggested by one of you, the form is orking quite rapidly now but now my code is not working properly.
I am getting error on the following piece of my code. It is in the else statement. You cannot give a value to this object.

If Forms!Ottensen.[Messung_Ottensen.SickerwassermengeSiWa] >= Forms!Ottensen.[Grenzwerte.SickerwassermengeSiWa] Then Forms!Ottensen![Messung_Ottensen.SickerwassermengeSiWa].ForeColor = lngoliv Else Forms!Ottensen.[Messung_Ottensen.SickerwassermengeSiWa].ForeColor = lngschwarz
    If Forms!Ottensen.[Messung_Ottensen.SickerwassermengeSiWa] >= Forms!Ottensen.[Grenzwerte.SickerwassermengeSiWa] Then Forms!Ottensen.[Achtung] = "Sickerwassermenge ist überschritten" 
Else Forms!Ottensen.[Achtung] = " "
 Forms!Ottensen.Refresh

The following line is marked yellow while debugging.

Else Forms!Ottensen.[Achtung] = " "