VBA Corruption

[phenderson]

Hi,

We have an application that uses a Access 2003 ADP (ADE in production)
as its front-end. Over the last 6 months or so we've seen an increase
in the amount of VBA corruption. Users receive odd error messages
like, "Can't find field """ or "Invalid expression """. We've tried
all the suggested steps, including, Compact & Repair, Decompile, Save
As Text => Import As Text, and Importing All Objects into a New ADP,
none of which have an effect. Another factor might be that we're
using source-code control with the ADP and doing a lot of importing
and exporting of the forms/report/modules. Does anyone have
experience with SCC causing corruption? Does anyone have any other
suggestions? I'd be happy to include the full text output of one of
the troublesome forms if that will help.

Thanks!

 

[Paul Shapiro]

Does each user run a local copy of the adp? I use a shortcut that copies the
latest adp file from the server to the user's computer every time they run.
At least that way if their local copy gets corrupted they get a clean copy
the next time they run the application.

Are adp's excluded from antivirus scanning?

The advantage of source-code control is you can just rebuild to repair
corruption. Why are you using decompile, save as text, etc.?

Probably doesn't apply to your issue, but I had random form corruption
taking a db created from SourceSafe in Access 2003 and using it in Access
2007. The reliable solution was to open EVERY form in Access 2003 before
letting Access 2007 touch the db. I wrote a routine to automate the process
if you want to try it.
Dim frm As AccessObject 'Temporary form
Dim strFormName As String 'Name of the current form

'Open and close each form
For Each frm In CurrentProject.AllForms
strFormName = frm.Name
Select Case strFormName
Case "frmTableOfContents", "frmAbstractDialogModeDupSub"
'Don't open these forms because they generate errors when used
this way.
Case Else
DoCmd.OpenForm FormName:=strFormName, View:=acNormal,
WindowMode:=acWindowNormal
Call pjsWait(lngMilliSecs:=250) 'API code that waits 250 msec
DoCmd.Close acForm, strFormName
End Select
Next frm

 

[phenderson]

Does each user run a local copy of the adp? I use a shortcut that copies the
latest adp file from the server to the user's computer every time they run.
At least that way if their local copy gets corrupted they get a clean copy
the next time they run the application.

Are adp's excluded from antivirus scanning?

The advantage of source-code control is you can just rebuild to repair
corruption. Why are you using decompile, save as text, etc.?

Probably doesn't apply to your issue, but I had random form corruption
taking a db created from SourceSafe in Access 2003 and using it in Access
2007. The reliable solution was to open EVERY form in Access 2003 before
letting Access 2007 touch the db. I wrote a routine to automate the process
if you want to try it.
    Dim frm                     As AccessObject     'Temporary form
    Dim strFormName    As String                   'Name of the current form

    'Open and close each form
    For Each frm In CurrentProject.AllForms
        strFormName = frm.Name
        Select Case strFormName
        Case "frmTableOfContents", "frmAbstractDialogModeDupSub"
            'Don't open these forms because they generate errors when used
this way.
        Case Else
            DoCmd.OpenForm FormName:=strFormName, View:=acNormal,
WindowMode:=acWindowNormal
            Call pjsWait(lngMilliSecs:=250)    'API code that waits 250 msec
            DoCmd.Close acForm, strFormName
        End Select
    Next frm

Thanks for the reply. Yes, each user has their own copy of the ADE.
The ADE is about 17MB, so I think we'd want to avoid having the ADE
copied to the user's workstation every time they run it. The ADP's
are not excluded from antivirus scanning. Is this something we should
be doing?

We started using the Decompile, Save As Text, etc. once the corruption
started appearing. It's not a part of our standard build process.

We haven't used Access 2007 much and definitely haven't used it when
the application's under source code control, but I'll keep that
routine for when we do.

One thing we've noticed while checking objects into source code
control is that for some reason a property named AsianLineBreak will
randomly get added to the header text. Has anyone ever seen this
property and/or know what it does?

 

[Klaus-Dieter Gundermann]

Hi,

have you received any helpful tips?
I am asking because I have the same problem with my Access-XP MDB
Application (with linked tables to SQL 2005 server). I am not using any
SCC but also see weird symptoms, including:

- Access crash when opening a table and filtering for date column
- Adding a new Control to a Form, and saving the form -> Access Crashes

- invalid expression, as concatenating strings sometimes misses some
part. e.g.
strQuery = "SELECT * FROM " & strMyTable & " WHERE " & strMyFilter
debug.print strQuery
-> Customers WHERE CustID=123
That is Access omits some parts of the string concatenation
- Can't find field "" , as Access does not forward some parameters
in a sub/function call. e.g
Public Sub MySub( Param1, Param2, Param3)

Call MySub("One","Two","Three")

in MySub the params whill have:
Param1 = ""
Param2 = "Two"
Param3 = "Three"


I have tried every suggestion from allenbrowne.com
and most times a DCR (Decompile/Compact/Repair) will cure the problem,
but not for long. After making some changes to one of my Forms/Code
a new problem may pop up even in forms/code i even didn't touch !

And what it make even worse: Even when the mdb is running fine on my
development system, when I deploy it to the clients Access may crash
on opening the mdb...

Regards

Klaus