Solved: .ADE file hangs on close in runtime

[Movian]

Hey,
I have an ADE system connecting to a Microsoft SQL express server. The system works perfectly for both the .ADE and the .ACCDR however when run as a .ACCDR when i attempt to close the system. Access Hangs there and stops responding. When i close it when its a .ADE it closes just fine.

Im at a loss on how to start trouble shooting this problem.... any help is appreciated.

[Movian]

Ok managed to get a little more information on the problem it only hangs if the user closes the system with the logon form open.

The only code that should have run is the on open event which is as follows

Private Sub Form_Load()
Dim tmp As Integer
Randomize

Dim myrs As New ADODB.Recordset

myrs.Open "SELECT * FROM Settings", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not IsNull(myrs("SharedFolderLoc")) Then
SharedFolderLoc = myrs("SharedFolderLoc")
Else
myrs("SharedFolderLoc") = "C:\"
SharedFolderLoc = "C:\"
myrs.Update
MsgBox "No shared folder loc found defaulting to C:"
End If

If Not IsNull(myrs.Fields("admincontact")) Then
Admincont = myrs.Fields("AdminContact")
Else
Admincont = ""
End If

myrs.Close

tmp = Int((myrs.RecordCount - 1 + 1) * Rnd + 1)
myrs.Open "SELECT * FROM Tips Where ID = " & CStr(tmp), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Me.Tip = myrs.Fields("text")
myrs.Close
Set myrs = Nothing

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM DefaultLookup WHERE DefaultValue Is Null or Defaultvalue = ''"
DoCmd.SetWarnings True
End Sub
The only thing i could think is if the sql statement is still running.

Any thoughts ?

[hansup]

The only thing i could think is if the sql statement is still running.
The DELETE statement?

Perhaps you're suppressing information by turning SetWarnings off:DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM DefaultLookup WHERE DefaultValue Is Null or Defaultvalue = ''"
DoCmd.SetWarnings True
Try this instead:CurrentProject.Connection.Execute "DELETE FROM DefaultLookup WHERE [DefaultValue] Is Null or [Defaultvalue] = ''"

Also DefaultValue is a reserved word. If you must keep that as your field name, at least enclose it with square brackets to avoid confusing Access.

[Movian]

I bet its the default value, the reason for the hide warnings is to prevent the system from showing the message stating how many records will be deleted....

As this is something that is occurring without the users knowledge (its automatically cleaning up a table). They have no idea what it will refer to and will become scared because its talking about deleting records.

I will try your changes now :) will let you know how it goes.

[hansup]

I bet its the default value, the reason for the hide warnings is to prevent the system from showing the message stating how many records will be deleted....

As this is something that is occurring without the users knowledge (its automatically cleaning up a table). They have no idea what it will refer to and will become scared because its talking about deleting records.
With CurrentProject.Connection.Execute, the users won't see that confirmation message with the number of records to be deleted. Therefore, no need to fiddle with SetWarnings.

[Movian]

Perfect solution!

I really appreciate the help. I was stumped at what the problem could be... until i figured out it was only happening when the log on form was causing the problem.... still odd that the on open event should cause a problem when closing the whole system but not when closing the form to open a different form...

Well it works now so i shan't complain

thanks again much appreciated.