Results 1 to 3 of 3

Thread: A bit of advice - maximum number of DAO recordsets open at once?

  1. #1

    Thread Starter
    Member
    Join Date
    May 2018
    Posts
    51

    A bit of advice - maximum number of DAO recordsets open at once?

    I'm doing a bit of work on a client's POS system. At present it uses about 65 DAO recordsets. This corresponds to many different tables, most of which are opened twice, once as a table and once as a dynaset as some bits of the system use indexes to locate things and others use find first...find next. These tables are housed in 10 database files, so there are also 10 databases. All of these are opened at once on launch and remain open the entire time the program is running, even if not being actively used. The system is also multi user (usually 2 copies open at once but can be 3)

    So far, this works fine. However, I am now looking at making it a pseudo-multisite system by having a copy of all the datafiles up to the previous day hosted at each site so each copy of the system has access to the data. So far I've got some simple apps which make use of this but they only need a couple of recordsets from each copy of the system open to do their work. Now I'm looking at integrating this all into the main program, and want to be able to access all the data.

    I'm concerned as to how well the current implementation would scale up if I simply added all the extra databases and recordsets in, this would mean 70 databases and 455 recordsets all open at the same time. Is this even a problem or should I be looking at better ways of doing this? I've already considered moving all the tables into one file - they were split up originally out of concern for the 2GB limit but it is clear that they are not going to get anywhere near that during the life of the system so that's easily done. Doesn't alter how many tables there would be though. Possibly some middleware which merges the same tables from different copies of the system together into one big table?

    Or as I said, if it will work fine I'll just go with having the huge number of recordsets.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: A bit of advice - maximum number of DAO recordsets open at once?

    I'm concerned as to how well the current implementation would scale up if I simply added all the extra databases and recordsets in, this would mean 70 databases and 455 recordsets all open at the same time.
    Have one database, with the appropriate number of tables in it... in addition, add a SiteID to each table, or at least to the parent tables, so that you can determine the "site owner" of the data... Then each site would get data that's associated with that siteid.

    Example: Let's say you have a Sales table... it has the list of sales.
    It might look like this:
    Code:
    ID - int (PKEy)
    SiteId - int (FKey to Site table)
    Amount - money
    ItemId - int (FKey to Item table)
    .. and so on
    Now the Item table... if all sites have the same items...
    Code:
    ID - int (PKey)
    Name - string
    Description - string
    Price - money
    -- and so on
    but if each site could have different items for sale:
    Code:
    ID - int (PKey)
    SiteId - int (PKey to Site table)
    Name - string
    Description - string
    Price - money
    -- and so on
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    New Member
    Join Date
    Sep 2020
    Posts
    4

    Re: A bit of advice - maximum number of DAO recordsets open at once?

    there doesn't seem to be an actual limit, but probably not a good idea, one crash will destroy them all

Posting Permissions

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



Click Here to Expand Forum to Full Width