Post reply

Access Projects?

  • July 13, 2005 at 6:29 am

    #573837

    I'm sorry if I got you confused. This is in windows but my version is in french so I tried to get you a translation which was incorrect obviously.

    on WINDOS

    Start / network / your network connection / properties / general tab / check the display icon once connected (loose translation again). You can also just double click on the connection to have that packets counter opened.

  • July 13, 2005 at 7:58 am

    #573896

    Thanks, Remi

    I have tried it with each form which has linked SQL tables as the recordsource.  Each form opens with no records and the packets received are between 200 to 500.  As I move through the records, the packets increase by a few hundred.  If I open all the forms and play around for a few minutes, the packets received goes up to just over 30,000.

    Does this confirm anything you were considering?

    Paul

  • July 13, 2005 at 8:11 am

    #573907

    I tested on one of my average table. It took ±50 packets for 20 rows and 1700 packets for 21k rows. This would tell to me that access fetches the minimum data at the time. The other way to see this would be to use the profiler and see what queries access fires. This would give you a much better idea than trying to figure out the numbers of packets.

  • July 13, 2005 at 10:01 am

    #573987

    Thanks again, Remi

    I'll give it a go. 

    Paul

  • July 13, 2005 at 10:03 am

    #573993

    HTH.. Please post back your findings... I want to see how this turns out .

  • July 14, 2005 at 3:55 am

    #574292

    Hi Remi

    I'm struggling to make much sense of this.  So, we have bought some software to test the bandwidth of the server under different conditions.  I hope this will help.

    However, I am going to go with the concensus here and change my Access queries for views and sp's or pass-through queries.  I am also embarking on making my form recordsources ado oledb connections from the on load event.  Thus, working towards removing linked tables to SQL Server altogether.  I am hoping that this half-way house will bring about an improvement, and, when I have more time, to move over to adp.

    Thanks for all your help

    Paul

  • July 14, 2005 at 6:51 am

    #574341

    Good luck with the upsizing .

  • July 14, 2005 at 9:20 am

    #574447

    Thanks, Remi

    I think you will see a lot more posts under my name before I get it properly sorted out.

    Just a couple of points - though maybe I should put this in another post......

    I have put the following in the Declaration section of a form and am running the connection and the sql which produces the recordset from the on load event.

    Private strSQL As String

    Private cnnCM As New ADODB.Connection

    Private rstCM As New ADODB.Recordset

    I have read that some of this should be re-usable with ADO (in other forms etc.) and am wondering how to make best use of this.

    Also, should I close a recordset when I move away from a form to another form (again thinking of server resources), but leave the connection open for the duration of database use?

    My connections using SQLOLEDB are fine - but I'm hazy about the finer details - and I guess that this is the point to ask the question.

    Paul

  • July 14, 2005 at 9:27 am

    #574451

    I'm not sure what you are talking about... what do you want to do with the connection, the recordset (1 form or multiple). What re-use did you want to make of the recordset?

  • July 14, 2005 at 9:55 am

    #574463

    If I hold fire on re-usability for a while and just ask when I should close a recordset (or close the connection) to prevent wasting server resources - it is just unclear from my reading up on the subject.

    If I open a recordset on a Staff form to show all staff members, then move to a Student form to show all students, should I close the connection for the Staff form (when it no longer has the focus) to preserve server resources. 

    I am just trying to think my way around minimizing server traffic.

    If this doesn't make sense, Remi, I'll work through it and post again if I get badly stuck.

    Thanks

    Paul

  • July 14, 2005 at 10:14 am

    #574468

    That's the down side of the ADP. You have 1 global connection (good) but it's always opened. So you should expect to have 50-100 connections at all times on the server. You can access that connection if you need : currentproject.connection

  • July 14, 2005 at 10:18 am

    #574469

    Okay, I see - I'm sure it will all make sense eventually...

    Many thanks for all your help in this.

    Best wishes

    Paul

  • July 14, 2005 at 10:21 am

    #574473

    Alright... start a new thread for the next question so that others may benefit from the answer.

  • July 14, 2005 at 3:41 pm

    #574611

    "That's the down side of the ADP. You have 1 global connection (good) but it's always opened. So you should expect to have 50-100 connections at all times on the server."

    This really should be in a new thread but...

    Actually, MS says that Access always has at least 3 connections per client.

    1) All Form recordsets

    2) All combo boxes

    3) the Main Project Window objects (e.g. Tables, Queries, etc in the Project window)

    In addition, Access sometimes opens new connections to handle subform recordsets if you are using Linked child/master fields in your subform.  This is all documented in the MS KB.  If you don't use linked fields, my preliminary testing seems to indicate that the shared form connection is used.

    MS claims that SQL Server can handle tens of thousands of open connections simultaneously - if you have the right server resources (presumably RAM).  I have never seen this verified with data.  However, in the absence of hard data, many programmers work hard to minimize the number of open connections (like the ADO.NET disconnected approach), despite the fact that it may not be necessary (except perhaps for large web sites.)  I don't have any data on this, but there are lots of conflicting pieces of info/opinions on connections/resources out there.

    ---------------

    Re:

    "I have read that some of this should be re-usable with ADO (in other forms etc.) and am wondering how to make best use of this."

    Paul, I'm not sure what you mean here, but I think that all MDB forms must use DAO recordsets, and can't use ADO.  MDB form recordsets are only supposed to return DAO. I don't think you can assign code-based ADO recordsets to forms in MDBs.  I think you can do it with DAO.   The Access documentation is ambiguous about this, so it's trial and error. 

    With ADPs, I think you can assign ADO recordsets to forms.  AFAIK, ADP forms can only use ADO recordsets, not DAO, IIRC. But again, the docs are ambiguous about this also.

    In any case, this is all academic, as preserving connections won't help you, as Remi has already explained.

    HTH,

    Rich

  • July 14, 2005 at 5:18 pm

    #574618

    I'll recheck my sources tomorrow .

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply