Problem with query causing Access to go to white screen

[Peter S.]

Any ideas on how to remedy a situation where when we run Access queries the
screen goes white then I hear a beep and the query status bar disappears. I
have seen this from time to time. I noticed if I copy the Access DB to the
SQL Server I am running the query off of and run it from there (eliminating
the network) the problem goes away. Any ideas on what causes that?

 

[Klatuu]

Where is your Front End located? If it is on the server, then that will
certainly decrease performance.
When you say "copy the Access DB to the SQL Server I am running the query off"
Do you mean using an SQL Server database instead of an mdb?
That statement is not clear.

 

[Peter S.]

My access db is used to query a SQL Server DB. Access is located on my
desktop machine. This is when the problem (infrequently) occurs. As an
experiment I put a copy of my Access DB on the SQL Server I was querying and
ran it from there and the problem disappeared. It's an infrequent problem and
doesn't happen all that often. I am trying to determine what could cause
Access to do this (screen goes white, I hear a beep and the status bar
disappears) is so I can understand how we can remedy the situation if it
happens again. We are small group and know what each other is doing so it
shouldn't be a load issue.

 

[Klatuu]

The results you are getting by putting the front end on the server is very
suprising and I have no idea why, but it is not a good idea to run a front
end from the network. That actually doubles the network traffic because the
app is still running on the client computer.

I would look at the structure of the data in the SQL database. I have never
seen it go white screen, but I did see one occassion where it took hours to
run a delete query. Turns out there was a 5 field primary key index with
various data types. As the delete was occuring, the index had to be rebuilt.
Chaning to an indentify primary key and removing the index made the
difference. My point is, your solution is masking the problem, not curing it.

 

[Peter S.]

It's not a solution it was an effort to understand the problem. What causes
Access not to refresh/repaint and go white? Can any Microsoft engineer's
speak to this? I am guessing it is waiting on some ODBC handshake (perhaps),
that is thwarting the internal refresh/repaint routine? After the beep the
screen repaints but the status bar is missing and I still see CPU usage. What
specifically could cause such behavior?

 

[Klatuu]

I can't tell you the specifics. As I stated previously, I would suspect
there is something about the way the table is defined in SQL Server. Also,
when this does happen, what else is the database doing? I would involve the
SQL Server DBA and your network engineers to see if they have any ideas.

Another alternative would be to use ADO to kick off a stored procedure.
That will leave Access and Jet completely out of the picture.