none
DoCmd.TransferDatabase 'max_user_connections' RRS feed

  • Question

  • My service plan does not allow me to change this number. I figure I shouldn't have to. But I can't find a good way to limit my connections by managing the lifetimes using this docmd, or using more straightforward coding. I go through about fifteen tables, and then it hits a block of ten tables 200 wide(199) holding dates. I knew it wouldn't matter but I tried changing from date to string. I also tried recreating all my tables. 200 is the allotment of members in a club. It gets through four big tables, and then shuts me down.


    • Edited by Xittenn Friday, May 5, 2017 4:18 AM
    Friday, May 5, 2017 4:17 AM

Answers

  • Your table structure is wrong and this is what is causing all your headaches.  Rule of thumb, when your table exceeds 20 fields/columns your design is flawed and not properly normalized.

    In any database, clients should not be enter as fields/columns, but rather rows.

    You'd have a Client table with fields setup like:

    Clients
    *******************
    ClientId
    FirstName
    LastName
    DOB
    ...

    and then you can enter millions of clients into the table without any issue.

    From there, not knowing exactly what you are trying to track, you could create tables for

    • Telephone numbers
    • Treatments/Visits
    • Medications
    • Allergies
    • ...

    So I'd urge you to rework your table structure.  If you need help, post back, we're here to try and help.

    The following sample databases may also help you understand some of the basic table setups


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, May 6, 2017 10:23 AM

All replies

  • Could you explain a bit more about what you're doing?  Are you getting this error on a single execution of DoCmd.TransferDatabase, or are you executing TransferDatabase in a loop?  Normally I'd expect Access to try to reuse the same connection, if nothing about the connection changed between iterations.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, May 5, 2017 4:54 PM
  • My service plan does not allow me to change this number. I figure I shouldn't have to. But I can't find a good way to limit my connections by managing the lifetimes using this docmd, or using more straightforward coding. I go through about fifteen tables, and then it hits a block of ten tables 200 wide(199) holding dates. I knew it wouldn't matter but I tried changing from date to string. I also tried recreating all my tables. 200 is the allotment of members in a club. It gets through four big tables, and then shuts me down.

    Hi Xittenn,

    You hit a block of ten tables 200 wide .... 200 is the allotment of members.

    Dou you mean you have ten tables with a column for each member? In that case I would redesign the tables to make a ROW for each member. Which columns you then need depends on what kind of data you want to store.

    I have many applications running, but the number of fields per table hardly exceed 20.

    Imb.

    Friday, May 5, 2017 6:03 PM
  • I have a database for some programs for a mental health facility. Due to the limitations of Access, I made the maximum number of clients 200. So monthly dues, is a table. I have an ID number, an ID Proper number, and the date. I then have 199 columns representing 199 clients. I have ten of these tables. I am calling DoCmd.TransferDatabase once for every table. I have a business account with hostgator, which isn't sufficient for me to make any adjustments server side. I am using ODBC with MySQL for my backend. Aside from this max_connections issue, and my complete inability to maintain a connection when someone stops working(and sometimes while they are working,) it's been really easy to work with this setup. a maximum of five administrators will access the database at any time.
    Saturday, May 6, 2017 4:13 AM
  • hi I replied to Dirk with the same info.

    Saturday, May 6, 2017 4:14 AM
  • Your table structure is wrong and this is what is causing all your headaches.  Rule of thumb, when your table exceeds 20 fields/columns your design is flawed and not properly normalized.

    In any database, clients should not be enter as fields/columns, but rather rows.

    You'd have a Client table with fields setup like:

    Clients
    *******************
    ClientId
    FirstName
    LastName
    DOB
    ...

    and then you can enter millions of clients into the table without any issue.

    From there, not knowing exactly what you are trying to track, you could create tables for

    • Telephone numbers
    • Treatments/Visits
    • Medications
    • Allergies
    • ...

    So I'd urge you to rework your table structure.  If you need help, post back, we're here to try and help.

    The following sample databases may also help you understand some of the basic table setups


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, May 6, 2017 10:23 AM
  • I knew this going into the creation phase, but I think the way I had it is better for the administration that has to use it. I would like to have seen my actual question answered. That said, I've normalized my tables, and will handle clients through recordsets--instead of hard structure.
    • Edited by Xittenn Monday, May 8, 2017 12:41 AM
    Monday, May 8, 2017 12:40 AM
  • > Due to the limitations of Access, I made the maximum number of clients 200

    That is totally unreasonable, and an indication you have incorrect database design. Let's start there. A million clients should be no problem.

    That said, healthcare information typically has sensitive information and as such does not belong in an Access back-end (which cannot be properly secured). Upgrade the BE to SQL Server.


    -Tom. Microsoft Access MVP

    Monday, May 8, 2017 12:49 AM
  • That said, healthcare information typically has sensitive information and as such does not belong in an Access back-end (which cannot be properly secured). Upgrade the BE to SQL Server.


    Note, Tom, that the back-end in this case is actually MySQL, according to Xittenn.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 8, 2017 7:27 PM
  • I would like to have seen my actual question answered.
    If you'll answer the questions I asked, I'm willing to try.  It's possible that the problem is in the ODBC driver, but it's conceivable that you are doing something in code that is responsible.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 8, 2017 7:29 PM