access slow to open and close

Try creating a query on the server DB to grab the info you want. Then link the workstations db to the query, not the main table.

if you use a MS mdb as a back end, access will need to transfer the entire contents of each table across the network before performing a query.  this is why access on a network can be incredibly slow.  if you have 45k rows, it is possible that 45k rows are transferred from backend to frontend in order for the frontend to create and run the query... even if your query selects just one row.

you might want to investigate alternatives to storing your db in .mdb format...

other experts?

dovholuk

The 4 that are not NT, what OS are they?  And where is the backend MDB being stored?

Jim.

sorry for delay.All six run NT on the same network. I think the problem lies in the linked table,even though i thought ( mistakenly it looks like) that the link only pointed to the data and didn't transfer all of it across the network. When i right click to check properties it is also very slow. I'm not sure of the code to use to access a query that i'd create on the server db. Something like this???

On Form Load????

Dim db as Database
Dim rs as recordset?
Dim db_prefix As String
Dim db_password As String
db_password = "password"
db_prefix = ";pwd="
Set dbsorig = OpenDatabase("N:\Folder\maindb.mdb", False, False, db_prefix + db_password)
Set rs = dbsorig.OpenRecordset("qryNew", dbOpenDynaset)

I guess i need to do some homework.

Some explaining is in order here .The server db back end is Access,with C++ forms. It is a large commercial application. The cost to print simple labels on "special" paper was ridiculous,$200 a box for paper and $500 for the printer.I made another db to access the data after getting the password (thank you WDASM) and made a list box with the selected "products" to print individual or group labels on a refurbished dot matrix. Db works great when opened.
After i posted the question i deleted the linked table (jeez,even that took forever) and the db appeared to open o.k. The main db always worked fine before this.

OK couple of things:

1. For A2000, SR-1 addressed a bug in slow opening of objects

2. When the app opens, hold open a table (even while developing) and close when you exit.  NT has a poor performing lock manager.  Without a table held open, the backend MDB has to open/close repeatedly.

3. Consider turning off Opportunistic Locking on the NT Server.  Supposed to be a performance boost (allows client side caching of files), but I find otherwise.

4. Turn off the sub datasheet feature in A2000 if your not using it. It consumes a large amount of processing to get it done.

5. Make sure the app is compiled.

6.  Make sure all users have full read/write/delete priv for the directory where the MDB resides.  THis ensures proper processing of the LDB file.

  #1 and 2 are the ones that you'll see the most improvement with.

Jim.

And one other point:

"sorry for delay.All six run NT on the same network. I think the problem lies in the linked table,even
though i thought ( mistakenly it looks like) that the link only pointed to the data and didn't transfer
all of it across the network. When i right click to check properties it is also very slow. I'm not sure
of the code to use to access a query that i'd create on the server db. Something like this???"

  If your using Access with JET as a database engine, nothing is processed on the server side.  Everything gets done on the client.

Jim.

JDettman
oops, its A97. I read something somewhere to use Windows start/run and try msaccess/ decompile, open db and recompile but pc didn't know that function. Is it possible i could run a query that is part of main database and returns only 10 records,which is usually what i need?

The /decompile forces Access to recompile the VBA project.  That probably is not needed here but you can give it a whirl anyway as you may be carrying some garbage around.

1. Make a back up!

2. Open Access with:

  C:\.....\...\MSACCESS.EXE /DECOMPILE C:\....\MYMDB.MDB

3. Close the MDB and Access

4. Open Access and Compact the MDB

5. Open the MDB and a module, do a compile/save all

6. Close the MDB and compact.

  As for the rest, it's still NT.  Your using JET as the database engine and it's all client based.  Nothing is processed on the server.  With JET, the server is simply acting as a file sharer, nothing more.

  Keeping a table open in the backend will help the most.  The other thing you should check is that the client and the server are using the same default network protocol.

  and of course, proper indexing of the table is a must.

Jim.

^&*^^%!! this is so annoying.I tried the decompile steps.Each one took-no kidding-20 mineach to open and close.I left the damn thing at work and went home with it still trying to open. The feeling I'm getting here is that there is no other way to get that data other than linking to the table and sucking everything over on startup?? Do the queries I made that use that linked table also cause excessive overhead? Are more points needed for this question?

^&*^^%!! this is so annoying.I tried the decompile steps.Each one took-no kidding-20 mineach to open and close.I left the damn thing at work and went home with it still trying to open. The feeling I'm getting here is that there is no other way to get that data other than linking to the table and sucking everything over on startup?? Do the queries I made that use that linked table also cause excessive overhead? Are more points needed for this question?

If you run a copy of this linked server table on the local machine do you still have the excessive Open/Close time ? Maybe this is a network / server issue?

Yes I have copied it locally as well. The best solution so far is to not close my linked database. The only problem is that multiple users use each machine and have to log on each morning. They need the data ASAP as they are producing material for shipment and it needs to be properly labelled and the wait is just unacceptable for some. I am looking at network issues but it is out of my realm. I used to have a Crystal Reports setup for this, it still works but I did not have the time to learn it well enough. I could'nt manipulate it to print 1 label,loop through selected labels etc. Again I guess the question should be: "Can I create a query in code that will access the other db and use that as a record source for a list box" because the problem seems to lie in the size of the linked table. There is a plan to migrate back-end to MS SQL but that is not for another year.

You can definitely create a SQL statement in code that will act as record source for whatever. You can use DAO or ADO. Using these same features you can direct the processing to 'server-side' only if you wanted. Although, the queries in Access should work just as well. I just can't see any reason why opening the .mdb file itself takes so long unless there are some activities going on like Macros or what have you at start up.

 I work in an environment where we have about 30 users that use an Access DB front end that sits on an NT server along with the back end Access DB. We have over 50,000 records in a single table. There are MANY other tables. Queries can be slow at times, but never more than  30 seconds a pop. We really have nothing extraordinary going on in our DB.

If the users have a front end DB on thier PC and only 2 workstations are affected, maybe the front ends need have a "repair" run or maybe there are size issues with the hard drives. Out of ideas.....

dkaygee made a great suggestion in trying it out on a local setup.  But even without that, you definitly have some type of network/server issue.  Access is simply not that slow on a 45,000 record table.

I have several apps that have tables over half a million records and even on an add-hoc query with criteria not on an index, the max time is 2-3 minutes.

Jim.

I have to rely on IT services at work to investigate the network end, they will be doing very soon. I think i'll also reinstall access. If I was looking for code to create a query to get records from a separate db,should  i post another question.

I would not suggest reinstalling Access.  When trying to figure out a problem, you should only change one variable at a time.

 Takes a lot longer this way, but without that, you'll never know exactly what the fix is.

 I also don't think it will have any bearing on anything.

Jim.

thanks.I'm assuming queies i made are not run until i explicitly do so? I have 8 or 9 for reports that i could probably consolidate into 1 if i look at 'em closely enough.

thanks.I'm assuming queies i made are not run until i explicitly do so? I have 8 or 9 for reports that i could probably consolidate into 1 if i look at 'em closely enough.

I don't understand your last post.

Aaaaaaggggggghhhhh! Finally. This goes to show how bad communication can be in large firms sometimes.I was away from work when this problem first occured. Apparently a user got an infected email (Bloodhound i think). She notified IT Services (I do not work in that Dept).They reinstalled an updated version of Command anti-virus. I had to go back and exclude mdb files from being scanned in background and it worked.Database opens normally now.No time to make coffee while waiting. I'm not sure how wise that is to exclude mdbs at this point but hey it works.
I'll distribute some points for all those who have commented.How can I keep this question but assign fewer points to this so it doesn't ding someone for too many points as a PAQ?

Lots of good suggestions on db performance and other stuff