Setup Question - Expert Needed

[LincolnMarkV]

Here is my issue of the day...

I have a BE/FE Setup using Access 2000. The front end is a MDE and the back end is MDB. I have implemented many of the great suggestions found here for security and crash prevention. I work for a non-profit and funds are tight to say the least. They want a full-scale commercial level product without opening their pocketbook. I do not have access to a server and I have an additional workstation on the network acting as a server. The system is NT4.0.

The problem with this is that only ten remote users can log in at once. I have a staff of 20-25, big problem. I have implemented a feature to AutoLogout folks who are inactive for the DB and tweaked the registry on my "server" to disconnect inactive users.

This database is accessed over the network by two remote locations and the mainoffice. The remote machines are terribly slow and need to be improved.

Any advice on the speed for the remote locations and the # of users would be greatly appriciated. Also, should I upgrade to MySQL or any other products?

Thanks,
Dave

PS...Thanks to all the people who post. I have taught myself through the info I have learned here. The books and MS website are pointless.

 

[AlexMidd]

I was going to say that changing the backend to MySQL might help with netowrk traffic by reducing the load that Access places on it with its inefficient use of queries etc. Can't offer much other than that, as I am not a networking expert I'm afraid.

P.S. Just wanetd to say, it sounds like you are doing an excellent job maximising the scant resources you have.

Have fun! )

Alex Middleton

 

[LincolnMarkV]

I'm expected to build them a house and they give me a screwdriver and a toothbrush. Welcome to the non-profit world. Fortunately, I report to a great director that appriciates the work.

Thanks for the advice.

I have been researching the MySQL transition.

Anyone else???

 

[fneily]

How about a Windows Citrix server? That way you buy just one more computer, buy the software, place the database on the server and then your users can remotely access the db by logging onto the server whenever they want. It's cheap, at least.

 

[drkhelmt]

My first thought would be to turn that "server" into a linux box. Its free and can do enterprise level stuff.

I think, but not sure, that your 10 user limit is a function of Windows and not Access.

As for upgrading to MySQL, with only 25 users, I don't think that is necessary. Also if funds are non existant, consider PosgreSQL.

Hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[LincolnMarkV]

Thanks everyone!!!

As for the Windows Citrix server, can I install it directly onto the workstation that I am currently using as a "Server". If so, can you paste a link to where I can download it.

Again thanks for all the help.

Dave

 

[psemianonymous]

Nothing's free. Citrix is most certainly not free. Buying another server with Windows Terminal Services (equivalent Citrix product) is also not free, but it's the best option you have. If they were willing to pay money, I'd recommend you make them run Access through a Windows Terminal Server connection; it's by far the least hassle and best performance.

MySQL will probably improve performance, but over a WAN no thick client database app will do well, so remember it's not a magic bullet.


My suggestion for you is to change the frontend--there are a million performance-saving tricks, but they boil down to:

* Ensure you're using indexes everywhere (this gives huge boosts)

* Go from "directly bound to every record in form" to "one record at a time" forms. Instead of pulling 1000 records you pull 1.

* Only do lookups when the user has typed at least 3 letters. Requires heavy coding; thankfully someone on comp.databases.ms-access has already done the heavy coding. (Search the newsgroup by following the link in my signature)

* Use temp tables for performance--I'm specifically thinking of times when you run complex reports off of several queries and subqueries.


Changing your frontend code is the only 'free' option to improve performance, besides porting your application to the web (which isn't easy). And speaking of 'not easy', don't listen to the folks suggesting you switch to MySQL, unless they claim to have done this sort of thing before (because they probably haven't). Porting to MySQL is not easy.

 

[psemianonymous]

Sorry, I left out my signature with the comp.databases.ms-access link. Attached below:

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[LincolnMarkV]

I just found some articles about using XP and hacking the registry to allow up to 39 concurrent remote connections. Has anyone else heard of this? It should the 10 user issue since I know we won't have 39 employees for years.

We are moving to a new building where I WILL have a server and my own network. I work for one gov't agency contracting to a City Gov't office so they red tape is unbelievable.

Thanks for the help. The advise has really advanced my career.

Dave

 

[psemianonymous]

Don't try to find technical workarounds to fix business problems. I think it's a license violation anyway (the Windows server products sell "client access licenses" so that limitation is probably there for a license-related reason).

Anyway, that's just my suggestion. Have a nice day.

 

[LincolnMarkV]

Good advice

 

[MichaelRed]

To the best of my knowledge, there is no win / access limitation on the number of concurrent users of an Ms. Access database. I have used Ms. A. with a potential of 75 users and commonly had 15 to 25 simeltaneous users w/o log-on issues / lockouts. I am currently programming on a system which often includes more than 10 concurrent users. If there is an implied limit, it would be in the "server" system related to memory usage, not an artificial numerical constraint. Performance issues for a Ms. A. (Jet) db with only the ten to twenty-five users are not worth the time/expense/hassle of change. Local users will not experience any real differences. Remote users will experience hte delays associated with the connection. If they are on a connection which offers the same speed as your "local" network they will not suffer delays. If you are not 'in-charge' of their connections you should not be tasked with the speed issue. Takl to the 'boss' and get them to understand this. REmote users need to discuss their needs with their departments / groups. IOf they really need the access, they really need to set up their part of the system to accomodate this.




MichaelRed

 

[LincolnMarkV]

We are moving to a new building where we will all be on the same network with improved speeds. This will solve the speed issue, because my DB at the local site is fast.

The 10 users I was speaking of is a limitation of Windows NT Workstation. Each user is making a remote connection into an additional workstation at my desk that is used only to house the Back End. My problm is not with Access. I am basically trying to make a workstation act as a server.