Re: Code causes error 2486 in access 2000, IPF in 2002

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ST" <ST@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:491BD26E-A6CC-4AF7-9D07-ED20CE1B0B5A@xxxxxxxxxxxxxxxx
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

.