Incompatibility between Access 2003 and Access 2002

[Guest]

I have been writing an Access application using two computers, one of which
runs Access 2002 and the other Access 2003. Ever since the project began
I've been able to copy the mdb file (formatted for Access 2000) back and
forth with no problem. Recently, however, after making some changes on the
Access 2002 computer, running the file on the Access 2003 computer causes a
"Microsoft Access has encountered a problem and needs to close." error. I've
tried the following: changing the format to Access 2002 (no help),
decompiling the file in Access 2002 (no help), running the file on other
computers loaded with Access 2003 (same problem). All the while, it
continues to run fine on the Access 2002 machine. Can anyone help me with
this?

 

[Allen Browne]

Firstly, make sure Name AutoCorrect is off. Explanation:
http://allenbrowne.com/bug-03.html

The file format won't make a difference. The Decompile is a good idea
whenever you switch version, as the binaries are different. After the
decompile, make sure *no* code runs (e.g. not even startup code), and
compact the database (again not running any code when if it restarts.)

At what point does it crash? When you open the database? Open a particular
form? Execute a particular query? Open the code window?

What libraries are you using? (Tools | References from the code window)?

What service packs are applied?

 

[Guest]

Thank you for your reply. There is no AutoExec macro. Holding the shift key
down during startup does delay the crash and, while still holding the shift
key down, I can even open the startup form in design view, but when I release
the shift key, the crash occurs. I've also tried opening the code window
while holding the shift key down. This causes the crash to occur
immediately. I don't think the problem is in the startup form. I haven't
made any changes to it recently and I've removed it from the Options menu as
the startup form, so it doesn't run during startup anymore.

 

[Allen Browne]

Try opening another database, such as Northwind.
Does that crash also as soon as you open a code window?

We are trying to determine whether this is specific to the database
(suggesting a corruption or bad reference) or to the computer (suggesting a
corrupted installation of Access, a faulty add-in, or interference from a
..NET version of VB.)

 

[Guest]

It is specific to the database in question. I have several others that work
fine on both computers.

 

[Allen Browne]

Okay, let's work on the assumption that this is a corruption this database.

The recovery process might look like this:

1. Make a backup copy of the bad mdb, so you get multiple chances at fixing
it.

2. Open it in A2002. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact
Remember to hold down shift if it restarts.
Close Access before anything else happens.

3. Close Access. Decompile with A2002 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"
Again, hold down shift so nothing runs.

4. Open Access 2002, and compact again, holding down shift.

5. Create a new (blank) database.
Immediately turn off Name AutoCorrect.
Open the code window, and set minimal references.
More info on references:
http://allenbrowne.com/ser-38.html

6. Import *just* the tables and queries:
File | Get External | Import.
At this point make a backup copy, and check it works okay in A2003.

7. Import any modules from the bad database.
Verify this still works in A2003.

8. Import your forms.
Verify in A2003.

9. Import the reports.
Verify in A2003.

10. Check that all the code compiles:
Debug | Compile

Hopefully, you will get through the first 6 steps successfully. At some
point when you begin importing the modules or the forms that have modules,
the problem will resurface. At this point, you may need to begin splitting
the problem in half (e.g. import half the forms) to identify which half the
problem form(s)/module(s) are in.

There are other options if this becomes difficult, such as exporting the
form with SaveAsText, and importing into the new database with LoadFromText.
Or even saving the text of the form's module out to a text file, setting the
form's HasModule to No, compacting the database, importing the light-weight
form, and then pasting the text back in.

 

[Guest]

I can't tell you enough how much I appreciate your support on this issue. I
was able to identify the offending module. It is a class module lifted from
"Access 2002 Desktop Developer's Handbook" by Litwin, Getz, and Gunderloy,
and published by Sybex. If you have the book, it comes from the chapter 7
mdb file "ch07.mdb". The module is designed to implement incremental search
(automatic updating of a listbox as you type text into a text box). It works
very well in 2002. It crashes Access 2003, even when you try to run the
original ch07.mdb itself. That is, it didn't seem to be some complex
interaction between my code and this module that caused the crash. I think
it is a rather pretty piece of code because it can be used with either ADO or
DAO (depending on how you set a constant) and can handle multiple types of
recordsets. But something in it causes Access 2003 to crash. So here is the
code, which I hope doesn't violate the copyright. I hope someone can
identify the specifics of what is going on here:
================
Option Compare Database
Option Explicit

' From Access 2002 Desktop Developer's Handbook
' by Litwin, Getz, and Gunderloy. (Sybex)
' Copyright 2001. All rights reserved.

' Incremental Search Class

' Set this constant to False to
' use ADO instead of DAO.
#Const USEDAO = True

Private WithEvents mlst As ListBox
Private WithEvents mtxt As TextBox

Private Enum ObjectType
otNone = 0
otTable = 1
otDynaset = 2
End Enum
Private mot As ObjectType

#If USEDAO Then
Private mdb As DAO.Database
Private mrst As DAO.Recordset
#Else
Private mrst As ADODB.Recordset
#End If

Public DisplayField As String
Public BoundField As String
Public Index As String

Public Property Set TextBox(txt As TextBox)
Set mtxt = txt
mtxt.OnChange = "[Event Procedure]"
mtxt.OnLostFocus = "[Event Procedure]"
End Property

Public Property Get TextBox() As TextBox
Set TextBox = mtxt
End Property

Public Property Set ListBox(lst As ListBox)
Set mlst = lst
mlst.AfterUpdate = "[Event Procedure]"
#If USEDAO Then
Call SetupRstDAO
#Else
Call SetupRstADO
#End If
End Property

Public Property Get ListBox() As ListBox
Set ListBox = mlst
End Property

#If USEDAO Then
Private Sub SetupRstDAO()

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strSource As String

' We can handle:
' * Tables
' * Queries with no parameters
' * Queries with resolvable parameters.
' * SQL SELECT strings with no parameters.
' * SQL SELECT strings with resolvable parameters.

On Error Resume Next

strSource = mlst.RowSource

' Attempt to open a table-type recordset.
Set mdb = CurrentDb()
Set mrst = mdb.OpenRecordset(strSource, dbOpenTable)

' If there wasn't an error, you managed
' to open a table-type recordset, and all is well.
' Now attempt to assign an index.
If Err = 0 Then
mrst.Index = Index
If Err = 0 Then
mot = otTable
GoTo ExitHere
End If
End If

' You're only here if you didn't manage to open a table-type
' recordset and set its index.
Err.Clear
Set qdf = mdb.QueryDefs(strSource)
If Err.Number <> 0 Then
' This isn't a querydef, but probably
' a SQL string. This may have parameters,
' so try one more thing: create a new
' querydef, so you can evaluate its
' parameters.
Err.Clear
Set qdf = mdb.CreateQueryDef("", strSource)
End If

' Fill in parameter values, if possible.
' This will still fail for parameters that
' require user input.
If Err.Number = 0 Then
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set mrst = qdf.OpenRecordset(dbOpenDynaset)
End If

If Err.Number = 0 Then
mot = otDynaset
Else
mot = otNone
End If

ExitHere:
Err.Clear
End Sub

#Else
Private Sub SetupRstADO()
' Open a recordset, based on the RowSource of the
' specified listbox.
' On Error Resume Next
Dim cmd As ADODB.Command
Dim intCount As Integer
Dim strSource As String

' We can handle:
' * Tables
' * Queries with no parameters
' * Queries with resolvable parameters.
' * SQL SELECT strings with no parameters.
' * SQL SELECT strings with resolvable parameters,
' except with DAO wildcards.

' IMPORTANT NOTE: ADO understands "%" and "_"
' as wildcard characters. Without parsing the SQL
' ourselves, we can't make Jet wildcards ("*" and "?")
' work here. Basically, if you supply SQL as the RowSource
' property for the list box, you cannot use "*" or "?".
' The problem is that Access can't handle "%" and "_"
' as wildcards, and ADO can't handle "*" and "?".
' If you use the query processor (that is, use a
' predefined query) as your RowSource, this won't be
' a problem -- Access takes care of the conversion for you.
' Although you COULD rewrite this code to handle
' this (replacing "*" and "?" in the WHERE clause)
' it seems like overkill. Just use a table, a query, or
' a simple SQL string as the RowSource and you'll be all set.

' Inline error handling is simpler here.
On Error Resume Next

Set mrst = New ADODB.Recordset
mrst.Source = mlst.RowSource
mrst.CursorType = adOpenStatic
mrst.LockType = adLockOptimistic
Set mrst.ActiveConnection = CurrentProject.Connection

' Attempt to open a table directly.
mrst.Open Options:=adCmdTableDirect
If Err.Number <> 0 Then
' That didn't succeed. Now try
' using adCmdTable, and this may
' require satisfying parameters.
Err.Clear
mrst.Open Options:=adCmdTable
' You may need to satisfy parameters. Do it here?
If Err.Number <> 0 Then
If Err.Number = -2147217904 Then
Err.Clear
Set mrst = HandleParametersADO(ct:=adCmdTable)
End If
End If
End If
If Err.Number <> 0 Then
' That didn't succeed. Now try
' using adCmdTable, and this may
' require satisfying parameters.
Err.Clear
mrst.Open Options:=adCmdText
' You may need to satisfy parameters. Do it here?
If Err.Number <> 0 Then
If Err.Number = -2147217904 Then
Set mrst = HandleParametersADO(ct:=adCmdText)
Else
GoTo HandleErrors
End If
End If
End If

If Len(Index) > 0 Then
' Just go ahead and try.
Err.Clear
mrst.Index = Index
If Err.Number = 0 Then
mot = otTable
Else
' Oops. Can't set the Index
' property. Therefore, clear
' out the Index string
' so later code knows there's
' no index in use.
mot = otDynaset
End If
Else
mot = otDynaset
End If
Err.Clear
If mrst.State = adStateClosed Then
mot = otNone
End If

ExitHere:
Exit Sub

HandleErrors:
mot = otNone
Err.Raise Err.Number, _
"IncrementalSearch.SetupRstADO", Err.Description
End Sub

Private Function HandleParametersADO( _
Optional ct As CommandTypeEnum = adCmdTableDirect) _
As ADODB.Recordset
' First, check to see the the row source
' has any parameters. This won't work
' for text you type directly into
' the RowSource property, but it will work
' for queries that have parameters based
' on form values. If you want to support
' generalized queries that have other types
' of parameters, you'll need to add support
' for that here.

Dim cmd As ADODB.Command
Dim intCount As Integer
Dim prm As ADODB.Parameter

On Error GoTo HandleErrors

' Open a new Command object.
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "(" & mlst.RowSource & ")"
cmd.CommandType = ct
intCount = cmd.Parameters.Count

' If there are any parameters,
' evaluate them now.
If intCount > 0 Then
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm
' If you've set up a Command
' object like this, you cannot
' use the Index, or the Seek method later.
' Indicate that this is a dynaset-like
' thing.
mot = otDynaset
End If
Set HandleParametersADO = cmd.Execute

ExitHere:
Exit Function

HandleErrors:
Select Case Err.Number
Case Else
Err.Raise Err.Number, _
"IncrementalSearch.HandleParametersADO", _
Err.Description
End Select
Resume ExitHere
End Function
#End If

Private Sub Class_Terminate()
On Error Resume Next
mrst.Close
Set mrst = Nothing
Set mtxt = Nothing
Set mlst = Nothing
End Sub

Private Sub mlst_AfterUpdate()
mtxt.Value = mlst.Value
End Sub

Private Sub mtxt_Change()
Dim strFilter As String
Dim strTemp As String
Dim strDelimiter As String

On Error GoTo HandleErrors

DoCmd.Hourglass True
If Len(BoundField) = 0 Then
BoundField = DisplayField
End If
If Len(mtxt.Text) > 0 Then
' Is there text in the text box?
' If so, filter based on that text.
#If USEDAO Then
Select Case mot
Case otNone
' Nothing to do!
GoTo ExitHere
Case otDynaset
strFilter = DisplayField & _
" >= " & FixQuotes(mtxt.Text)
mrst.FindFirst strFilter
Case otTable
' If there is an index set,
' you can use Seek.
mrst.Seek ">=", mtxt.Text
End Select
#Else
Select Case mot
Case otNone
' Nothing to do!
GoTo ExitHere
Case otDynaset
If Len(strDelimiter) = 0 Then
strDelimiter = "'"
End If
TryAgain:
strFilter = DisplayField & _
" >= " & FixQuotes(mtxt.Text, strDelimiter)
mrst.Filter = strFilter
Case otTable
' If there is an index set,
' you can use Seek.
mrst.Seek mtxt.Text, adSeekAfterEQ
End Select
#End If
' Did we find any rows at all?
' If so, set the value of the
' list box to be the value you found.
If Not mrst.EOF Then
mlst.Value = mrst.Fields(BoundField)
End If

#If USEDAO Then
' Nothing special to do, if you're using DAO.
#Else
' Reset the filter for next time.
mrst.Filter = vbNullString
#End If
Else
' If no text, then
' move to the first row,
' set the value to be that value
' (so the list box scrolls to the top)
' and then set the value to be Null,
' so nothing's selected.
mrst.MoveFirst
mlst.Value = mrst.Fields(BoundField)
mlst.Value = Null
End If

ExitHere:
DoCmd.Hourglass False
Exit Sub

HandleErrors:
DoCmd.Hourglass False
Select Case Err.Number
#If USEDAO Then
' No special errors, for DAO.
#Else
Case 3001 ' The stupid ADO quotes problem.
' ADO can't handle parsing this stupid thing.
' What to do?

' If the delimiter is currently "'"
' then try "#". This might work. If there's both
' two apostrophes and a "#" in there, you're in trouble.
If strDelimiter = "'" Then
strDelimiter = "#"
Resume TryAgain
Else
MsgBox "ADO is unable to parse the expression you entered."
Resume ExitHere
End If
#End If
Case Else
Err.Raise Err.Number, _
"IncrementalSearch.TextChange", Err.Description
Resume
End Select
Resume ExitHere
End Sub

Private Function FixQuotes(strValue As String, _
Optional strDelimiter As String = "'") As String
' In:
' strValue: Value to fix up.
' strDelimiter: (Optional) Delimiter to use.
' Out:
' Return value: the text, with delimiters fixed up.

FixQuotes = _
strDelimiter & _
Replace(strValue, strDelimiter, strDelimiter & strDelimiter) & _
strDelimiter
End Function

Private Sub mtxt_LostFocus()
On Error Resume Next
mtxt.Value = mlst.Value
End Sub


==========

 

[TC]

Further to Allen's good advice: if you have followed all his steps, &
it still crashes, you need to find what line it crashes on. Take a copy
of the db, then add some msgbox'es or whatever to help you find out.
(Then you can discard that copy, instead of having to take those
statements all back out.)

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Allen Browne]

Okay, good, you have traced it to one particular routine.

I'm not going to try to debug Ken's code, but in general, class modules are
more problematic (less stable) than standard modules, and you do have to
instantiate, manage, and destroy them carefully.

I'm not sure what the version-specific problem might be with this code.
Perhaps someone who has used it might be able to comment.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I can't tell you enough how much I appreciate your support on this issue.
I
was able to identify the offending module. It is a class module lifted
from
"Access 2002 Desktop Developer's Handbook" by Litwin, Getz, and Gunderloy,
and published by Sybex. If you have the book, it comes from the chapter 7
mdb file "ch07.mdb". The module is designed to implement incremental
search
(automatic updating of a listbox as you type text into a text box). It
works
very well in 2002. It crashes Access 2003, even when you try to run the
original ch07.mdb itself. That is, it didn't seem to be some complex
interaction between my code and this module that caused the crash. I
think
it is a rather pretty piece of code because it can be used with either ADO
or
DAO (depending on how you set a constant) and can handle multiple types of
recordsets. But something in it causes Access 2003 to crash. So here is
the
code, which I hope doesn't violate the copyright. I hope someone can
identify the specifics of what is going on here:
================
Option Compare Database

[snip]