How to Gracefully Close Database in Multiuser Environment?

[Rick]

I have an Access 2007 .accdb (testing at the moment) wherein the FE is on a
Terminal Services server (Windows 2003) running in the Access 2007 Run-time
environment and the BE is on another Windows 2003 server.

This app is accessed by many Users and is working well - up to the point of
exiting. I get the error message: 'Execution of this application has been
stopped due to a run-time error. The Application can't continue and will be
shut down.' At which point the User clicks OK and the shutdown proceeds
normally. Is there a better way to do this?

Thanks.

Here is the code:
' Exit the application.
Case conCmdExitApplication
'close open forms
If IsLoaded("frmClients") Then
DoCmd.Close acForm, "frmClients", acSaveYes
End If
'update the Session information
strSQL = "INSERT INTO tblLogOn (LogOnName, LogOnComputer, LogOnDateTime,
LogOffDateTime)" _
& "VALUES ('" & gbUserName & "', '" & gbComputerName & "', " &
Format(gbLogOnDateTime, JetDateTimeFmt) & ", " & Format(Now, JetDateTimeFmt)
& ")"
dbsBITO.Execute strSQL
'CloseCurrentDatabase
rs.Close
Set rs = Nothing
Set con = Nothing
DoCmd.CloseDatabase

 

[Rick]

I have traced the error to the command: DoCmd.Close Database.
Should I just be setting it(the application) to nothing?

 

[Tom van Stiphout]

Even on TS each user should have her own copy of the FE.
-Tom.

 

[Linq Adams via AccessMonster.com]

I agree with Tom! Users sharing a front end has frequently been cited as a
major cause of ongoing corruption as well as other problems. Mt first step
would be a separate FE for each user. Even if it's not the culprit here, it
needs to be done.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Linq Adams via AccessMonster.com]

To close the database, DoCmd.Quit

I have traced the error to the command: DoCmd.Close Database.
Should I just be setting it(the application) to nothing?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Rick]

Thank you very much Linq - that was the fix. Works great.

To me the jury is still out with respect to having to place a separate copy
of the FE in each Users' directory. I have had similar databases running for
years with only one copy of the FE and have never had any corruption issues.
As someone said, it may be the way I write the code which has spared me so
far, but I am going to proceed with my old ways until I take a hit. The BE
is check pointed (shadow copy) every 4 hours and is also backed up daily so
the recovery would not take long if needed.

But thanks for the advice.


.... rick

 

[Tony Toews [MVP]]

DoCmd.Close acForm, "frmClients", acSaveYes

The acSaveYes parm is actually going to save the form. Which you
probably don't want.

"Save Whether to save changes to the object when it's closed. Click
Yes (save the object), No (close the object without saving it), or
Prompt (prompt the user whether or not to save the object). The
default is Prompt. "

Immediately before that I'd use the following code to save the data.

If me.dirty = true then _
docmd.runcmd accmdsaverecord

This will then show the user any problems and allow them to cancel the
close, fix up the data and close the form again.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Rick]

Thank you Tony. I will make that change.