Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Queue ADO connections to MDB backend

    Lets imagine the typical front-end/back-end scenario. However, imagine that no linked tables are used. All transactions are done with ADO connections - these are opened at the start of a transaction, the required data is appended to temp tables in the front-end, then the ADO connection is closed. If the user saves any changes, the connection is opended, the data is appended back to the backend, and the connection closed.

    What do you people think to the idea of using temp files to queue the connections to a backend? So, when you want to open a connection, a routine reads the number of temp files created by other users performing transactions. When the number of temp files is below a certain number, then the front-end creates a uniquely named temp file, then begins the transaction. Obviously, at the end of the transaction, the temp file is deleted, thus allowing someone else to begin their transaction.

    Is this a massively stupid idea, or has anyone actually ever done this? The reasoning behind this idea is the stop Access back-ends getting hammered and ending up corrupt when too many users are connected to it on a high-usage network.

    Here's the code I'd be using to do this:

    Count temp files:

    Code:
    strFileName = Dir$("N:\*.alf")
    Do While Len(strFileName) <> 0
        lngFileCount = lngFileCount + 1
        strFileName = Dir$
    Loop
    Create temp file:

    Code:
    strFileName = fOSUserName & ".alf"
    Open strFileName For Output As FreeFile
    Close

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Within your question it seems this idea of yours is coming from the fact that you have a lot of users using the Access front end at the same time. What I have done to allow more users than Access can handle is to have the Access front end on everyone's PC rather than calling it from a network drive. Within the front end, I have a local table that has the version number in it, and a matching table in the backend that has the current version number. When the match, everything is fine. When they don't match, I kick off a Shell which is my Refresh.mdb. I pass the name and path of the DB that is requesting the Refresh to the Refresh.mdb. Immediatly after the Shell command, I do an Application.Quit.
    The Refresh.mdb database will use the received db name and path to determine which database from the network to copy to the path just received. Then after the copy is finished, the Refresh.mdb will do it's own Shell command to call the newly copied db from the local hard drive. I'm no longer getting corrupted db's because of too many users. Added advantage is that the users have their own copy of the front end db and therefore are working faster because it cuts down the network traffic.
    HTH,

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I'm aready using the method of local front-ends for each user though. The trouble is that the network is very high-use, and I think that bandwidth for Office Applications is also bottlenecked. I would imagine that if my method works, you could in theory exceed the 255 user limit that JET imposes, since you would only ever allow, say, 5 simultaneous connections.

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    In the late 70's, I was working in San Diego and our sales office was in Chicago. In San Diego we had a batch system, in Chicago we had an interactive system with local terminals attached. Because there was no high speed communications at a reasonable price, (it was the Dark Ages) we batched our daily transactions at both ends, then swapped batch transaction files during the night. I see the similarity with what you are suggesting. Our nightly swap worked VERY well for several years. I don't know what ever happened to the nightly swap as I was gone before it was changed.
    I said all of that to set up saying this: Sounds like a really good idea to me. A tad bit complicated, but only to implement. The concept is quite elegant I think.

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I think it could work, but thought I'd post here first to see if anyone could blow it out of the water!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bcass
    I'm aready using the method of local front-ends for each user though. The trouble is that the network is very high-use, and I think that bandwidth for Office Applications is also bottlenecked. I would imagine that if my method works, you could in theory exceed the 255 user limit that JET imposes, since you would only ever allow, say, 5 simultaneous connections.
    if you are using a server back end, and an access front end.. then wheres the 255 limit coming from? Im assuming (of course) you are deploying a local front end.

    you dont need to use a workgroup file if you are deploying a MDE front end, but if you must then consider deploying an appropriate workgroup at a more local level on say a shortcut to the applciation.

    if you have migrated the applciation using the qupgrade wizard then you still have soem work to do. Although the wizard does lots for you, it doens't fully leverage the strengths of using a server back end. You need to start doing a more DBA role.. setting up the queries and stored procedures on the server. If you are concerned about network traffic then you defiantley need to make sure youuse maximum use of SQL in passthrough queries rather than using JET to handle the SQL locally.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I'm not using a server back-end, I'm using an MDB backend. And yes, as previosuly stated, I'm using a local front-end for each user. The front-ends are MDE files.

    You say you don't need to use a workgroup file when using MDEs - why is this?

    consider deploying an appropriate workgroup at a more local level on say a shortcut to the applciation
    How do you do this? I don't usually use Access built-in security, I usually use my own.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry I could have sworn I read you were using a server back end...

    your 255 connection limit is usually a problem within JET environments (using access default database).

    PKBaldy will suggest otherwise, but Ive generally found that JET starts to have problems anywhere between 15 and 50 concurrent users. above that sort of number of users I wouldn't contemplate using JET for a single data back end. Once the app gets to that sort of userbase I would insist on switiching to a server back end.

    So the 255 connection limit fall by the wayside if you are using a Access front End, if each is using a local or individual workgroup files, and the security is largely controlled by your server and the network permissions.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Yes, I too have found that anything above 20 concurrent users causes no end of problems on a high-usage network, when using an MDB backend. That's why I put together the ADO queing system as described in my first post. This way, there will never be any more than x number of actual concurrent users connected at any one time.

    When you talk about separate MDW files for each front-end, is that because MDW files can also suffer corruption problems when a large number of users are connected to a single MDW?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the 255 connections limit is soemhting thats inherent to JET, so Ive always worked on the basis that its best to keep well under that figure throughout JET. When I use MDW files I always make sure they are fairly granular.. I dont have monolithic MDW files.. I define them at workgroup or site level. If there is ever any risk of blowing that number of users then I break it up so that there arent too many applicaitons all trying to access the same workgroup file. Each Acces instance is maintaining a link to its sepcified MDW file..,
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Your BE Access DB will allow up to 255 concurrent connections period. I have had multiple connections in the 15-22 user range with no adverse corruption problems in the BE ... Everything depends on how loaded your network is ... If you have next to no available bandwidth then even 1 or 2 connections can blow you out of the water ...

    Your idea can work as long as you don't need unique #'s instantly ( like purchase order #'s, work orders, or any other reportable unique items) ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by healdem
    PKBaldy will suggest otherwise, but Ive generally found that JET starts to have problems anywhere between 15 and 50 concurrent users. above that sort of number of users I wouldn't contemplate using JET for a single data back end.
    If that was referring to me, I'm not sure why. I would tend to agree with you, based on what I've read elsewhere. I certainly wouldn't argue the point either way, since all of the "big" applications I've designed use SQL Server, so I don't really have any personal experience with that many users and a JET BE.
    Paul

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pbaldy
    If that was referring to me, I'm not sure why. I would tend to agree with you, based on what I've read elsewhere. I certainly wouldn't argue the point either way, since all of the "big" applications I've designed use SQL Server, so I don't really have any personal experience with that many users and a JET BE.
    OOPS!
    its more likely I meant PKStormy... must have been on a real role that night
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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