About Ready to Give Up on Access

[SteelersFan]

I work at a Phamaceutical company in the US (based in London) and have a database that tracks atypical events. It has normally about 10 concurrent users and continually (once a week sometimes more sometimes less) corrupts. I have spent a lot of time validating the database writing the user requirements, functioanal and design specs as well as a 150 page IOPQ.

It is a split database with both front and back ends on a file server. I know this is not the best way to do it but the database is frequently updated with requests for new reports etc and I am not sure how to push down the front ends to all the use pcs. The database is about 40 mb.

I like the database. It is fully audit trailed and does what I need it to do, but the corruption thing is driving me batty. I have the jetutils with the viewer to see who is in the db (by pc name) and then I cross reference that to the person in the db and call them to get out. It is a real pain in the arse and I am just about ready to agree with the IT guys that Access is not a multi user database.

Should I scrap Access and go to SQL Server?

 

[DALIEN51]

In The Usa?

Ive always wanted to visit the states so pay me to come fix it!!!!

Failing that one thing I find that usually helps with multi user access environments is NEVER bind forms. Use VBA to write records back to the database once the code has verified the data is ok. I have wrote a numbe of Access applications for very busy help desks with multiple users keying in lots of call information simultaneously and unbinding forms has always solved the problems. Aslo, how about creating a blank front end and a blank back end database and re-importing the objects into them? This usually smooths out any bugs.

Good Luck!
(Oh how I dream of the USA)

 

[SteelersFan]

Thanks for the response.

My forms are linked to queries. What do you mean by 'un binding them'?

The USA is a great place (except the hicks in the south) and of course our dimwitted leader George Bush.

 

[The_Doc_Man]

Hey, be nice to us dumb Southern hicks. Our necks might be red, but some of us DON'T happen to think of 5th grade as the best 5 years of our lives. (With apologies to Jeff Foxworthy.)

"Unbinding a form" means that you have no "obvious" recordsource name for the form. It means that the controls on the form don't have "obvious" controlsource field names. They are just isolated data recipient elements, dangling out there, all prettied up and no place to go. But that might be all you really need.

If the ONLY thing you ever do from this putative form is data entry, doing it by explicit recordset operations keeps the records exposed to corruption for the shortest possible time. So here is the high-level overview.

Just go into design view and create a form. Don't use the wizards. Don't base it on a table or query. All you need is your controls toolbar. Populate the form with controls representing everything you normally capture. Put validation code all over it. Formatting validation, range validation, etc. etc. - put each code-based validation step in the field's LostFocus routine if you need to validate something by code inspection. Other validation rules like maximum size and such are available as properties of the controls themselves. Some controls do not require validation rules. Like Yes/No data corresponds to Check Boxes and your choices are ... let me see ... checked and not checked. What's to validate?

Now put a button on the form that says Update. Under the Click routine, open the recordset, do an .AddNew, populate the fields of the recordset from the controls on the form, do an .Update, close the recordset. After that, make the Update routine call a specialized Form-Erase routine (that you must write yourself) to make the form ready for the next data entry.

Put a second button on the form that says Cancel. Under the Click routine, do not open the recordset at all. Just call the same Form-Erase routine. Maybe before you do that erasure, toss in a message box with OK/Cancel or Yes/No option to confirm that you want to erase the form without saving any data.

If you need to edit, it is tricker but still possible. You can open the recordset to do searches, grab the data you want, and close the recordset. Then, when the Update button is clicked, you can open the recordset a second time, update the individual fields of the recordset, do the .Update again, and close the recordset.

It takes a bit of work to do this, and no foolin' when I say that it ain't easy (though much of it is merely tedious, not really hard to write), but it minimizes the time you are exposed to data corruption by system crashes, network hiccups, and other nastiness. The shorter the time something is open, the less likely it is to get corrupted.

 

[Pat Hartman]

Should I scrap Access and go to SQL Server?

You don't need to scrap Access to move to SQL server. That is one of the beauties of Access. All you have to do is move the DATA to SQL Server and you can leave your app as is However, yes there is always a however, you may want to make modifications for the sake of efficiency. For example, all your forms/reports should be bound to queries that contain where clauses. The where clauses minimize the data that needs to be transported over the network.

Search here for corruption to see other posts on the topic. They pretty much come down to a few area:
1. memo fields - I don't know why they cause problems but they do. You can get around them by isolating the memo fields in their own table so they are not stored with the other data. You can rejoin to them in a query so you shouldn't have to change anything but your RecordSource queries.
2. Improper shutdown of Access. This happens in a variety of ways - network hiccups, client PC lockups, user pressing the off switch instead of a proper shutdown.

 

[John.Woody]

Pushing the FE to your users is quite straight forward. Create a folder on each workstation and put a copy of the FE in there. Use a shortcut to allow them to open it. As for updating the FE keep a clean copy of the db in a folder on the server, create a dos batch file copy clean file FEon workstation and put it in the startup folder. Every time they reboot they get a new FE so all you do is update the FE on the server sit back and tell them to restart.

HTH
John

 

[boblarson]

I have a database that I've created that I chose to use a frontend on every computer. So, to make sure that they are always up to date, I made it so that the program checks the backend to see what frontend version the version should be and if it doesn't match it gives the user a message that their frontend is out of date and it will now close and reopen.

What happens behind the scenes is that a batch file will run copying the new frontend from the network location where I put it when it is ready to deploy. It will copy it to the user's folder, which is named the same on every computer. The batch file also reopens the frontend, which is now the newer version.

It works really well and ensures that the user has the latest version.

 

[pafcpilgrim]

I think what Pat says is a very valid point, so just want to expand on it more. If you think of the amount of data that is possibly transfering between a network be it local or wide if you allow a user/s to open a bound form up and display all the records and then let them search it or edit it that would cause possible problems down the line.

Restrict the amount of data a user can view by having a form where you get them to first find what they are looking for and you can do this in many ways, that way if you bind a query with a where clause to a form it will only produce the record they are after thus not transfering all the data to them before filtering.

Don't forget the data on the back end could be massive so you don't want a user getting a copy of a recordset when that recordset could be huge thus putting a strain on the network. Also SQL Server does not come with a tool to create an interface so you would still need Access or something similar to interact with the data.