Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    78

    Unanswered: ADO doubts in BE/FE environment

    Hi All.
    I have an Access FE/BE environment in my office. Currently its a "normal" structure, without any sort of ADO connections. I am planning to move to ADO to improve the performance over the network and prevent unnecessary loading on the BE. However, I did some research on ADO (as I am pretty much new to it and have not had any exposure to it yet), and need some clarifications on that.

    1. The main benefit of using ADO is that it prevents unnecessary connection to backend and only connects when the form or report is opened up. Fair enough. However, I have also read that forms and reports can be left empty (i.e. no recordsource), and only filled up when they are loaded. Once they are closed, again they can be made empty. My question is which is the right way to go? Leaving the form or report's source empty until they are not loaded sounds to be much simpler rather than each time making and opening an ADO connection and closing them. I think I am missing some vital aspect, so I need some proper clarifications to clear my doubts from all the Access Gurus out there. I would really appreciate any guidance on the above.

    Thanks to all in advacne for all your time and efforts.

    Regards:
    Prathmesh

    P.S.: I have posted this question specifically from BE/FE point of view.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Quote Originally Posted by Prathmesh
    1. The main benefit of using ADO is that it prevents unnecessary connection to backend and only connects when the form or report is opened up.
    Actually main benefit of ADO is it is new and will be supported by MS for awhile, and also it would be easier for you to work with ADO.NET in the future. Since you will be familiar with most of the functions.

    Quote Originally Posted by Prathmesh
    Fair enough. However, I have also read that forms and reports can be left empty (i.e. no recordsource), and only filled up when they are loaded. Once they are closed, again they can be made empty.
    I believe it is not the form's or report's recordsource, it is rather subform's or subreport's recordsource. FE/BE databases with Access is known for their slow connection speed. but once the connection is made speed is not a problem. so with FE/BE databases, if you have more than one subform in your main form, you don't want to load all subform's data until user needs them. best practice about that is placing a tab control and put subforms into each page, then set a subform's recordsource when a user want to see it.

    Quote Originally Posted by Prathmesh
    My question is which is the right way to go? Leaving the form or report's source empty until they are not loaded sounds to be much simpler rather than each time making and opening an ADO connection and closing them. I think I am missing some vital aspect, so I need some proper clarifications to clear my doubts from all the Access Gurus out there. I would really appreciate any guidance on the above.

    P.S.: I have posted this question specifically from BE/FE point of view.
    The point is once ADO opens the connection, it is no more slow after that. So if you have a form that lags while opening, you can make it invisible instead closing. this way you can keep form's connection open and you can make it visible back when you need it again. it will lag once when you open the form first, then it will work fast without problem.

    Another speed tip with FE/BE databases is keeping nonchanging tables in FE. So when use them in your form's ADO won't lag at all since they are in FE file.
    ghozy.

  3. #3
    Join Date
    Oct 2003
    Posts
    78
    Hi Ghozy,
    Thanks for that clarification. It was a great help. Well, as such, none of my forms have multiple subforms, so that reason for ADO implementation does not hold good. Actually the main reason that I wanted to do this is, I have read that Access gets slower in FE/BE enviroment with more number of connections, so I wanted to limit the connection to necessary time period only. So if somebody is using a report or doing some updates via Form, then only the connection should be made. If no activity is going on, and the switchboard is just lying there, there should not be any connection as such. As I said, this was to decrease the connection overhead on the BE. Is there any alternative way to go about this?

    Once again, tks to all for their suggestions. Cheers.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    as soon as you close any form, you loose connection for that record source. reason for slowing down is ado tries the find file and have find handle each time access open a bound form. it is not ADO is slow. it is actually Access doing that not ADO. ADO is not slow, Access closes unused connections. then later tries to reopen them when you open the form, which causes slowing down. connection operation is costly and ADO is not slow.
    ghozy.

Posting Permissions

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