Perplexed, getting access 97 setup on a server for 50 Users

[m198redleg]

The question I have is how to setup Access 97 on a server(a), where the field technicians(50) can access the table for input only, N the District Managers(2) can access the table with read only(b). I have written this Access97 dBase which has worked very well as a stand alone system. The Manager enjoys the ability to accepting the email from the field. All that was needed was appending the data to the main table and it was good to go. Management has tasked me to expand it to their new Server. They want the field to input directly onto the main table and the Manager drops out of one step. I have read about Client-Side Cursor, adLockReadOnly, adOpenDynamic, adLockPessimistic, adLockOptimistic. My problem my be that I am reading to much, now. Most people here may say, "I can answer that question!"
I have one main table tblHectar, three forms feeding that table: frmOSHE, frmWAV and frmREC(4 different feeds - same theme;However). From what I have read I should have the forms on the users Laptops and the Table on the Server. I tried that here, but when I opened it up with my Laptop and Desktop, I was glad I had a backup. Gave me Dr. Watson, moment it opened. I have validated Opens in shared with No locks - oh - didn't to remove Open databases using record level lock. Tables are Run permission "User" at this time.
I don't know anyone else to ask, but the people which have helped me the most, in the past.

 

[psemianonymous]

There's no super-technical hidden method to split your application: you first copy your file. Rename one of the files. Let's say you renamed the backend so now you have:

"db.mdb" AND
"db_be.mdb"

Open the backend (db_be.mdb). Delete all queries, forms, reports, macros, modules. You now have the backend.

Open the frontend (db.mdb). Delete the tables. By the way, you should have backups of all this. Moving on. Go to File->Get External Data->Link tables. Find "db_be.mdb" and link every table you find. Close the frontend.

Now open the frontend. You should have a fully-working split database.


Table links to Access tables work almost exactly like "in-file" tables stored in the frontend. There are a few exceptions, like you can't open a table-type recordset, and other gotchas that don't matter and can be fixed with some quick "does this work? No? Change to SELECT * FROM tablename" fixes.

NOTE: table links point to a "STATIC" directory, so no matter where you copy the frontend, it always points to "X:\FULLDIR\db_be.mdb". So this is something you should take note of.


Once you have a split frontend/backend, and they work, get a good system of automatically updating everyone's machine. Jeremy Wallace (JeremyNYC) has a batch-file based method (

http://www.abcdataworks.com/);

Tony Toews has a small EXE that updates and launches your frontend (

http://www.granite.ab.ca/access/autofe.htm).

They both work.


You will see a performance gain, assuming all this works.


Pete

PS--Once you have split the database, if it's still too slow, there are other solutions...

 

[Quehay]

This is really good advice (but first and in bold: By the way, you should have backups of all this. ).

Just to unpack a bit more if you're new to this: The front end is what users have on their machine, and back end is the common data file stored on the server. Because Access is a file-sharing, rather than true client-server database each person will be working with a copy of the data file that gets brought over to their machine for use while open.

The ability to read,update,add,delete, etc. comes from user permissions, which are best given once to a role and then the users are given permission to use that role. The onus is on you here to do your homework--this stuff is in the help.

The locking and concurrency stuff determines what happens when two or more people who have permission to change data attempt to do so concurrently.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[m198redleg]

Folks I want to thank you for get back so soon, sorry I didn't. I went for a frost mug watched a movie and got some sleep. I woke up and thought about font end & back end and when you spoke of it and the references "foolio12" you confirmed it and since tried it. works great. ?? Does this mean I will pretty much have to revert to SQL for queries and such to generate reports ...Table links to Access tables work almost exactly like "in-file" tables stored in the frontend. There are a few exceptions, like you can't open a table-type recordset, and other gotchas that don't matter and can be fixed with some quick "does this work? No? Change to SELECT * FROM tablename" fixes. It won't bother me, I have developed a knack for it, by now.
"Quehay" you brought up another of my severe fears, that is when the field techs start inputing data into the dBase from their front ends (even if just two do it at the same time accidently) the table will not know what to save, or in what record or I could get a hodgpoge of junk store in the table unless I put locks. That is what you meant by
The locking and concurrency stuff determines what happens when two or more people who have permission to change data attempt to do so concurrently am I right?
OR do I see Access allowing multipule users being offered this luxury without these lock and gizmos because of "Access is a file-sharing, rather than true client-server database each person will be working with a copy of the data file that gets brought over to their machine for use while open." I am trying to digest Jeremy Wallace's information on security to keep oout techs with an attitude, but I want to say thanks. very much so and I appreciate your help. It got me out of a brain log jam, lack of a better phrase.

 

[Quehay]

<<Does this mean I will pretty much have to revert to SQL for queries and such to generate reports?>>

Naw...with linked tables everthing should work transparently, as if the tables were still in the same file (with linked Access tables that is).

<<when the field techs start inputing data into the dBase from their front ends (even if just two do it at the same time accidently) the table will not know what to save, or in what record or I could get a hodgpoge of junk store in the table unless I put locks>>

The app knows where to send the data, no worries in that regard, as long as your forms are bound to the correct tables/fields to begin with.

The locking is analogous to someone taking a log book off a counter to write on a given line of a page, if you will.

1. If they start a totally new line, then there's no conflict.

2. If they want to edit an existing line and someone else has walked over and is attempting to write on the same line there's a potential conflict depending on locking scheme.

In general set locking to optimistic (&quot;no locks&quot. This says &quot;Access lock this record only in the instant that someone sends an update over the network to the common file.&quot; Usually this is just fine unless you've got a really busy data entry pool (and this is often an indicator that Access is out of its depth too...). Then it's a question of updates occurring hopefully not in the same half second. If you set locking on other settings it takes more network resources and will slow things down in general.

You're probably not going to have problems here so I wouldn't worry about too much depth in this area yet (there are a lot of fine granularity things to look at if it's really an issue, and there are also some known glitches in Access record locking depending on the way in which db is opened--at least in Access 2K).

The permissions determine whether a person may do any number of things, but this only works if you've got full Access security turned on (do yourself a favor and avoid this until you're sure you know all the nuances and ramifications).

If you're worried about wise-guys making &quot;improvements&quot; to your app, put restrictions on the NT folder that holds the back end (ask your SysAdmin for help here), and distribute an MDE front end to users--this will only allow changes to queries, and then only is the bypass key is used (of course you also need to lock down the general user options to prevent design view, etc.). You can also remove the bypass key option in code and put a hidden toggle on it for only you to know about (ask if this is needed).

Read about MDE's and make a backup copy of front end before creating MDE--Acc97 gets rid of your editable .mdb front end if you're not careful.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[m198redleg]

I apologize for not getting back with you all earlier, but meeting and such. I have implemented your suggestions. Thank you. Quehay and Foolio12. You are true examples of a term my Dad used to say to people. &quot;You are both a cool drink of water on a hot day.&quot; Thank you very much.
I may have to call back again, but if I do, with MVPs of your caliber, I know I am helped with the best.
Everytime you get something working good here they want something more. It reminds me of human nature - always wanting more.
Again Thanks.