Code causes error 2486 in access 2000, IPF in 2002

[ST]

I have an Access 2000 database application which was originally created on a
Windows 2000 pro computer using Office xp developer and installed in on a
Windows 98 machine. I originally installed Office 2000 and installed the
database using the setup created by the Office xp developer packaging wizard.
Everything was working fine until someone accidentally uninstalled Access
2000 to make room on the drive not realizing the application would no longer
work. I temporarily reinstalled the application using the setup with the
access 2002 runtime so it would work until we upgraded the hard drive.

One of the forms takes data from input boxes, runs some SQL and a couple
update queries and opens another form -

DoCmd.OpenQuery "UpdateHr"
DoCmd.RunSQL "UPDATE CurrentP SET CurrentP.PStart = #" & Me.Text15 & "#,
CurrentP.PEnd = #" & Me.Text17 & "#;"
DoCmd.OpenQuery "AddPR"
DoCmd.OpenForm "ViewPR", acNormal ' , , "PR.PEND = " & Me.Text17

At some point while running the queries or sql or opening the form I would
get an Invalid page fault in the XP runtime version about 3/4 of the time.
This wasn't happening when we were running the db in 2000. After upgrading
the hard drive, I uninstalled the 2002 runtime version and re-installed
Access 2000. Now at this same point in Access 2000 every time I try to run
this code it will not run at all, I get error 2486 - You cannot complete the
action at this time. Then I keep getting this same error no matter what I try
to do until I exit the application completely, running code on any other form
or even attempting to exit the form gives the same error. I reinstalled the
original database and then again with the runtime version and then even
installed the full version of Access 2002 to try both of those and have the
same Invalid page fault problem 3/4 of the time in both Access 2002 full
version and runtime. The information given with this fault says

AppName: msaccess.exe AppVer: 10.0.2627.1 ModName: oleaut32.dll ModVer:
2.40.4518.0 Offset: 0002b683

I have tried uninstalling all office applications completely and
reinstalling them, even manually deleting leftover directories and registry
values before reinstalling. I have tried replacing the oleaut32.dll with 2
other versions and have the same problem.

The references for this db are:
Visual basic for applications
Microsoft access 9.0 Object Library
OLE Automation
Microsoft ActiveX data objects 2.1 Library
Microsoft Windows Common Controls 6.0

Any help would be so greatly appreciated with either version, I would prefer
to run the database with Access 2000 but would settle to just have it work in
the runtime version without the page faults.

Thanks
ST

 

[Allen Browne]

Analysis
======
a) Since the app has been opened under various versions of Access, there is
a good likelihood that it contains some spurious binary code. A Decompile
followed by a Compact should fix this.

b) The failed code may result from concurrency issues. Close anything else
that is using the same tables before executing the code. Particularly if
there is any forms open that could be editing a record, explicitly save.

c) RunSQL does not notify you if it fails, unless you have SetWarnings on,
in which case you get too many notifications, and still can't determine
programmatically if it succeeded. Instead, use the Execute method with the
dbFailOnError switch:
http://allenbrowne.com/ser-60.html

d) The OpenForm line will probably fail, since it lacks the # delimiters
around the literal date value you are concatenating into the WhereCondition
string. The code also needs to check that Text15 and Text17 contain valid
dates (e.g. not null), and that the dates are correctly formatted (if used
with non-US regional settings.)

e) Turn off Name AutoCorrect:
http://allenbrowne.com/bug-03.html

f) Remove unnecessary references such as oleaut32, and add a reference to
Microsoft DAO 3.6 (for the code below):
http://allenbrowne.com/ser-38.html

g) The OpenForm with WhereCondition won't work correctly if the form is
already open.

h) Use a valid event. This is probably okay (e.g. the Click of a command
button), but the errors could be caused by using an unsuitable event - one
that triggers a range of other events where the problem actually lies.

Suggestions
=========
Try this sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

7. Replace the code with this kind of thing (aircode):
------------code starts------------
Dim db As DAO.Database
Dim strSql As String
Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const strcTarget = "ViewPR" 'Name of form to open in the end.

Set db = CurrentDb
If IsDate(Me.Text15) And IsDate(Me.Text17) Then
db.Exeucte "UpdateHr", dbFailOnError
'Debug.Print "Records changed by UpdateHr: " & db.RecordsAffected

strSql = "UPDATE CurrentP SET CurrentP.PStart = " & _
Format(Me.Text15, strcJetDate) & ", CurrentP.PEnd = " & _
Format(Me.Text17, strcJetDate) & ";"
db.Execute strSql, dbFailOnError
'Debug.Print "Records changed by SQL statement: " & db.RecordsAffected

db.Execute "AddPR", dbFailOnError
'Debug.Print "Records changed by AddPR: " & db.RecordsAffected

strWhere = "PR.END = " & Format(Me.Text17, strcJetDate)
If CurrentProject.AllForms(strcTarget).IsLoaded Then
DoCmd.Close acForm, strcTarget
End If
DoCmd.OpenForm strcTarget, WhereCondition:=strWhere
Else
MsgBox "Enter dates in Text15 and Text17"
End If
Set db = Nothing
------------code ends------------

Notes
====
1. Add error handling.

2. Uncomment the Debug.Print lines to track down what's happening.

3. I have assumed your OpenQuery was used on action queries, not SELECT
queries.

4. The SQL statement has no WHERE clause, so all records in CurrentP get
updated. Not sure if that's intended.

5. If ViewPR might have been open and in the middle of an edit, the edit may
be lost:
http://allenbrowne.com/bug-01.html

 

[ST]

Thanks for your help. I tried all of your suggestions and was able to figure
out that while the error was showing up while trying to open the form, the
cause was actually the third query for some reason. I could stop the code at
that point and manually run the query and it would work fine, but running it
this way 'DoCmd.OpenQuery "AddPR"' cased the error I mentioned, and running
it like you mentioned 'db.Execute "AddPR", dbFailOnError' still caused an
invalid page fault. I also tried running is as an sql statement like the
second query/sql statment and it still caused an IPF. So as a last resort I
tried doing it like this and this worked:


Dim dbs As Database, rst As DAO.Recordset, rst2 As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(mysql)
Set rst2 = dbs.OpenRecordset(mysql2)

rst.MoveFirst
Do Until rst.EOF
With rst2
.addnew
!PPSTART = rst!PPSTART
!PPEND = rst!PPEND
!EID = rst!EID
!HrlyRate = rst!HR
.Update
.Bookmark = .LastModified
End With

rst.MoveNext
Loop
rst.Close
rst2.Close

I would love to know why the query would not run but I am just happy it is
working at this point.

Thanks for your help.

ST