Results 1 to 4 of 4
  1. #1
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98

    Using a Button to select a table as the Record Source for a Form

    I have form which displays data held in a table. (nothing radical there then!)



    I export the contents of the table each month and have a dataset/table for each month.

    I'd like to load an archive version of the form and THEN select which dataset (table) to open. All the data table structures are the same, so once selected, the form acts the same for each archive file.

    I thought that I might be able to have a button on the archive form labelled "Select Archive" and when clicked, it allows me to select the archive table which I want to load as the record source.

    I suspect that this isn't good practice, but not sure which way to approach the task in hand. Perhaps I should simply export the monthly records into one "archive" table and use filtering

    Any suggestion how to achieve this or enlighten me to best practice?

    Many Thanks

    =Jimbo=

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Why are you doing the archiving? Normally data would remain in a single table. What you want is possible, but I would almost certainly not go in that direction.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98
    Hi Paul,

    The reason that we decided to archive was mainly because searches on this table were taking an increasingly length of time and it was assumed that this was being affected by the number of record. We are adding around 400-500 records per week. There would be about 5,000 records in the table if we hadn't shifted some out. Although we've overcome the search speed problems, it could have something to do with the fact that I've been implementing some of the "go faster" tips you gave me last month rather than as a result of archiving the records.

    Are you suggesting that you'd NEVER Archive ? We could end up with 20,000 + records in a year and they are only kept for reference and maybe analysis. Whilst I know I could be filtering the records, it made sense to me to compartmentalize them.

    Following you comment yesterday, I'll stitch them all back together and test

    Thanks

    =Jimbo=

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Hopefully those tips and perhaps indexing solve the speed problem. Access should handle a few hundred thousand records with ease.

    I've used an archive table in two applications, both taxi reservation/dispatch systems. I did it because I needed the queries on upcoming reservations to run instantly (the dispatchers requery the data constantly). I've since seen two commercially written limo reservation/dispatch systems that do the same thing, presumably for the same reason. I've written a limo reservation/dispatch system and didn't use an archive table, because the volume didn't justify it. I have applications with millions of records where I don't use an archive table and the performance is fine, though those use SQL Server as the back end.

    I guess I'm saying I will use one, but only if absolutely necessary.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change/select record source of subreports
    By TerriLynnG in forum Reports
    Replies: 2
    Last Post: 08-24-2013, 10:18 AM
  2. Replies: 4
    Last Post: 05-09-2012, 07:20 AM
  3. Replies: 1
    Last Post: 03-04-2011, 08:53 AM
  4. Replies: 2
    Last Post: 11-29-2010, 11:16 AM
  5. Replies: 3
    Last Post: 03-22-2010, 04:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums