They want it to run faster!!! 1

[Jedi420]

I have a split Access 2000 database with the backend on our network drive and the front end on everyones computer. Since we're all in the same office, everything runs fairly fast and well. Now, however, there are 2 more additional users who are at a remote site and they're complaining that the database is too slow. I'm thinking ... duh! You're like 3 miles away. This is not an acceptable answer though.
So, I've been trying to rework the DB by unbounding my forms and keeping all my forms loaded throughout the running of the app. I still dont think they are gonna see that big of a performance jump and feel as though I'm wasting my time. I've done a fairly thorough search of methods to make Access run faster, but is there anything that can feasible make a 5-minute loading form load in seconds?!?!?! Perhaps someone can help. Any ideas or suggestions will be greatly appreciated. (^_^)

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[MichaelRed]

hmmmmmmmmmmmmmmm ... mmmmmmmm ...

Well, the first thing which comes to mind is to upgrade the conection. It "Sounds Like" they are trying to use the app over dial up lines (and slow ones at that), so changing their access to DSL or Cable would be a big boost.

The next issue would be to split the db, in the more efficient mode, where ONLY the data resides on your server and the UI/Front End resides on each local desktop. Of course, this can present maintenance issues, as you will need to set up the process to update the front ends -and be sure that ALL uses are at the same rev level of YOUR app. This has been discussed in these fora previously. While it can be a chore to set up, it is something which you should do even w/o the speed/remote users issue.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Jedi420]

Hey,

Thnx for responding. I'm already using that FE/BE architecture with ONLY the data in the Back-End on the server and the front ends on each users PC. I use batch files to revise the front-ends (thnx JeremyNYC), so I'm good there. This is a fairly large company so it takes some time for DCO to get back to us with what kind of connection links this location and the remote one.
I've indexed all that is referenced by queries and forms, turned off the Auto Name Correct, keep all the forms open and hidden until their needed, but I just don't see me getting any significant performance boosts.
I tried explaining that if they wanted something super-fast that would connect multiple geographic locations, they shouldn't use Access, but the thing's made now and it's kinda too late for that now. Any more ideas or anything are very much welcome. Thnx!

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[psemianonymous]

Consider giving them a separate front-end from your local users. Cut out all but the bare minimum of features, like data entry only.

Windows Terminal Server is another option for your remote users--though I have had lots of problems myelf, others have had great success with it. It is, I suppose, the standard solution for remote Access users. My users on a 56K (or less) dialup connection were able to use the database at ... acceptable speeds. The screen updates were slow, but not unacceptably so. To gain the ability to print and grab files from their local hard drive, you'd have to set up Citrix MetaFrame or something along those lines ($$$), but if it's already there, use it.

 

[Bastien]

what about using a web server and making the forms in html (developing an intranet)...then geographics are not the problem and speed is limited to the connections itself...

consider ASP or PHP for quick and easy development...

Bastien

cat, the other other white meat

 

[willir]

Has any one thought of using replication? Admitedly, you open one can of worms to fix another problem but it sure addresses the performance problem.

Richard

 

[ericksoda]

You might also try using stored procedures instead of access queries, if you haven't done that yet.

David

 

[markgrizzle]

If the remote users aren't editing or entering data, then mail the data to them on a schedule?

 

[Jedi420]

Thnx for responding guys. The Replication option sounds tempting if I knew it wouldnt be a hassle. I'm entertaining the Terminal Server idea, but was thinking .... I know it's bad for users to share a Front-End due to corruption and there are two people at the remote site. Would it be OK to have them use the same FE on a Terminal Server, or should I have them somehow use individual copies?

- Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[psemianonymous]

My terminal server is still not great. No backend corruption, but the terminal clients would periodically break, rendering the client unusable for the rest of the day. It turns out that the terminal server's settings MUST disable opportunistic locking. This was key.

Splitting the front-end also helped. It reduced instant lockups when 2+ users used the client, to "occasional" lockups, on a biweekly basis. What I'm saying is that yes, the front-ends helped. But if you're not having problems to begin with...there's no point, is there?

I highly recommend you do it the terminal server way--once it's set up, it's set up. Replication may work as well.


If you want, I'll post my batch file that distributed the citrix front-ends; it's (probably) the same info you need.

 

[evalesthy]

Foolio12: Could you post the batch file for distributing citrix front-ends. I just put an Access app on Citrix and that may come in useful down the road. Thanks.

 

[psemianonymous]

Incriminating comments/REMs removed...(I value anonymity)

rem echo on
@echo off


SET RQTXTFILE=RQ Database.1.15.txt

REM RQ DB auto-update process
REM 07/14/2003 - ******
REM
REM Template for this file taken from [URL unfurl="true"]http://www.abcdataworks.com/Developers/Batch.html[/URL]
REM
REM This copies a new version of the database to the local machine if necessary, then 
REM runs the database.

if "%1"=="CITRIX" goto CITRIX
if "%1"=="LOCAL" goto LOCAL

rem If no parameters are passed in, halt this process and notify the user.
cls
echo -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
echo.
echo     RQ DB start-up
echo.
echo Use this batch file with the proper associated shortcut.  
echo For local (LAN) users, use the "RQ Database" link.  For
echo Citrix MetaFrame/Windows Terminal Server users, use the 
echo "RQ" link.
echo.
echo -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

pause

rem -------------------EXIT POINT-------------------
goto END
rem -------------------EXIT POINT-------------------


:CITRIX
SET RQSRCMDB=U:\rq_citrix_fe.mdb
SET RQLINK=U:\all shortcuts\BATCH - Citrix Start-Up.LNK
SET RQSRCDIR=U:
rem make the directories (no harm if they're already there)
H:
cd \Personal
mkdir "RQDB" >nul
cls
cd "RQDB"

goto RUNDB


:LOCAL
SET RQSRCMDB=K:\RQ\RQ\rq_local_fe.mdb
SET RQDESKTOPLINK=RQ Database.LNK
SET RQLINK=K:\RQ\RQ\All shortcuts\BATCH - Local Start-Up.LNK
SET RQSRCDIR=K:\RQ\RQ
rem make the directories (no harm if they're already there)
C:
cd "\Program Files"
mkdir "RQ DB" >nul
cls
cd "RQ DB"

goto RUNDB



:RUNDB


rem Copy a new front end if the newest version file is not present
if not exist "%RQTXTFILE%" copy "%RQSRCMDB%"
if not exist "%RQTXTFILE%" copy "%RQSRCDIR%%RQTXTFILE%"

rem Copy a new desktop shortcut in case one does not exist.
if "%1"=="CITRIX" goto SKIPDESKTOPSHORTCUTCOPY

if not exist "%USERPROFILE%\Desktop\%RQDESKTOPLINK%" copy "%RQSRCDIR%%RQDESKTOPLINK%" "%USERPROFILE%\Desktop\%RQDESKTOPLINK%"


:SKIPDESKTOPSHORTCUTCOPY

rem Start the shortcut, which will start the database
"%RQLINK%"


:END
rem pause
exit

 

[psemianonymous]

Oh yeah, here are the two shortcuts, of course necessary to the process:

CITRIX shortcut

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" "H:\Personal\RQ DB\rq_citrix_fe.mdb" /wrkgrp U:\icg.mdw

LOCAL shortcut

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\Progra~1\RQDB~1\rq_local_fe.mdb" /wrkgrp "\\FAC_HV_FS1\PROG\RQ\RQ\icg.mdw"

 

[evalesthy]

Thanks - I'll look it over and post back if I have any questions.