Solved

access slow to open and close

Posted on 2001-06-11
22
239 Views
Last Modified: 2007-12-19
I have a main database on a server( 6 users in total).I have another db on 2 pc's running NT.I have linked a table on the workstations to a table on the server.The linked table has about 45,000 entries and one primary key.
On the workstation db's i created a couple of queries for printing various reports. There is only a small bit of code that creates and destroys a temp query to use as records for a listbox. The 2 workstations that run this db are getting incredibly slow to open and close, preventing me from running other apps.The other 4 computers seem unaffected. I compacted the main db and workstations db. The workstation db was 500k after compacting.I ran it once,closed it and it was ~900k.There was no data added to the linked table. Any suggestions?  Is there a more efficient way to get the data from the server without linking?
0
Comment
Question by:lostsock
  • 10
  • 8
  • 3
  • +1
22 Comments
 
LVL 1

Expert Comment

by:dkaygee
ID: 6178283
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.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6178337
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
0
 
LVL 57
ID: 6178737
The 4 that are not NT, what OS are they?  And where is the backend MDB being stored?

Jim.
0
 

Author Comment

by:lostsock
ID: 6179497
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.
0
 
LVL 57
ID: 6179519
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.

0
 
LVL 57
ID: 6179527
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.
0
 

Author Comment

by:lostsock
ID: 6179566

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?
0
 
LVL 57
ID: 6181181
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.
0
 

Author Comment

by:lostsock
ID: 6183973
^&*^^%!! 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?
0
 

Author Comment

by:lostsock
ID: 6184060
^&*^^%!! 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?
0
 
LVL 1

Expert Comment

by:dkaygee
ID: 6184121
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?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:lostsock
ID: 6184160
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.
0
 
LVL 1

Expert Comment

by:dkaygee
ID: 6184219
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.....
0
 
LVL 57
ID: 6185868
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.
0
 

Author Comment

by:lostsock
ID: 6202064
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.
0
 
LVL 57
ID: 6202082
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.
0
 

Author Comment

by:lostsock
ID: 6202465
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.
0
 

Author Comment

by:lostsock
ID: 6203700
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.
0
 
LVL 57
ID: 6203981
I don't understand your last post.
0
 

Author Comment

by:lostsock
ID: 6253473
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?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 total points
ID: 6253514
Glad to hear you found the problem.  I usually mention turning off virus scanners in regards to NT performance, but forgot it this time.  Even so, your problem was on the client side.  I'll keep that in mind for the future.

You can reduce points on the question at the bottom of the page.

Jim.
0
 

Author Comment

by:lostsock
ID: 6253609
Lots of good suggestions on db performance and other stuff
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now