Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best way to handle form timer (A2002)

    Hello All,
    Per some previous post, I asked about form timers. I would like some opinions on the best way to handle them given my configuration. I am using a FE/BE setup using Citrix server. Basically the citrix box handles all of the database work and the user just sees a screen snapshot of what is happening on the server. I have 5 append queries that I want to run every 5-10 minutes. I did not put these in the front end as I don't want 20 FE's trying to do the updates. So what I did was to put the queries and form into the BE. My question on this is..... With 5 users logged into the DB, is there really 5 instances of the BE running, or just one? Is the form in the BE loaded when the file is being linked to from the FE? I thought of having a separate file with the form and queries that would run when the server boots, but the terminal server doesn't actually run a "User" until someone logs on to that server.

    Sorry about being long winded!

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Best way to handle form timer (A2002)

    The backend database doesn't "run" at all unless someone opens it explicitly. It merely serves as a place to store data for the frontends to use. So if a user opens the frontend, it only connects to the backend, but it doesn't open the backend in the Access interface. Forms aren't opened, etc.
    You would need to open a separate frontend database and open a form that runs the timer. I have no experience with Citrix at all, so I don't know if it is possible to automate this.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best way to handle form timer (A2002)

    Thanks, I will keep looking into it.

    Thanks,
    Mark

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best way to handle form timer (A2002)

    Why would you be running append queries every 5-10 minutes? Appending from what to where? Running something like that from a timer with users actively involved with the database sounds risky to me. What is it that you're trying to accomplish?
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best way to handle form timer (A2002)

    Thank you Charlotte,
    I have this Access DB connected to a separate SQL server DB (not MS). The ODBC connection is to slow to use with the tables truly connected. Therefore, I created a copy of some of the date within these connected tables to my local BE. I am then running a separate small FE that runs 4 append queries at 5-10 minute intervals. Mostly these are updating 1-2 records at a time. Do you have some ideas that may help? What are the risks.

    Thanks,
    Mark

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

    Re: Best way to handle form timer (A2002)

    I think we need to know a bit more about how your network is configured as well as some information about the servers involved. As well, some information about the sizes of the tables would help. I do have some experience with Citrix, Windows Teminal Services and the like, and have used it quite successfully with Access front-ends connected to SQL Server back-ends. My suspicion is that you are expending significant time and energy trying to solve performance issues with SQL Server with workarounds. So some questions:<UL><LI>Are the Citrix application and SQL Server running on the same server or separate servers?<LI>What speed LAN are you using - 10Mbit or 100Mbit?<LI>How are your users connecting to Citrix - dial-up or DSL/Cable modems?<LI>What version of SQL Server are you running?<LI>How much memory has been allocated for SQL Server to use?<LI>Are you using any SQL Server views or stored procedures to speed things up?[/list]As I indicated in a previous post, our experience indicates that ODBC connections perform almost as well as direct connections in most cases, and in some cases actually outperform connections using ADO.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best way to handle form timer (A2002)

    Hi Wendell,
    Thanks again for the help. The reason for wanting to switch to sql server is that we have had some table corruptions and help from MS literature says that we are using the DB well beyond the design capabilities. Some of the tables we have contain record sets beyond 15-20K records. I am not switching over to SQL because of poor performance, only to have a robust back end solution. Keep in mind I have not changed over to SQL yet except on my laptop for testing and development. To answer your questions:

    1. Citrix will be on separate boxes. Currently, the citrix server and Access FE / BE are running on the same server.
    2. All our network is 100Mb
    3. Most of the time, the users are connected via LAN in house and DSL remote.
    4. We are not running SQL yet. If I go through with it, I will want whatever is the most current version.
    5. If we do the conversion, the SQL server will have a dedicated server with at least 1 GB of RAM. I don't plan on running anything else on that box besides the normal precautionary software.
    6. I plan on using as many stored procedures and views as possible to gain performance.
    7. Typically, there are 5-20 users connected to the database at one time.

    Thanks,
    Mark Santos

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

    Re: Best way to handle form timer (A2002)

    Hi Mark,
    SQL Server should help with your corruption problems. In over 10 years experience with SQL Server, I've only seen two cases of corruption. In one case, we are pretty sure a set of software RAID drives containing the database was pulled to do Y2K testing, and then put back in the wrong order. The other was way back in 1994 on SQL Server 4.28 or something like that, and I never got a clear answer from the consultant we used as to what had happened. It definitely is a more robust product as well, in that it uses a log so that if a hardware crash occurs, you can use a backup to recover pretty much everything right up to the time of the crash.

    On the other hand, the record counts you are dealing with are well within the limits of Access. I've personally built Access tables with as many as 4 million records and things worked fine - but you certainly want to index tables and return as small a recordset as possible. Access is more sensitive however to the number of users, and in my experience 5 users usually works pretty well, but with 20, especially if they are doing heads-down data entry and queries, performance begins to slip. I suspect all of your Citrix users are working with a single Access front-end - if that's correct, you might want to look at letting each user have their own copy of the front-end. We've found that to be more stable and give somewhat better performance as well. I would however move the Access back-end to another server, and then compare performance with SQL Server running on the same server. That gives you more of an apples to apples comparison. SQL Server 2000 should server you very nicely, although a new release is scheduled later this year according to the grapevine. Finally, be cautious about jumping to performance conclusions using your laptop - I do run SQL Server on mine with 512MB of RAM, but that is about the mininum that you will get acceptable performance with. My partner runs a laptop with 1GB of RAM, but identical in all other respects, and his is noticeably faster than mine. Hope this helps give you some guidance on the path ahead.
    Wendell

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best way to handle form timer (A2002)

    Hi Wendell,
    Thanks again for the input. All of what you said makes sense. In my citrix setup, all the users have a separate front end. This front end is copied fresh everyday from a master location to their user folder on the server. This solves problems with the FE getting bloated by using the same file day after day.

    On my laptop, the sql server with a converted BE runs great. I think I will pursue using our existing front end with the ODBC connected back end first. Then maybe I will do some things with ADO as an experiment.

    Thanks,
    Mark

Posting Permissions

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