DB slow to open

[njb]

Okay. I have a weird problem. I have an access db out on our share drive. It is very slow to bring up the signon and open the db. I also have a copy of that db that I use for development. It is located on the same share drive under a different folder. It opens very quickly. So what gives? I have compressed the db - didn't help. The shortcut properties show the same path directions - just point to a different folder. All references look to be the same. I am stumped and the users are started to complain. (And my users are other IT techs and they can really complain! Peer pressure!!!). So, any ideas?

 

[Bastien]

How many users? I believe that access has trouble with more than a few simultaneous users logged in and inputting at one time. A better solution might be to combine access with asp and let the web server do the work. You can then control how long the users have the DB open for, should be long enough to write to the DB or read from the Db, several miliseconds. this allows more users to share the DB at one time.

hth Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[OhioSteve]

The core problem is that there are MANY potential variables, and its hard to isolate just one. For example, they could have old, feeble PCs. Or, perhaps the "network" is really not just one network. Your way of getting to the .mdb may not be the same as the users', even though it looks similar. Or perhaps the problem is psychological. Maybe these users are just very impatient...users' expectations do vary widely. I would try to rule out potential causes. For example, find out if they have the same PCs as you, find out more about the network, etc. Oh, and try to get the users to be specific. Is the whole app slow, or just one form? If it is one form, how does that form differ from the rest of the app?

 

[njb]

Bastien - It's slow to bring up even when there is no one signed on. Just me. So it is not the number of users.

OhioSteve - I agree - this is hard to figure out. You comments about expectations and different PCs are valid. But in this case, that isn't the problem. The production system is slow to start up on my own PC and my test copy is fast to open on my PC. Something else is going on.

Should point out that it is just the startup that is slow. It's fine after that.

And I create my test copy of the system by doing a copy and paste of the entire folder to another folder and work from there. So all options would be identical. This is very weird.

 

[OhioSteve]

Well, the plot thickens. You can sit at your PC, open the original, and it responds quickly. Then you can sit at the VERY SAME PC, open the copy, and its slow. Both apps are fast AFTER they open. If the above is true, you can really narrow down the possible causes. Does this app use a bunch of linked tables outside of the .mdb? Or do other things link to it? Did you hardcode address references in this thing? Those are the kinds of questions you need to start asking...because for some reason this thing freaks out when you move it. Somehow, the app (the copy) is saying "where am i?" when it opens.

 

[njb]

Oops you have it backwards. The test copy is fast to startup, the production copy is slow to startup. Which makes it even wierder. I agree it does seem to be searching for something but what? No linked tables, references the same, what else is there to look at? I did not split the database so it is all in one. With the exception of the slow startup, the speed is fine for our purposes. (This is our department's internal project tracking, time entry, and billing system).

There is only one reference to a hard coded file and that is in a form that exports to an Excel file. It's only run once a month and the file is located in the same folder as the db.

I haven't asked them yet but would the server guys be able to put some kind of trace on it?

Thanks for your thoughts.

 

[OhioSteve]

We are both saying the same thing: your copy is fine, but the users' copy is slow. I assume that you are working on your DB, then copying it over to the other folder. Somehow, the production copy is confused by the new address. So there must be SOMETHING that it is doing, or trying to do, that makes it look outside of itself. What about security? Does this thing use the Access security system? I am not familiar with it, but I seem to remember that it creates a little file outside of your .mdb. And, one would use the security system at startup.

 

[madmatts]

njb and OhioSteve,
Hi...It is funny that I stumbled across this message. I am having very similar problems and need help. From what njb said, he is NOT using linked tables, but I AM using linked tables. My supervisor can have the program open quickly at his desktop, but it responds much slower at another desktop. The computers Mhz are comparable (over 500). The desktops are running Win98 SE, the database lives on one of our NT4 servers, and our users are authnicated thru our Win2000 server...complicated system, huh?

What does hardcoding address references do? Where can I look to find out about that, if it will help me?

njb - is it just me or does Access db slowdown seem to be a common problem?

Thanks!

 

[njb]

You are right - we are saying the same thing only differently. I guess I look at it in reverse because I copy the production system to separate folder, make my design changes and then signon to the production db and import the changes from the test db into the production db. But the way you describe it is right too.

Yes, I have Access security turned on. But there is an MDW file (that's the security file) in each of the folders and I have the shortcuts pointing the correct files and folders. That was one of the items I checked. It is curious that the signon is slow to come up too and I have wondered if that could be the problem but how?

madmatts - I haven't had problems with Access being slow except for this problem but I haven't worked on any super large access database either.

 

[OhioSteve]

Maddmatts, don't assume that your problem is similar to njb's. Look at the start of our thread. I mentioned that slow performance can be caused by many things. Slow performance is kind of like a headache in medicine- its a ubiquitous symptom. Njb says that everything is fine until he copies the .mdb to a different folder. Then when its address changes the problems start. That makes us think that it is somehow confused by the change of address.


Oh, you asked specifically about linked tables. Well, Njb doesn't have any but if he did it would be a problem. When you draw the relationships with those tables, the system assumes that neither the .mdb nor the target file will move. If they DO move, you will have problems. The best thing to do is cut the links after you copy the .mdb to the production area. Then redraw them by hand so you know that everything is cool.

So, Njb, you are using .mdw files. I know very little about them. However, I do know that they are physically outside of the .mdb. It sounds like you have created a new one just for the production copy. What does one do to switch the .mdw that the .mdb refers to? I do not know the answer. How do we know that the little binary brain is not thinking "which .mdw should I use?"

 

[OhioSteve]

Maddmatts, don’t assume that your problem is similar to Njb’s. Slow performance is a common symptom that has many causes. We are only focusing on address issues because we have pretty much ruled out other stuff. Njb says that everything is fine until he copies the .mdb to a different folder. That makes us think that the application is looking for something external to itself…something that it can’t find from its new location.

Oh, you asked specifically about linked tables. Well, Njb doesn’t have any, but if he did it would be a concern. In my experience, those relationships don’t update reliably. So if you move either the .mdb or the target file, you have trouble. It is a common practice to work on an .mdb in a secluded area, then copy it to the production area where the users can reach it. My practice has been to sever the relationships with external data sources after I copy over the .mdb. Then I redraw them by hand. That way, I know that everything is cool.

So you are using .mdw files, Njb. I know almost nothing about them. It sounds like you have created a new one in the production area for the .mdb. My question would be: are we CERTAIN that the .mdb IS using the nearby .mdw, or is it on a hunting expedition? I do not know how to answer this question. However, it does seem suspicious because you would be using the .mdw at startup (and that is when you say that the problem occurs).

 

[OhioSteve]

Hey, do either of you guys know much about modules? I hate to admit this, but I have avoided using them for years. I do everything with the regular interface, or with macros, or with code snippets in objects’ properties. Now someone has given me a complete, stand-alone VB program that I need to use. I created a module and copied in the code. But I could not get the run button (“!”) to be active. It seems to always be gray. And how do I run a module from a macro- what is the appropriate action and arguments? His procedure does have a name with the format xxx(). So how do call it?

 

[madmatts]

OhioSteve,
You beat me to the punch. I see you got your situation resolved. I was going to tell you that I don't know too much about modules either, but I do know about the RunCode command and then inputting your function name. I'm glad you got it working.

 

[njb]

OhioSteve - another way to run is a module is to open in in design mode (look at the code). Highlight the name of the function or the subroutine. Click the run (arrow) button. It will run. I sometimes find it is easier to right code for a quick fix than put together screens and queries. How that helps.