Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't Open Any More Databases - Error 3048 (XP/W2K)

    Hi All,

    Seeking some more advice & help on how resolve or how to attack resolving this error. I have read the various posts where this problem has been encounter and are starting to understand the problems - I think. I have implemented & tried many of the suggestions from the posts and seem to have reduced the error. However some more changes have meant it has come back, so I am looking for ways to try to reduce connections or trace connections also etc.

    The database that I have written for a friend is complex in terms of what they have open, it is for recruiting so they have a client / position / filling client screens open at the same time. Each form has a tab control with several pages and each page has a subform/s. It is a front edn/be configuration with the be on a server and 4/5 ppl accessing via desktop.

    I have set up the tab controls so that each subform reconnects etc.. have checked to ensure that I am closing recordsets after using them. I now suspect that large number of combo box's are now taking effect in soaking up resources...

    From reading the other posts I under that combo's use up connections, hence if a tab page has 15 combo boxs would setting the row source to null and only setting it when the tab page gets the focus potenially remove a connection?

    Would shifting the be to SQLServer assist??

    Any help much appreciated from anyone who has had this problem and what you have done to get around it.

  2. #2
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Tony,
    I've not ever had this error condition, nor have I read other posts concerning it. However, I have successfully written a system with 10 main forms, all of which can be simultaneously loaded (though all but one are marked invisible). I'm somewhat anal about keeping track of all my open ADO tables, and carefully close them if, and when, any form closes; possibly even sooner if it was open only to satisfy an internal subroutine. The one thing I did that may be somewhat unusual is immediately upon system startup, I opened the ADO connection, and saved the connection variable in global storage. Thereafter, all of my screen code never sets another connection -- they just reference the global variable when I open each ADO Recordset.

    Anyway, it is a technique with which you might experiment.

    -- Jim

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Thanks Jim,

    Yes been tearing my hair out with this.. I'm not a programmer by trade, more a business analyst who loves to tinker... will put some thought and test out your method see if it may help me out.

    I decided to do some testing tonight also... took the db upsized it SQL then setup a ADP and MDB with ODBC connections to see what happened response wise. The MDB connected via ODBC is slower in response when scrolling through a recordset than the original and the ADP project is a fraction slower but of course also requires some considerable programming changes from what I have read (all new to me here)

    So in the interum I'm still confused as to what to exactly do, whether to persist with the access version and see if i can make it work. From what I have read here it appears that people have written the same size monster's so I figure it maybe my lack of expertise that is also causing some problems. On the whole it does runs we'll and I've been able to do what I wanted to. Just this stumbling block now...

    Part of my problem is also that when I test it here I haven't yet been able to get it too error, it only appears to be when there are 2/3 users using the db so also adds to the frustration.

    Many Thanks
    Tony

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Unfortunately, going to SQL Server or an MSDE back-end isn't likely to help you. It sounds as if you are ending up with too many connections to the back-end. A great many subforms or combo boxes with table or query row-sources can cause this problem. One solution might be to leave lookup tables used by combo boxes in the front-end. Otherwise, I suspect you may need to do some significant redesign, and perhaps some table normalization to really fix the situation.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Thanks Wendell,

    I suppose what I was really attempting to determine was if a SQL backend would potentially give me more connections...?

    I undertook a redesign, I removed many lookup tables back into one main lookup table. At the time if I remember correctty I removed about 18 odd table connections. At the same time I had also configured the least unused subforms to load only when the tab control was being used. This work did appear to help, until the last lot of additions that I just made. (which included another chunk of combo box's)

    One thing I have not been able to determine is how number of connections stack up. The back-end is on a server being accessed by 4/5 front ends. So does this mean each user can have the 2000 odd connections to the back end or does this mean that the 2000 from the back end is shared between the users accessing the data??

    Cheers
    Tony

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Sorry for not responding sooner - I believe there is some sharing of tables as I've seen databases with a fairly large number of tables shared by 20 or more users without problems. You indicate that you have the database split, but do you have the front-end deployed to the individual workstations? If you are sharing a single front-end among several users the connection issue is likely with it.

    To answer your question, yes SQL Server can support more connections, but not dramatically more - and in fact the MSDE begins to throttle things when you get more than about 5 users connected to it, and using ODBC can often make it think it has hit that limit. Thus my conclusion is that you wouldn't solve this problem by switching to it.
    Wendell

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Thanks Wendell,

    I've spent the last week away and while away taken the knife to this db and trimmed it to the absolute minimum that they. The database is split yes with a front end (mde) on each workstation (3-5 users) to the back end on a server. Things have gotten even more frustrating... Often this problem occurred with 3/4 users logged in. The other day 3 of them used it all morning (no problmes) and only 1 of them was there in the afternoon and it happen about 3 times.

    I added some vb to capture the screens and pages that they have been accessing and I've in no way been able to replicate this at home either on my PC or between my 2nd PC on a small home network doing what they do.

    When I compare the db to another I wrote it is about the same or even smaller now... there are 22 linked tables, 4 main forms that they have open with 10 odd subforms. Yes there are quite a few combo's and controls but no more than the other db I wrote which I have no problems with.

    I'm really starting to go nuts with why this is happening - I've spent hours reading whatever I can find on the net, hours recoding and trying out different things, but I can't seem to find out why this error keeps occurring

    I originally had this db in access 2000 and I'm starting to wonder if I should convert it back now as a test... as I don't remember having these issues before I converted.

    So clutching at straws I'm starting to wonder what else????

    Service packs / jet eng - Could these cause a problem if the server and PC's are not running the same versions??
    Could a corrupt install on a local PC cause a problem with the jet and the connections
    If I do convert to Access 2000 has anyone had any problems running the A2000 on PC's with XP installed??

    I'm open to all and any ideas or thoughts... and if anyone know's a way I can monitor or test these connections to find out exactly how many I'd love to hear from ya also.

    Many thanks to you all.
    Cheers
    Tony

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Best I can tell, this issue is probably related to the number of TableIDs involved - a search found this article in the Microsoft newsgroups that sounds very similar to your problem. It is also possible that you are bumping up against a limit on the number of objects ever placed on a form (784), but that seems much less likely given it appears to be a runtime issue. I would suggest that you take a hard look at putting the row sources for your combo boxes into a local table in the front-end in most cases, or if they are really static, putting them into the design of the combo themselves as a text string.

    FYI, I don't think going back to 2000 would help - I would look at that as a last resort. (I very recently worked on a 2002 based database with well over 100 linked Access tables and 200+ forms and didn't see this issue.) But you shouldn't have any problems running 2000 on workstations running WindowsXP either. I seriously doubt you are looking at issues with SPs for either Access 2000 or Jet 4.0. One thing that would help in debugging is knowing what the net effect of the problem is. Can the affected user simply shut down the database, reopen it and go on working? Does it affect all users, or just one at a time?
    Wendell

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Hi Wendell,

    Thanks for the ongoing support! The article you pointed me to was one that I had found in my travels and is exactly the problem that I am having.

    Can you give me some more info on " I would suggest that you take a hard look at putting the row sources for your combo boxes into a local table in the front-end in most cases"... how do you configure this? and what is intention/benefit to me if I do?

    One thing I am doing is setting the rowsource when the cbo get focus. The string allows all records to be viewed and then changed to then only allowable options when you enter the field. Could this also be having an impact maybe? (I am now only using 1 main lookup table for the entire database)

    In response to your question, Yes the affected user can shutdown and restart the database can carry on as normal. It appears to only effect one user at a time. Each user maybe doing various tasks, but basically they all tend to have the same 4 screens open. These 4 screens have tabbed controls, with 15 odd subforms attached etc.. (some subforms are only loaded when the screen is visited) It is not just anyone user, no pattern to when it occurs or how often, can be 3/4 people logged in or just 1 as the case was the other day. I suppose this is what has me confused even more now, that if 1 user can be logged in and this error occurs then something is not right... yet 3/4 users can be logged in and work away for half a day etc without any problems.

    As you said, people have databases with 100 linked tables and a heap of forms. Effectively I have 22 linked tables, 4 forms-tabbed (15 subforms approx) so hence my stubborn resolve to find what I either I am doing wrong or if infact I am hitting the limit.

    Once again many thanks for the continued support
    Cheers
    Tony

  10. #10
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Hi Wendell,

    Just been trying a few things out... I'm now wondering if the mdb is corrupt somehow to some degree??... I tired to import the database (XP to XP) and (XP to A2K) into a new database, it completes the import but there are certain forms (main forms) that when you try to design them they now hang and won't open. Tried this more than once and same thing... Once the import is complete the db compiles ok etc but I really suspicious that this is odd behaviour. I now wondering somehow this maybe related to my problem.... It's all rather odd

    Cheers
    Tony

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    We did actually experience something a little bit like this with the large database I mentioned previously. That database had more than 50K lines of code, and it seemed to get into a mode where you would get weird run-time errors, but if you recreated everything using SourceSafe it would fix the problem and things worked OK. In that case however, it would run on a few PCs, and not on others. If you haven't used any code that is likely to make Access 97 crash and burn, you might try saving the database in 97 format, and then converting it back to 2000. I'm just a bit suspicious that there is something in 2002 that causes some weird things to happen occasionally. The fact that only one user at a time is affected says the issue is with the front-end, so that's where you want to focus.

    Moving the single look-up table to the front-end cuts in half the number of TableIDs used internally by Access - linked tables take 2, so that might well fix your problem. (Table IDs are limited to 2048 in Jet, and that includes all system tables as well as open queries, etc., and thus any combo with a table or query rowsource consumes one or 2.) Another strategy, if your choices are pretty well fixed and infrequently need to change is to assign numeric values and string constants as the rowsource, thus not referencing a table or query. The string values would be the same info that you store in your look-up table, but you wouldn't need code to set the rowsource dynamically either. Another tool in the bag of tricks to make Access behave sometimes.
    Wendell

  12. #12
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Thanks Wendell,

    Just one question - The Table ID's, I am correct in the 2 combo's using the same linked lookup table would consume 4 links?

    I'm thinking that I will put the lookup locally, then when the user update or add's check the backend if it doesn't exist add it and update it to the front end. As you say this will greatly reduce the connections...

    I'm also going to rebuild the frontend as I suspect something might be odd. I need to do some research on the "objects ever placed on a form (784)" as with the build I see that I have a label control at 748.... so this maybe an issue also.

    Many thanks I feel like I'm making progress again.
    Cheers
    Tony

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    If I understand correctly, you are allowing users to add records to the lookup table. If that is the case, you really need a linked table in the backend, as you are likely to end up with conflicts when two users add different entries with the same key value. You could try to manage the process using VBA to update both tables simultaneously, perhaps using Transactions, but Access isn't terribly adept at that kind of thing.

    A question regarding your use of combo boxes - are they used to store a key value in the Data Source for your form or subform, or are they used to control the valid entries for their respective field and you actually store the text value in the Data Source?
    Wendell

  14. #14
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    Yes I'll need the linked table but will control the updating of the lookup when the db opens or at the time when a user adds a new record. (not to worried as I've worked out how best to deal with this) What it will mean however is that all combo's will be looking locally for their data & hence a reduction in the table id's with each combo. (we'll that is what I am hoping)

    The combo's all store key id's in the data source, I may have one that stores text only.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases - Error 3048 (XP

    The reason I asked is that we don't often find situations where we have key value being stored that we allow users to add entries to the list. In general, we try to figure out the various categories or whatever in advance, and then if a need for a new one arises, we add that administratively rather than letting users enter misspelled things. There are times where we may use a combo box to lookup a specific record, and if it's not found we then execute "Not In List" code to ask the user if they want to add a new record, but there aren't many other situations where we give the user that choice. One of those things you implement after a few lessons in the school of hard knocks.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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