[AccessD] ...building, managing, and using a page favorites list

Max,

    My sample db named Query_TopsPerSubGrpLargeData is available at Rogers Access Library. It has a form named F_FillTables. 

    In the set of unbound text boxes at right, you can enter the desired number of patients, number of tests per patient and number of days. Projected number of total records gets displayed at bottom. (Existing default values result in 12000 records).

    Option group at top offers two alternative methods for populating table T_LabTest:
    (a) Combination of action query and recordset AddNew method and 
    (b) Pure  recordset AddNew method.

    Clicking the command button captioned "Fill Tables With Fresh Data" carries out the task. Progress bar at bottom shows the progress as well as time taken in seconds (For small number of records the timer may hardly register beyond 0, specially with option (a)).

    Link for my samples is given below:

http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Max Wanadoo 
  To: 'Access Developers discussion and problem solving' 
  Sent: Monday, March 09, 2009 12:01
  Subject: Re: [AccessD] ...building, managing, and using a page favorites list


  Thanks AD.

  Really interesting.  I don't suppose you have any sample code from these
  tests, do you?

  Max


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
  Sent: 09 March 2009 05:30
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] ...building, managing, and using a page favorites
  list

  Max, Gustav, JC,

      For bulk inserts, action query is observed to be faster as compared to
  pure recordset approach. The advantage becomes more pronounced with larger
  data set. In fact, many orders of magnitude.

      In a test conducted for populating a five field table with 1,200,000
  fresh records, the results were as follows (Access 2003 desktop on Win XP):

      (a) Using DAO recordset alone:                                  4605
  seconds
      (b) Combination of action query and DAO recordset:    210 seconds

      With the above workload, method (b) using action query, comes out
  approx. 23 times faster. The differential tends to increase with increase in
  data size. On the other hand, if the number of records involved is very
  small, the speed difference should not matter. 

  Best wishes,
  A.D. Tejpal
  ------------