Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blame the Network? (WIN 2000 Acc 97)

    Here's the scenario:
    I have 50 databases on a network.
    Most work fine.
    A couple of them, (FE/BE) when I try to open a query in Design View, it could take 45 seconds to open.
    I see no pattern as far as the dbs are concerned. Some are large (15mb) most are small (<2mb).
    The queries are a variety of Select , Append etc. Every so often I check Task Manager and the db
    is Not Responding. Then, 10 seconds later, the query opens in Design View.

    In addition, when I click to open a database (FE or BE doesn't matter) when the log on box pops up,
    there is a delay. After the password is entered and I click OK, again, a good 5-6 second delay
    in opening the db.

    All databases have been compacted. I have no complicated queries.

    I feel that I have exhausted the possibilities as far as Access goes. I believe it is
    either my PC, or the Network. Of course the network guys always say it's Access <img src=/S/liar.gif border=0 alt=liar width=25 height=22>

    Can anyone at least give me some questions to have them look at?

    I am on Windows 2000 SP3
    Access 97 SR2
    They just defragged me. <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16>

    Please assist !
    Thank you,

    Michael

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    I presume you mean the front-end databases are also on the server. If so, make a copy on your local hard drive in some convient folder, link to the back-end tables and check to see how long it takes to open. Then go show your network guys the response time. In the same vein, is your security file (system.mdw or whatever you've named it) also on the server? That would explain some of the slow performance in logins.

    Bottom line - you are usually much better off putting the front-end and security file on the local hard drive. You can find a tutorial that goes into more detail on our web site, as well as a tool we have that helps keep workstation files current. Post back if you have more questions.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Hi Wendell!

    Front Ends reside on local computers. The 'bad' queries open (in design view) slowly no matter where
    they are. The funny thing is, everything within the dbs work fine. All code works OK, all command buttons
    open whatever they're supposed to open (quickly!) etc. Actually, most queries do too.

    Security file is on server...but... the logging in issue and the 'Click OK' to open db issue, is recent.
    No new users. No new Front Ends.

    Just frustrating.

    Thanks!!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Well, it was a guess that was off target! Have you tried importing all the objects in your front-end into an empty Access97 database and testing that to see what performance looks like? Also, what about other users - do they see the same behavior? (I presume the back-ends are Access97 as well?) How long have you been running Win 2000 - we found performance on 97 was not as good with early versions of Win2000, but supposedly that was fixed in the first SP?? If you were running Access 2000 I would suggest a decompile, but I don't know how effective it is with 97. Another possibility is to create an MDE version of your front-end and see if that fixes it. Lots of ideas - hopefully one of them will improve things.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Ya know, I've been reading these boards for a couple of years now, and know that I
    should give as much detail as possible. But I still usually leave some piece to the puzzle
    out!!
    I have done all of the usual - import into a clean db.
    Compact
    Taken everything out of the db except for the 1 table and the 1 query.
    I have had Win2000 for months - with Acc 97 on it from the beginning.
    This seems to have started when I got a different computer.
    I made sure they copied all of the SP & SR's that were on my other machine, but I
    have a feeling, they missed something.

    So, I will have them put everything on one of their computers and see what happens.
    (My guess is it will work just fine!)
    Then I can ask the obvious (but eluding question for Network folks) "So - what do you
    have on your machine that I don't?

    Michael

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Go for it - I constantly remind myself how glad I am that I'm not the Network Guys, though every once in a while I get sucked into it.
    Wendell

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    If it is specific queries in specific databases that are slow, then I'd tend to discount the network being the problem. Are those queries always slow or just sometimes? You may not have a complicated query, but if it still may have to look thru too many records in the database to process. You might want to look at the selection criteria and any ORDER BY clause to see if perhaps another index on a table might help.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Are these queries slow on all the users PCs, or is it just your PC where these queries are slow?

    If it is just slow on your PC that means it's not indexes (or lack of them) that are slowing you down.

    Pat

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Hey guys - just got in - woulda replied sooner !

    It's just a couple of queries and everything worked fine until they changed computers on me.

    The point is, the queries run OK - proper results etc.
    It's when I try to open them in Design View, that there is hesitation.

    The reason I believe it's 'not my fault' (code, indexes, etc) is I also have the following issue:
    When I click on ANY Access database -FE or BE or shortcut, or directly, the Access SplashScreen/LogOn box
    takes almost 10 seconds to appear. Then when I click OK on the logon box, again, it hesitates and
    takes almost 6-7 seconds to open.

    I know 6,7, 10 seconds isn't a lot of time - but when it used to open 'Upon Click' in 1 second or less,
    there is a problem. And I believe it's Access or even all of Office (or heaven forbid, my 'newer, faster, more
    memory, computer they gave me <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    They're coming by today to see if they can figure it out. I'll keep y'all posted!

    Thanks a lot guys (and gals!!).

    Michael

  10. #10
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Okay Michael, you know what this sounds like to me? It sounds like a constant 're-link' problem. Here's what I mean. When you have a FE BE scenario, when you need a 'resource' that resides on the Backend, the FE has to 'connect' to the back end, EVERYTIME. The connection process is the real time delay. (Windows 2000 servers seem to add to this....but you get this issue no matter what the server is). For example, we have an Intranet, and a Internet IIS server. I have an Intranet site that uses a database on our Internet server. Initially connecting to that database takes 2 or 3 seconds. Once the connection is made, the data pulls are pretty much instantaneous. So, to prevent every page from getting the 'connection' delay, I load an object that 'connects' to the database, and I keep that connection permanent. Then every process that needs data from the database uses that objects connection object. (I am using ADO and VB ActiveX .dll's).

    However, long before I got into ASP and web development, I had similar problems in straight Access Front Ends. In Access 97, one of the 'tips' it gives for split databases is to make a 'link' form and table. Here's how. Put a table in your backend, call it tblLink. Just give it one field. No data is necessary. Then go into your Front End, and link that table from the backend. (If you have multiple backends, add multiple linked tables.

    Then just use the autoform button on that linked table 'tblLink'. Have your startup code (or AutoExec macro) load that form 'hidden'. That will keep the connection to the back end 'live' the entire time someone has a copy of the FE open.

    The delay you are getting (probably) on opening the query in design view, is that Access has to 'connect' and access the table defs in the BackEnd, to get the list of fields. It may be connecting and reconnecting for each table in the query. If that is the case, the 'link' process described above should fix it.

  11. #11
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Hey Drew!

    Now I can't wait to get into work tomorrow to try it!!
    It sounds like this could work.

    Thanks !!
    Michael

  12. #12
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Well keep us posted!

    By the way, I wouldn't let the network guys off the hook. If you switched machines, and then this suddenly occurred, they may need to check the driver on your NIC, and the connection between you and the switch you are connected on.

  13. #13
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Good morning!

    Well, of course the hidden form/linked table idea is working!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I can't believe how fast everything works now !!

    Thank you SO much for all of your ideas and help.

    Michael

    I will be checking with the network folks regarding the 10 second delay of the splash screen
    and the delay after clicking OK. Gotta try to get them for something!! I'll have them check
    the driver on my NIC, and the connection between me and the switch I'm connected on.
    Boy - I'll sound so smart!! <img src=/S/read.gif border=0 alt=read width=19 height=33>

  14. #14
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    Glad to help out.

    What delay after the splash screen? That could be due to the 'link' being created.

  15. #15
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blame the Network? (WIN 2000 Acc 97)

    When I click on ANYthing Access, the splash screen opens, then it takes about 10 seconds for the
    LogIn box to appear. After entering the password and clicking "OK", it takes another 6 seconds for
    the db to open.
    This is strictly on my computer, which leads to me to believe it is the computer, not Access itself.

    The 'boys' will here soon. I'll let you know!

    Thank you !
    Michael

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •