Access 2003 crashing

[maryj]

Client is using Access 2003 SP2/WinXP. The database she is using worked fine
on Win2K/Office 2K. Since she has upgraded, she will periodically get the
message "Access has encountered and error and must shut down". Sometimes it
works fine and then other times it crashes. Nothing consistent on when it
crashes and no other errors. All the tables but 1 are linked to SQL tables,
the other is an actual Access table. The database is ~3.7 mb. It was
originally created in an older version of Access - not sure which version.
There is quite a bit of VBA code. We have tried compact and repair, ran an
office repair and uninstalled/reinstalled Access. There is at least one other
user that is also having similar problems who also has Access 2003. We can
open the linked tables. To me it sounds like a compatiblity issue as it has
been upgraded. Any ideas of what to try, other than recreating all the code?

 

['69 Camaro]

Hi, Mary.

There is at least one other
user that is also having similar problems who also has Access 2003.

Is this a shared database, where everyone opens the same database file
located on a network server, or is the database split, with the back end
located on the network server and each user has a copy of the front end on
his own workstation? If the database file is being shared and it's an MDB,
not an MDE file, then the code can become uncompiled, especially if users
with other versions of Access open the file before Access 2003 opens the
file. When the code becomes uncompiled, Access 2003 can crash under certain
circumstances. If this is the case, then open the VB Editor and ensure that
the code is compiled.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[maryj]

We compiled the code and found many instances of errors. Does this mean that
the code needs to be rewritten for Access 2003?

 

['69 Camaro]

Hi, Mary.

Does this mean that
the code needs to be rewritten for Access 2003?

Well, you'll have to fix the compiler errors before it will run correctly in
Access 2003. But Access 2000 databases will generally run in Access 2003
with very few, if any, code changes. Did the code compile in Access 2000?
What types of errors are we talking about? Hard-coded paths that don't
exist on the Access 2003 workstations, but do on the Access 2000
workstations?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[maryj]

I believe the database was using the 2000 file format. I'm not exactly sure
what you mean by "Did the code compile in Access 2000?" One of the messages
was: Method or data member not found. Another: variable not defined.

 

['69 Camaro]

Hi, Mary.

I'm not exactly sure
what you mean by "Did the code compile in Access 2000?"

I mean that someone opened the database file in Access 2000, opened the VB

Another: variable not defined.

Obviously, it wasn't compiled in Access 2000, either. Make sure that
whoever is doing Access database development has the "Require Variable
Declaration" setting turned on. To do so, open the VB Editor and select the
Tools -> Options... menu to open the Options dialog window. On the "Editor"
tab, ensure that the "Require Variable Declaration" check box is checked, and
select the "OK" button to save any changes in this dialog window. This will
insert Option Explicit into every new standard module and class module.
However, it doesn't affect any of the existing modules, so you'll have to
ensure that every module in the database has the following two lines at the
top of the Declarations section:

Option Compare Database
Option Explicit

The first line can be replaced with Option Compare Binary if bookmarks are
being used in the code within that module, or if a binary comparison would be
more appropriate than the database settings for the code in that module.

Now, whenever the Access developer is done writing code or making any
changes to the code, ensure that the code compiles before the database
application is deployed to the users.

One of the messages
was: Method or data member not found.

That's generally a missing reference or a library reference that is set in
the wrong order of precedence. If the latter, then the code should be
disambiguated as to which library a declared object is part of. For example:

Dim rs As Recordset

.. . . should instead be either:

Dim rs As DAO.Recordset

.. . . or

Dim rs As ADODB.Recordset

.. . . since both the DAO library and the ADODB library contain the Recordset
class so when declaring variables, you need to tell Access which library
class you're using. If you don't, then Access will use the class from the
library that's listed first (ordered from the top to the bottom of the list
of checkboxed References) in the Reference dialog window, and that class's
methods and properties might not match that of the object that you eventally
instantiate in the code.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[maryj]

Thanks so much for your help. Now we know where to begin.

 

[aaron.kempf]

oh beautiful maryj lol

these MDB people are idiots.

find someone, pay someone-- to upsize your database to ADP format.

MDB isn't reliable enough for a single user and a single record.
don't rely on an obsolete database format.

 

['69 Camaro]

You're very welcome.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Spyros]

Client is using Access 2003 SP2/WinXP. The database she is using worked
fine
on Win2K/Office 2K. Since she has upgraded, she will periodically get the
message "Access has encountered and error and must shut down". Sometimes
it
works fine and then other times it crashes. Nothing consistent on when it
crashes and no other errors. All the tables but 1 are linked to SQL
tables,
the other is an actual Access table. The database is ~3.7 mb. It was
originally created in an older version of Access - not sure which version.
There is quite a bit of VBA code. We have tried compact and repair, ran an
office repair and uninstalled/reinstalled Access. There is at least one
other
user that is also having similar problems who also has Access 2003. We can
open the linked tables. To me it sounds like a compatiblity issue as it
has
been upgraded. Any ideas of what to try, other than recreating all the
code?