I have an Access VBA program that will run great one day and next it will lockup. I don’t get an error message when it lockup, I just know because when it locks up it just sit with the hour glass and I have to close the db and restart it and it runs fine.
I cannot figure out when it keep doing this, can anyone help me?

[Option Compare Database
Global Date2 As String
Sub ImportGLTrans()
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim IntVar As Long
Dim UserId As String
Dim Date1 As String
Dim Password As String
MsgBox Date2
DoCmd.Maximize
DoCmd.Echo True, "Export GLTRANS "
DoCmd.Hourglass True
DoCmd.SetWarnings False

On Error GoTo ErrorHandler

Set LawsonCn = New ADODB.Connection


LawsonCn.ConnectionString = "Provider=Lawson.LawOLEDBC;Data Source=h6h489.tcn.org;Prompt=Complete"

LawsonCn.CursorLocation = adUseServer
LawsonCn.Open

'EmptyTable ("TBL_GLTrans")
CurrentDb.Execute "DELETE FROM TBL_GLTRANS"

Set rs = New ADODB.Recordset


rs.ActiveConnection = LawsonCn
rs.Open "dme:PROD=PROD&FILE=GLTRANS&FIELD=COMPANY;FISC AL-YEAR;ACCT-PERIOD;CONTROL-GROUP;SYSTEM;JE-TYPE;JE-SEQUENCE;LINE-NBR;OBJ-ID;STATUS;ACCT-UNIT;ACCOUNT;SUB-ACCOUNT;SOURCE-CODE;DATE;REFERENCE;DESCRIPTION;BASE-AMOUNT;UNITS-AMOUNT;POSTING-DATE;ACTIVITY;ACCT-CATEGORY;TRAN-AMOUNT;ORIG-PROGRAM;OPERATOR;RECONCILE;EFFECT-DATE;UPDATE-DATE;APDISTRIB.PO-NUMBER;APDISTRIB.VENDOR;APDISTRIB.INVOICE;APDISTRI B.DESCRIPTION&SELECT=COMPANY%3D07%26UPDATE-DATE%3E12/31/07"
Set rsGLTrans = CurrentDb.OpenRecordset("TBL_GLTRANS", DB_OPEN_DYNASET)
rs.MoveFirst
IntVar = 0
Do While Not rs.EOF
IntVar = IntVar + 1
Date1 = Format(rs.Fields("UPDATE-DATE"), "mm/dd/yyyy")
If Date1 = Date2 Then
rsGLTrans.AddNew
rsGLTrans!COMPANY = rs!COMPANY
rsGLTrans!FISCAL_YEAR = rs.Fields("FISCAL-YEAR")
rsGLTrans!ACCT_PERIOD = rs.Fields("ACCT-PERIOD")
rsGLTrans!CONTROL_GROUP = rs.Fields("CONTROL-GROUP")
rsGLTrans!R_SYSTEM = rs.Fields("SYSTEM")
rsGLTrans!JE_TYPE = rs.Fields("JE-TYPE")
rsGLTrans!JE_SEQUENCE = rs.Fields("JE-SEQUENCE")
rsGLTrans!PO_NUMBER = rs.Fields("APDISTRIB.PO-NUMBER")
rsGLTrans!LINE_NBR = rs.Fields("LINE-NBR")
rsGLTrans!OBJ_ID = rs.Fields("OBJ-ID")
rsGLTrans!R_STATUS = rs.Fields("STATUS")
rsGLTrans!ACCT_UNIT = rs.Fields("ACCT-UNIT")
rsGLTrans!ACCOUNT = rs.Fields("ACCOUNT")
rsGLTrans!SUB_ACCOUNT = rs.Fields("SUB-ACCOUNT")
rsGLTrans!SOURCE_CODE = rs.Fields("SOURCE-CODE")
rsGLTrans!R_DATE = rs.Fields("DATE")
rsGLTrans!R_REFERENCE = rs.Fields("REFERENCE")
rsGLTrans!R_DESCRIPTION = rs.Fields("DESCRIPTION")
rsGLTrans!BASE_AMOUNT = rs.Fields("BASE-AMOUNT")
rsGLTrans!UNITS_AMOUNT = rs.Fields("UNITS-AMOUNT")
rsGLTrans!POSTING_DATE = rs.Fields("POSTING-DATE")
rsGLTrans!ACTIVITY = rs.Fields("ACTIVITY")
rsGLTrans!ACCT_CATEGORY = rs.Fields("ACCT-CATEGORY")
rsGLTrans!TRAN_AMOUNT = rs.Fields("TRAN-AMOUNT")
rsGLTrans!ORIG_PROGRAM = rs.Fields("ORIG-PROGRAM")
rsGLTrans!R_OPERATOR = rs.Fields("OPERATOR")
rsGLTrans!RECONCILE = rs.Fields("RECONCILE")
rsGLTrans!EFFECT_DATE = rs.Fields("EFFECT-DATE")
rsGLTrans!UPDATE_DATE = rs.Fields("UPDATE-DATE")
rsGLTrans!VENDOR = rs.Fields("APDISTRIB.VENDOR")
rsGLTrans!INVOICE = rs.Fields("APDISTRIB.INVOICE")
If rs.Fields("APDISTRIB.DESCRIPTION") > " " Then
rsGLTrans!R_DESCRIPTION = rs.Fields("APDISTRIB.DESCRIPTION")
End If
rsGLTrans.Update
rs.MoveNext
Else: rs.MoveNext
End If
Loop
rs.Close
rsGLTrans.Close

'TerminateLawson
LawsonCn.Close
CurrentDb.Execute "Qry_UpdateTranAmount"
Set LawsonCn = Nothing

MsgBox "Load completed successfully!", vbExclamation, "GLTRANS Load"
DoCmd.Hourglass False
Exit Sub

ErrorHandler:

MsgBox Err.Number & " " & Err.Description & " Please contact Systems Development", vbCritical, "AAL Software"

End Sub]

You might try this:

'DoCmd.SetWarnings False

and allow warnings to be thrown for some events.

You might also change your On Error statement,

On Error Resume Next

Then cut and paste this after major transactions.
Err.Clear
...

If Err <> 0 Then
MsgBox "Error at " & Err.Description, vbInformation
End If
Err.Clear

It looks to me that the problem would be in your connection given how it is acting. It is not timing out when it can't get the connection. Check Err after the connection and see what the value is.

Did that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Mmcdonal,
Thank you for help, I had a feeling it was with the connection but I could not figure it out in this code. I will test the changes you suggested and hopefully this will fix my ongoing little headache with this db.