the right way?

[ducker]

Hi all,
Neat forum, I'm glad I came across it. I've been working in Access databases for 5+ years now. At my current company for 3+ years.

They are running on windows 98, using Access 97. They have multiple databases. Many of which have multiple users accessing them at the same time.

Many of the databases created prior to my arrivial at the company were made via the older wizard, and accessed by multiple people. They caused a whole host of complications. Locked records, corrupt tables/ corrupt databases, users unable to close forms/exit out of the database.

Instead of making multiple front ends for each user, I open only one record from a rst at a time. So mutiple people will not have any record locks at the same time. I understand that this does cause a potential issue with users over writing someone elses changes. But the alternative has caused so many problems it's not funny.

So, I end up having a form for a user to select a record, a procedure for populating a form with the record selected, procedure for editing/saving, etc...

Is this a valid work around? How else would people handle this situation? Multiple Front Ends all pointing to a single Back end?

I have a couple more questions to bounce off of people after I get this one nailed down

It's like an uphill battle here; too many users who "know access" creating stuff that's an over glorified excell spreadsheat.

Thanks!


-Mike

 

[ghudson]

Is this a valid work around? How else would people handle this situation? Multiple Front Ends all pointing to a single Back end?

Yes, you want to split the db and install a front end onto each users hard drive and all fronts ends are linked to the one back end which has all of the shared tables.

 

[ducker]

Yes, you want to split the db and install a front end onto each users hard drive and all fronts ends are linked to the one back end which has all of the shared tables.

This seems like a very cumbersome way to potentially allow anywhere from 20-30 users to access data.

So you have 20-30 identicle copies of a database pointing / Linked to one "data" (or backend) database.

This opens up so many additional hurdles. In our sintuation, we have maybe 4 different databased that more then 20 people access.

So I would need to create 80 copies of a FE database? I guess the best way to describe what I'm doing is:

Creating a quasi FE in each form. by making each form one stand alone instance. and storing the data in the same database.

and this is very very wrong?


-Mike

 

[ghudson]

You create only one front end for each application and distribute it to each [20] users computer. Many developers do it every day yet for many more users and many more applications.

 

[monkeytunes]

You don't create 80 versions of the Front End. You put the Front End on your server, and tell your co-workers to download it. Your Back End is on the server as well (hopefully in a different, hidden place!). Whenever you make changes to the Front End, you upload it to the server, and tell everyone to download it. (There are ways to force the updates as well, through VB coding to check for new versions, or you could simply move the Back End, thereby breaking the old Front End links, and point your new Front End to it, so everyone HAS to download it.) Make sure you disable menu-options, the db window, etc., so the users only get your forms at startup.

If you have users who insist they "know access", then let them create their own, locally stored Front Ends, which you will be happy to link for them on the rare occasions they need it. This way, you won't have to deal with their poor design, they won't muck up your Official Front end, their queries/reports/forms won't be deleted every time you tell them to download a new Front End, and you can check up on them when they need you to link the Back End or whatever.

And as far as your "quasi-FE" - there's no reason why you can't have multiple Back Ends linked to one Front End. I often use a Reference Back End for Front Ends databases, for unchanging things like tblCountries or tblStates. This means each Front End will have the applicable Back End, plus links to this Reference Back End.

 

[KenHigg]

Ducker, An intermediate solution would be to do the split and place them both on the server and run the FE off the server. Then you don't have to manage all the copies of the FE. I would however try to develop an automated distribution system such as Pat has described...

Ken

 

[ducker]

Hm... wow. well I've written a ton of code for nothing. If I can simply split the FE and the BE and have users not have any conflicts.

I understand what Pat was suggesting. I'll just have to do a bit of research and thought in how I can overwrite a local copy of the FE and automate it as much as possible within AC97.

I'm guessing that this doesn't solve the possibility of conflicts/overwriting of data by multiple users.

--
Previously what I would do would be kick everyone out of the Database and then replace any updated tables/forms/etc. Many of the databases are at least partially locked down (no database window visible).

If you are currently allowing people to update objects (A97 allowed this but A2K and newer do not) while the db is opened by other users, this could easily explain your corruption problems.

I think many users are updaing objects unbeknowst to them and this is what is causing conflicts. maybe due to AC97's fault? I don't explicity have users login, they all use the databases as "admin".

So simply adjusting these databases to a FE/BE configuration should alleviate these corruption problems?

Many of the databases I haven't gotten my hands on are very basic. Those that I've spent a lot of time in thus far I've completely reworked how Access handles records (as Pat mentioned; I pretty much explicity told Access how to open and close records instead of allowing Access to do the work and simply split it in to a FE/BE configuration).

gah... looks like I've done a lot of work over the years for nada. I'm not sure if I remember how to create a BOUND form anymore!

-Mike

 

[KenHigg]

Duck - I sense there is still some ambiguity in the nature of the issues you're trying to resolve.

The split front end solution is a preferrable method but I doubt splitting the db in itself will do little to address the write conflicts. Also, I haven't seen anything in your posts to this point that would convince me you are not using bound forms as I haven't read where you state if you are using DAO or ADO, etc. to get to the data. Can you elaborate?

Splitting a db and using ADO/DAO normally are considered two seperate topics / methodolgies...

ken

 

[ducker]

Sorry I'll try to clear it up. I apologize, I've been here for 3 years and I fully realize that how I pose my questions and the structure of my work is not standard; as the company itself is hardly standard.

properties of the "EDIT" button

and the code that's called

Public Function open_populate_edit(WOID)
    Dim dbs As Database
    Dim rst As Recordset
    Dim strsql As String
    
Set dbs = CurrentDb

strsql = "Select * from [tbl_wo] " & " where [tbl_WO].WOID = " & WOID
    
Set rst = dbs.OpenRecordset(strsql)
    
With [Forms]![WO_Entry_Form_Edit]
    ![WOID] = rst.Fields("WOID")
    ![Building] = rst.Fields("Building")
    ![Work Location] = rst.Fields("Work Location")
    ![Division] = rst.Fields("Division")
    ![BldgPM] = rst.Fields("BldgPM")
    ![Client Name] = rst.Fields("Client Name")
    ![Client Contact] = rst.Fields("Client Contact")
    ![Client Phone] = rst.Fields("Client Phone")
    ![Client other info] = rst.Fields("Client Other Info")
    ![WO Client Type] = rst.Fields("WO Client Type")
    ![WO type] = rst.Fields("WO Type")
    ![WO Sub-Type] = rst.Fields("WO Sub-Type")
    ![Description] = rst.Fields("Description")
    ![Insurance] = rst.Fields("Insurance")
    ![Dumpster] = rst.Fields("Dumpster")
    ![Memo] = rst.Fields("Memo")
    ![WO Create Date] = rst.Fields("WO Create Date")
    ![WO create BY] = rst.Fields("WO Create BY")
    ![WO Due Options] = rst.Fields("WO Due Options")
    ![WO Due Date] = rst.Fields("WO Due Date")
    ![WO Special Instr] = rst.Fields("WO Special Instr")
    ![Access Date] = rst.Fields("Access Date")
    ![WO Issues Date] = rst.Fields("WO Issues Date")
    ![Designer] = rst.Fields("Designer")
    ![TeamLeader] = rst.Fields("TeamLeader")
    ![Permit Needed] = rst.Fields("Permit Needed")
    ![Permit Status] = rst.Fields("Permit Status")
    ![Permit Issue Date] = rst.Fields("Permit Issue Date")
    ![Permit Number] = rst.Fields("Permit Number")
    ![Attachment Path] = rst.Fields("Attachment Path")
    ![AWA Path] = rst.Fields("AWA Path")
    ![Pricing Info] = rst.Fields("Pricing Info")
    ![Pricing Options] = rst.Fields("Pricing Options")
    ![Approval LSG] = rst.Fields("Approval LSG")
    ![Approval LSG date] = rst.Fields("Approval LSG date")
    ![Approval OPS] = rst.Fields("Approval OPS")
    ![Approval OPS date] = rst.Fields("Approval OPS date")
    ![Approval DSG] = rst.Fields("Approval DSG")
    ![Approval DSG date] = rst.Fields("Approval DSG date")
    ![Approval CON] = rst.Fields("Approval CON")
    ![Approval CON date] = rst.Fields("Approval CON date")
    ![Approval FLD] = rst.Fields("Approval FLD")
    ![Approval FLD date] = rst.Fields("Approval FLD date")
    ![WO Status] = rst.Fields("WO Status")
    ![WO Progress] = rst.Fields("WO Progress")
    ![WO Comment] = rst.Fields("WO Comment")
    ![WO Complete Date] = rst.Fields("WO Complete Date")
    ![As Built Drawings] = rst.Fields("As Built Drawings")
    ![Related WO] = rst.Fields("Related WO")
    ![LastUpdate] = rst.Fields("LastUpdate")
End With
End Function

A user would then be allowed to make changes and then hits a "SAVE" button when then simply updates the record with the values in the form.

The whole purpose of all these steps it to create an environment that keeps users away from potentially stepping on each others toes or corrupting/locking each other up.

Now with the "older" databases, a simple forms based on queries (or even tables if I haven't noticed it yet).

I hope that clarifies a bit.

-Mike

ps. perhaps it's simply the stupid AC97 and accidently modifying objects that is causing me to do it this way... taking way all possible standard Access buttons so that the only buttons a user clicks are ones I created. and thus there is no potential object modification.

 

[KenHigg]

Hum...

I have some ideas but rather than cloud the issues further, maybe it's best to wait and see what some of the more experienced users suggest.

Ken

 

[ducker]

Good to know Pat. One other coworker that does development with me said she has no problem bringing home snippets of databases and working then in a Access XP environment. Pethaps it's simply that with the shear amount of code I have that something will most likely bomb out.

I believe I've read somewhere that Microsoft suggested this method of data access. I'm going to look through my notes and references.

Aside from updating a Fe client I don't see the advantages of really splitting up many of my databases as the databases have been designed specifically not to conflict with each other. This isn't to say I'm not going to split them all up. I will be, but I'm just curious as to the advantages.

Ken, I'd love to hear some of your ideas.

Love the discussion,
-Mike

 

[KenHigg]

Duck - I have several db's that have migrated through several iterations starting with Access 2 and have not encounter any issues of the scale you seem to be having with write conflicts and corruption.

The basic set up is I put the table in one mdb and everything else in another mdb. Then I put both of the on the sever and email the end users a short cut which the in turn copy to their desk top. Now I know some feel it best to put the front end on the workstation but my situation won't allow this since a night push may delete any files that haven't been registered, blah, blah with our IT dept. All this issue is a whole other story...

Anyway, these dbs all use bound forms etc. and work very well. And they have numerous concurrent users.

Suggestion 1. Pick one of your less complex db's and set it up with a FE/BE and bound forms and put it in production. Then see what happens...

Suggestion 2. If the native record lock methods still don't work good enough, set your db up with some kind of scratch built, table based record locking mechanism. To make an example overly simple: Say all users can view the records. Then when a user elects to edit one, when they click on a button associated with the particular record, it sets a value in the record, say a 'Lock' fld. Then if someone else elects to open/edit the record it must check to see if this value will allow it. Then when they save the record, it resets the value and unlocks it...

All of this is kind of off the top of my head and I really haven't tried it, and quite frankly sounds a little clunky, but maybe you get the idea...

Ken

 

[ducker]

Yea I thought of doing something like that before Ken. The original reason I hesitated in doing that was if a user crashed while in "edit" mode, it would leave that record locked.

Because the user base is all using Windows 98, we tend to see a lot of lock ups/crashes in just their OS. We do not have individual user email boxes (thus no exchange server), so I wouldn't be able to use email as a method of distribution.

To put it in perspective, they look to me to build nice sand castles with out a bucket and dry sand

I'm going to take a bound DB that I know I've had locking problems in the past and split it up in to a Fe/Be configuration. When I say "locking problems" I mean when Access feels that someone has locked the database/form/control/etc... not just a specific record set.

Hopefuly this will work.

After I get all this organized I can get on to the crux of my delima... implementing a calendar system with Access 97.

-Mike

 

[ghudson]

Because the user base is all using Windows 98

That reminds me of the days when we used Windows 98 and all the problems my users had. It was mostly do to the hardware the old computers had back then. You could not add more that 128 megs of RAM to Windows 98 [yeah I know some will disagree but we tried it without success]. Once we upgraded to Windows XP and the better hardware that came with a XP box all my "weird and unusual" problems disappeared. We were still using Access 97 at the time so it was definitely the Windows XP and the better hardware that eliminated the problems I used to face. The older computers [OS software and hardware] just could not handle the load the db's were pushing with large amounts of data through a networked database.

Ensure the users are correctly exiting [closing] your databases will also prevent database corruption. Ctrl+Alt+Del can be a database killer!

 

[Brian1960]

Advice in general

I have been reading this thread and no one seems to notice that all the users log in as Admin! This is a bit odd and may be causing some of your problems. I have a distributed (in 8 countries) system which has a BE, a FE and a workgroup file sitting in the same directory as the BE. The FE can sit either in the workstation and be linked across the network or be run from the server.

At a different client they have the FE on their PCs and a small Bat file which they can click to copy the latest version to their PCs. Their "Link" uses the network path.

Not sure if this is the best method but it works and is reasonably trouble free.

The other thing is how often do you compact and repair? A2K allows this automatcally for the FE but the back end needs to be done each week by a small Db built do it and it runs on a timer. See Candice for this one!!!

 

[ducker]

The other thing is how often do you compact and repair? A2K allows this automatcally for the FE but the back end needs to be done each week by a small Db built do it and it runs on a timer. See Candice for this one!!!

Compact - nightly via a batch file.
Repair - when needed - as a compact will fail over night, I'll know and then be able to repair the database the following day.

-Mike

 

[ducker]

You will have issues if you ever need to convert the db's to a newer version of Access since you have so much DAO code. You might want to start now to disambiguate (don't ya' just love that word?) the DAO objects as you make changes to the db's.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

This will avoid certain issues for versions of Access where ADO is the default data access method.

What type of issues Pat? One of the other developers has done some development of code in 2002 and simply brought it in to 97 w/o a problem.

When you state that I might want to start to "disambiguate" the objects. I'm assuming you mean by switching the forms over to bound forms instead?

gah... that's going to be SO much work.

Again thanks so much for all the feed back thus far!

-Mike

 

[ducker]

I gave you an example of what I ment by disambiguate. Change
Dim dbs As Database
Dim rst As Recordset
to
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Thanks Pat. Sorry I didn't realize that was the example.

-Mike