Did I do this right - Closing Recordset 1

[AKMonkeyboy]

I currently am using a Form based on "TblCustomers"

In the "On Close" event I put the following to close my recordset (I was having trouble with data corruption)

-------
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("TblCustomers&quot

rst.Close

Set rst = Nothing

-------

Just wanted to make sure that's right - this corruption thing is driving me crazy!

 

[cmmrfrds]

What you have is not doing anything productive that I can see, unless you are trying to create a new recordset and close it immediately. Access will take care of opening and closing the recordset that the Form is based on. You need to look some place else for the problem with corruption.

Corruption.
Is the application working off of a network drive?
Is the application front end and back end? Network or local drive.
How many users on the app?
What version of Access?
Have you isolated when the corruption happens?

 

[AKMonkeyboy]

Sorry for the lack of information there.

Access 2000 - yes it's on a network drive.

Generally there won't be more than 3 or 4 users.

I have narrowed the corruption down to one table. It seems to happen when the user edits a record, then closes the form. He then reopens the form and edits it again. Almost instantly the record goes "chinese". At which point he comes whining to me and I have to fix it. I have read numerous places that it's best to "explicitly close" your recordsets. But I haven't the experience yet to know for sure if I am doing it right.

 

[cmmrfrds]

This is true "(that it's best to "explicitly close" )", if you actually opened a recordset as per your example. The Forms recordset is opened and closed by Access, which is not the recordset in your example. So, in effect, all you have done is created another recordset and opened it and closed it.

Does the user have all the necessary write permissions to the network drive.

Try making a front end copy of the app and attach the tables from the network drive. The way you have it set up you are at the mercy of how reliable the network is. The app will also run faster from the users PC.

 

[AKMonkeyboy]

I see what you are saying about the closing issue. As I read up on the stuff it makes more sense.

I'm a little confused as to front end and back end issue. If I create a separate database with Forms, etc. that would need to be stored on individaul PC's, correct?

That would mean any changes I make to this portion would need recreated over multiple PC's - correct? If I store this file on the network I'm back to the mercy issue.

Thanks for your time.

Adam

 

[cmmrfrds]

You are right the front end stored on individual PC's is a pain for maintenance, but for 4 users it is not too bad. Another possibility is terminal services if the client PC's are windows 2000 and the mdb can be stored on Windows 2000 server. That way only the keystrokes go over the network and network corruption is not much of an issue. In your situation I would put the front end on the individual PC's.

 

[WildHare]

My .02 cents worth:

1) I've found that Access handles multiple users MUCH more cleanly and with far fewer corruption problems if I base forms on QUERIES that extract the relevant data, rather than on the base table itself. With base tables, you run into problems synchronizing updates across all those network links back and forth, that appear to be minimized using query recordsets instead. Don't know why, don't know how, (don't CARE why/how...) but it seems to be the way JET likes to work.


2) Front Ends do NOT have to be stored on everybody's individual PC - if that's too much of a hassle, just create ONE front end database, stick it on the network server too, and let everyone link to it - a copy will be scooted down the pipe to each user as they open the file (it may take a second or two, but in the long run, what the hell...) but it makes maintenance easier in some circumstances.

I've created more than 20 multi-user Access apps over the last half-dozen years, on local nets and even WANS, with A95, A97, and A2K, and can't ever remember having to actually resort to all that recordset open/close nonsense except in one or two bizarre situations.

Life is complicated enough. Don't make it more so unless you absolutely have to.

Jim

How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Rolliee]

aMen wildhare

 

[AKMonkeyboy]

Thanks everyone. All suggestions are taken and considered with the utmost degree of appreciation (how's that for gratitude?).

I will try the simple solutions first - I love to K.I.S.S. and move on from there. Being self taught in all of this is much easier when you have all of this help.

Thanks again.

Monkey