locked
"Early warning" for error 3048 RRS feed

  • Question

  • Hi

    Is it possible to create some kind of "early warning" for error 3048? Sometimes the user opens more forms than the access jet engine can handle. I wish there was a way to inform the user that system resources are now almost completely claimed, with a suggestion to close one or more forms that may not need to be open.

    Is there such a way?


    Best // Peter Forss Stockholm GMT +1.00

    Friday, December 15, 2017 10:09 AM

Answers

  • Is there such a way?

    Hi Peter,

    I don't know, but I think there is not such a way.

    It is not the number of forms only, but all resources, including all RecordSources for forms, reports, listboxes, etc. And I think the total number of listboxes is the most important contributor.

    The number of open forms can easily be checked by the Count of the Forms-collection.

    In my systematics I have never encountered that limit, probably because I do not use listboxes. A click in the control where you would have a listbox opens a dynamic form where the user can select the specific record (or record_id). After selection that form is closed again. During selection the user can sort on any columns to enhance the selection process, and do many more things.

    Further I use a systematics where certain forms are closed automatically when they loose focus.

    Then I have set a limit on how many times a dynamical form (all are identical) can be opened at the same time. At reaching that limit, the user is asked to close some forms. This is not to prevent error 3048, but merely for the simplicity for the user. 

    Perhaps it gives you some ideas.

    Imb.

    • Marked as answer by ForssPeterNova Friday, December 15, 2017 12:52 PM
    Friday, December 15, 2017 12:29 PM
  • Peter

    It should indeed be possible. You should be able to make use of the code behind my available connections tool for this purpose.

    Have the form running but hidden. When the number of available connections drops below a specified value, show a message informing users that they need to close certain objects.

    Having said that, this is never an issue in any of my production databases because all objects are closed when no longer required whether they are forms, recordsets or whatever.

    @imb

    Neither combos nor listboxes are an issue. As they both involve row sources, each uses connections but as there are 255 connections available, there is no reason to avoid their use. Listboxes consume no more resources than combos. Suggest you try my utility to prove this for yourself.



    • Edited by isladogs52 Tuesday, September 29, 2020 6:55 PM
    • Marked as answer by ForssPeterNova Thursday, October 1, 2020 11:05 AM
    Tuesday, September 29, 2020 6:52 PM

All replies

  • Is there such a way?

    Hi Peter,

    I don't know, but I think there is not such a way.

    It is not the number of forms only, but all resources, including all RecordSources for forms, reports, listboxes, etc. And I think the total number of listboxes is the most important contributor.

    The number of open forms can easily be checked by the Count of the Forms-collection.

    In my systematics I have never encountered that limit, probably because I do not use listboxes. A click in the control where you would have a listbox opens a dynamic form where the user can select the specific record (or record_id). After selection that form is closed again. During selection the user can sort on any columns to enhance the selection process, and do many more things.

    Further I use a systematics where certain forms are closed automatically when they loose focus.

    Then I have set a limit on how many times a dynamical form (all are identical) can be opened at the same time. At reaching that limit, the user is asked to close some forms. This is not to prevent error 3048, but merely for the simplicity for the user. 

    Perhaps it gives you some ideas.

    Imb.

    • Marked as answer by ForssPeterNova Friday, December 15, 2017 12:52 PM
    Friday, December 15, 2017 12:29 PM
  • Hi Imb

    Thank you, again.

    It's always interesting to get to know your experiences. You work differently with Access and there is a lot to learn from it. What I take today is that listboxes are resource-intensive, forms automatically closed when they loose focus.

    Previously, I've learned that local tables (when possible to use) consume half compared to linked tables. And that snapshots do not require the same resources as dynasets.


    Best // Peter Forss Stockholm GMT +1.00


    Friday, December 15, 2017 1:25 PM
  • Hi

    isladogs52 have a nice little tool. It checks available connections.

    http://www.mendipdatasystems.co.uk/available-connections/4594418530


    Cheers // Peter Forss Stockholm

    Tuesday, September 29, 2020 5:31 PM
  • Peter

    It should indeed be possible. You should be able to make use of the code behind my available connections tool for this purpose.

    Have the form running but hidden. When the number of available connections drops below a specified value, show a message informing users that they need to close certain objects.

    Having said that, this is never an issue in any of my production databases because all objects are closed when no longer required whether they are forms, recordsets or whatever.

    @imb

    Neither combos nor listboxes are an issue. As they both involve row sources, each uses connections but as there are 255 connections available, there is no reason to avoid their use. Listboxes consume no more resources than combos. Suggest you try my utility to prove this for yourself.



    • Edited by isladogs52 Tuesday, September 29, 2020 6:55 PM
    • Marked as answer by ForssPeterNova Thursday, October 1, 2020 11:05 AM
    Tuesday, September 29, 2020 6:52 PM
  • Hi isladogs

    Thank you for the tool.

    I thought there was a connection between the number of linked tables and available connections. But I do not see any such connection. Tested your tool in a database with 200+ tables and in another that was completely without tables. Same result 251 Available connections.


    Cheers // Peter Forss Stockholm

    Wednesday, September 30, 2020 3:31 AM
  • @imb

    Neither combos nor listboxes are an issue. As they both involve row sources, each uses connections but as there are 255 connections available, there is no reason to avoid their use. Listboxes consume no more resources than combos. Suggest you try my utility to prove this for yourself.

    Hi isladogs,

    Don't confuse the cause and effect of my not-using List- or Comboboxes.

    The cause is, that I found the possibilities of the displays far to limited: I wanted to be able to see the whole record if necessary, to edit fields in the record, to zoom in foreign key relations in the record, to change the selection, and a couple of more things that would make the display really dynamic. For that purpose I developped a "form" (just the same generalized form that is tuned in a different way) that made the standard List- and Comboboxes superfluous. An extra effect then is the less open connections at the same time.

    From general intrerest I would like to see your utility, but alas, my A2003 won't understand it.

    Imb.

    Wednesday, September 30, 2020 8:12 AM
  • Peter

    This is a quote from my website:

    Each reference to a local table, linked SQL table or query object uses 4 connections.         
    A reference to a linked Access table, spreadsheet or text file uses 6 connections.  

    BUT this only applies when the object is in use.

    Tables (whether local or linked) use no connections UNLESS they are in use either by opening the table or a query/recordset/combo/listbox based on them

    The connections are automatically released when the object is closed

    So it doesn't matter whether you have 1 linked table or (as in one of my large databases) 320+ linked tables. Its what you do with them that matters.

    Wednesday, September 30, 2020 3:43 PM
  • Hi imb

    My comments were based on what you wrote earlier in this thread.

    As you realise, any type of record source will consume a number of connections whether its a form / subform / combo  / listbox or whatever. However as long as each object is closed when not in use, those connections are, as stated in my previous reply above, released again.

    So whilst tabbed forms (as an example) may well have distinct record source for each tab plus additional row sources for combos/listboxes, this can be easily managed by only connecting the record source when each tab is open & clearing it when moving to a new tab

    Anyway, as you said you would like to look at the utility, I have just added an Access 2003 MDB version to my website article: http://www.mendipdatasystems.co.uk/#/available-connections/4594418530

    If you find that useful & are interested in a 2003 version of any other utility, let me know & I'll convert where feasible.

    Also, if you would like to continue discussion of your form designs, perhaps we could do so by email. If interested, you can find my business email on the Contact page of my website

    Wednesday, September 30, 2020 3:54 PM
  • Hi isladogs

    This was news (to me) and good news. I have developed access applications many years now and have always tried to use as few tables as possible and always use local tables when possible.

    Thank you very much!


    Cheers // Peter Forss Stockholm

    Wednesday, September 30, 2020 5:24 PM
  • Hi Peter

    You're welcome. Perhaps I need to make that point clearer in my website article.

    I have the opposite approach which is to have as few local tables as possible in the FE in order to reduce file size and prevent bloat. The exceptions are things like tables for settings and to manage linked tables as I use DSN-less connections. Where temporary tables are needed I will often put them in a separate 'side-end' app which can be replaced as necessary

    Wednesday, September 30, 2020 6:04 PM
  • Anyway, as you said you would like to look at the utility, I have just added an Access 2003 MDB version to my website article: http://www.mendipdatasystems.co.uk/#/available-connections/4594418530

    If you find that useful & are interested in a 2003 version of any other utility, let me know & I'll convert where feasible.

    Also, if you would like to continue discussion of your form designs, perhaps we could do so by email. If interested, you can find my business email on the Contact page of my website

    Hi isladogs,

    Thank you for your "Available-connections" database in A2003 format. I have found it and will now see how I can use it. I don't know how it will work in a shared library database. Against my philosophy is the use of a (dedicated) form and a report.

    I would like to discuss my generalized forms, but also the different approach to start a new database, and the maintenance of existing ones. I prefer to do that in this forum, but I doubt if there is any further interest, at least based on the responses. If there is no further interest, we can continue by e-mail.

    Imb.

    Wednesday, September 30, 2020 7:52 PM
  • Hope you find it useful.

    Excellent though this forum is, the fact that it is impossible to upload files is at times an issue. Whilst I can give a web link where relevant, often that isn't appropriate whether a quick demo file would do the job.

    Despite your best efforts to explain your design principles, I must admit that I'm still unclear what you're describing. Being able to look at an example would clarify your approach. We can always return to the forum with any points of general interest


    Wednesday, September 30, 2020 8:26 PM

  • So it doesn't matter whether you have 1 linked table or (as in one of my large databases) 320+ linked tables. Its what you do with them that matters.

    Hi isladogs52

    320+ tables.
    Is there a way to view, handle and edit the relationships better than Access own buit-in relationships editor. I mean, I have an application with 200+ tables and in the bulit-in editor it is messy.

    Cheers // Peter Forss Stockholm

    Sunday, October 4, 2020 6:16 AM
  • Is there a way to view, handle and edit the relationships better than Access own buit-in relationships editor.

    Hi Peter,

    In the subjective way, as measured how "I" feel it, yes.

    In one of the metadata tables I keep record of which foreign key is used per item (entity). By inspecting the table I can immediately see the foreign keys per item, but also where this item is used as foreign key. If you do this recursive, you can see all relations. With some kind of reporting system you can get a total overview.

    I am not so interesting in the complete overview, far more interesting is to know which relations (foreign keys) there are for a certain item (entity). When I have a continuous form of an item, I display buttons for all foreign keys, which - when pressed - zoom in in the related data. These buttons a red-coloured in the next example. It is standard available in any continuous form.

    But there are also other ways to display all the current relations, authorization dependant, including self-references:

    So, all relations can be available on the spot. The simple trick is a (part of a) metadata table that holds the relations.

    In your case you have to make such a table manually, in my systematics it is done completely automatic during the definition phase of any database.

    Imb.

    Sunday, October 4, 2020 8:39 AM
  • Hi Peter

    Apologies for not replying sooner but my internet connection has been down since Friday...so replying on my phone. Unlikely to get broadband reconnected for several days which is a pain...

    There isn't really a better way of creating or editing relationships as these are saved in the system table MSysRelationships which is read only.

    However, there are alternative ways of viewing these...though I believe I have already mentioned these before. Even so, the info may be of use to others as well

    1. Allen Browne has an improved relationships report giving details about the linked fields and datatypes. See http://allenbrowne.com/AppRelReport.html

    2. The MSysRelationships system table can be queried giving info about the fields linked, the join type, whether referential integrity has been enforced and whether cascade update/delete has been applied. That info is stored in a system table field called grbit and there is a detailed explanation of its meaning in the third part of my article at http://www.mendipdatasystems.co.uk/relationships1/4594533224.



    • Edited by isladogs52 Monday, October 5, 2020 7:53 AM
    Monday, October 5, 2020 7:52 AM