Access 2003 MDB Issue Revising Forms

Posted on 2014-01-10
Last Modified: 2014-01-10
First of, this app has a history.  I'll spare the details but it started life as an Access 2010 project.  Saved out of 2010 accdb as an 2003 MDB.  Didn't really use any of the 2010 functionality just wanted to move to a newer, supported platform.

There is no going back to 2010, the project deadline in a couple days.

I resolved the reference issues and ran thru the application functions.  All looked good.

The issues began when I tried to modify one of the forms in the app.  I  added a new field to the form.  When I tried to save the form the entire app locked up and canceled.  I got the 'Do you want to send this error to Microsoft' box.

I tried every technique I know to resolve application issues:

I have:
1. Decompile/Recompile
2, Compact Repair
3. Created an entirely new blank MDB and pulled the entire application into it.
4. Make sure there a no unresolved references.

I was eventually able to revise the screen I was working on.  But not the problem on other screens I am trying to revise.  In the middle of a form revision the app locks.  From that point on the form I was working on cannot be opened in development mode or any mode for that matter.

In that case I have pulled in the original version of the screen from a backup MDB and redo steps 1- 4 above.
The issue has occurred multiple times now and my deadline is fast approaching.

I have:
Decompiled many times,
Compact Repair many times
Created an enitrely new blank MDB and pulled the entire application into it several times.
Resolved all references.

I realize going from 2010 to 2003  is an odd situation and likely not one that is very common but I was hoping that there might be some ideas in the EE community.
Question by:mlcktmguy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39771153
sorry and i feel your pain but, the rule still stands (there is no back ward compatibility)... for application run in multi versions of access.. develop the app using the lowest version

my 2¢
LVL 36

Expert Comment

ID: 39771155
Make sure there are no compile errors.  They can sometimes cause what looks like corruption.

Try opening the app in a different version of Access.  If you are using A2010, then open it with A2003 or vice versa.  Different versions of Access are more and less sensitive to "stuff" and sometimes simply opening the app in a different version can straighten out the bit that is off.

Author Comment

ID: 39771312
No compile errors.

As far as opening in a different version of Access:

The app was originally created in Win 7 Pro, Access 2010.
Saved as an Access 2003 MDB
Now running on a Win XP Pro, Access 2003 machine.

Are you suggesting I move it back to the Win 7 machine, open the MDB in Access 2010, save it and then move it back to the XP machine and open it in 2003?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 36

Expert Comment

ID: 39771452
Yes.  take it to the Win 7 PC and open it with 2010.  Make some small change.  Then bring it back.  I know it sounds strange but then corruption is strange and this has fixed corruption for me even when all the steps you've gone through haven't.  It's liking kicking the database "upside the head".
LVL 84
ID: 39771606
Are you doing your development work on the XP/2003 machine, or on the Win7/2010 machine?

If you're on the 2010 machine, try moving to the 2003 machine and doing your final build/compile there. Also try creating the new database and such on the 2003 machine. I've had issues when saving out of 2010 to older formats.

Author Comment

ID: 39771688
Since moving from 2010 on WIn 7 machine, all development is being done on the Win XP 2003 machine.

Author Comment

ID: 39771783
Tried moving MDB to Win 7/2010 machine, opening the MDB, making a small change and saving it back to the Win XP/2003 machine.

The from I am trying to work on is called 'frmProperty'

Same issue.  I added one button at a time and saved the form for each one.  I was able to add 3 buttons.  When I added the 4 button and a new unbound textbox, the form save OK.  But then when I tried to run it I got 'You cancelled the previous operation' on the form open command when another form tried to open it.

Now frmProperty is basically dead.  Will not open in design mode, cannot be called in run mode.
LVL 36

Assisted Solution

PatHartman earned 334 total points
ID: 39771875
For the forms that have corrupted, have you added/deleted a lot of controls?  There is a max limit of controls for a form that I think is somewhere around 900 but don't quote me on that.  You may be able to tell by the number that follows the name when you add a control by dragging it from the ribbon.

If your form has reached this limit, you'll need to rebuild the form.  Open it in design view (or the most recent backup you can find) and copy all the controls and paste them onto the surface of a new form.  Then open the code module and do the same thing.  Set the recordsource and any other properties.

One other thing you can try is to export the corrupt object as text.
Public Sub ExportForm()
Dim strPath As String
    strPath = "C:\Data\Work\Form_"
    Application.SaveAsText acForm, "frmLogDrawingsBAD", strPath & "frmLogDrawingsBAD" & ".txt"
End Sub
Public Sub ImportForm()
Dim strPath As String
    strPath = "C:\Data\Work\Form_"
    Application.Application.LoadFromText acForm, "frmLogDrawings", strPath & "frmLogDrawingsBAD" & ".txt"
End Sub

Open in new window

While the form is text, delete the hex stuff.  I circled some of it in the attached picture.  Go through the text file and delete this stuff between the Begin and End tags wherever it occurs.  Then import the form back into the database.  Text Form

Author Comment

ID: 39771933
PatHartman: Thanks.  

Since time is a serious factor, while I was waiting for an EE response, I had created a new blank form and set the recordsource to the same as the old form.  Then I copied all of the controls and backing code to the blank form as you suggested.  I am in the process of adding my new controls but it seems to be going well so far.  The new contols and functionality that I couldn't add to the original form has been added to this one.  I'm not done yet but at least have some hope.  Fingers crossed, I've spent way too much time on some simple form revisions.

I think your export/import is also a good and previously unknown to me option.  I'm sure I'm going to run into this again.  I'll try the export/import before I recreate the entire form.
LVL 26

Assisted Solution

Nick67 earned 166 total points
ID: 39772209
My sad experience is that you are SOL.
I run A2003, A2010 and A2013 in concert.

Within a very short time whatever object has been touched by uplevel versions will be corrupted when touched by Access 2003.
"Now frmProperty is basically dead.  Will not open in design mode, cannot be called in run mode. "  Yuuuuuuuuuuuup.  Been there done that

You still have a working copy.  Good.  And Bad.
You are going to have to throw away every object created by any version other than Access 2003--Bad.
You can copy-and-paste all the controls from each object 'corrupted' by the uplevel versions to new objects that you create in Access 2003--Good
It can be done--Good.
It's time-consuming--Bad.
Your deadline is short--Very Bad.

Having learned this the hard way, I've never had to do more that 2 or 3 objects.  On occasion, I'll get the nice wizards to create a form in A2010.  There's no hope to put it into production--but it can save me some scutwork when I copy-and-paste the controls to a new A2003 object before deep-sixing the object created by the wizard.

I've never tried to use the undocumented export/import objects to text in a downlevel conversion scenario -- and I am not sure that it would work.  Time is short.  Do you want detail on how to do the export to text / import to text through VBA knowing it may not work, or would you be better off putting your time to use doing what will undoubtably work -- recreating each object?

As a stopgap, export your accdb to an mdb.  CHANGE NOTHING because it will get corrupted if you do.  Make it into an MDE--which is not supposed to be able to be changed.  You can put THAT into production until you rebuild all your objects.

Assuming that you've spilt this into a front-end/back-end app, of course.

And make no mistake, even if you get frmProperty working, every single object that you got from the accdb is a landmine waiting for you to step on.  They all have to go.  The bit about hand-hacking out the hex from the exported text file is new to me, though!
LVL 36

Accepted Solution

PatHartman earned 334 total points
ID: 39772301
My understanding is that SaveAsText is the way addins like SourceSafe work and Access itself probably uses the method to import/export objects.

Here's a useful sub that exports all objects of the specified type.  I use this in a backup routine to export all objects when I want to have a text version of everything.  My production copy which is more sophisticated and  I don't have with me at the moment uses a different method of defining the target directory.

Public Sub ExportDatabaseObjects(ExportType As String)
On Error GoTo Err_ExportDatabaseObjects
    'Dim db As Database
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim D As Document
    Dim C As Container
    Dim i As Integer
    Dim sExportLocation As String
    Set db = CurrentDb()
''import from text =
''application.Application.LoadFromText acForm, "frmRisks","C:\Temp\TextRiskReview070615\Form_frmRisks.txt"

    sExportLocation = "C:\Data\Work\Maggio\TextObjects\" 'Do not forget the closing back slash! ie: C:\Temp\
    Select Case ExportType
        Case "TableDefs"
            For Each td In db.TableDefs 'Tables
                If Left(td.Name, 4) <> "MSys" Then
                    DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
                End If
            Next td
        Case "Forms"
            Set C = db.Containers("Forms")
            For Each D In C.Documents
                Application.SaveAsText acForm, D.Name, sExportLocation & "Form_" & D.Name & ".txt"
            Next D
        Case "Reports"
            Set C = db.Containers("Reports")
            For Each D In C.Documents
                Application.SaveAsText acReport, D.Name, sExportLocation & "Report_" & D.Name & ".txt"
            Next D
        Case "Scripts"
            Set C = db.Containers("Scripts")
            For Each D In C.Documents
                Application.SaveAsText acMacro, D.Name, sExportLocation & "Macro_" & D.Name & ".txt"
            Next D
        Case "Modules"
            Set C = db.Containers("Modules")
            For Each D In C.Documents
                Application.SaveAsText acModule, D.Name, sExportLocation & "Module_" & D.Name & ".txt"
            Next D
        Case "QueryDefs"
            For i = 0 To db.QueryDefs.Count - 1
                Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
            Next i
        Case Else
    End Select

    Set db = Nothing
    Set C = Nothing
    MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
    Exit Sub
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
End Sub

Open in new window


Author Closing Comment

ID: 39772834
Excellent Information and thanks for the advice and words of experience.  Thanks to your informative and honest responses I am not completely SOL.  Yes, I'm in a very tight spot but at least I have a plan of attack if I encounter this issue again, which I know from your experiences, I surely will.
One thing I did to be proactive is to just go ahead and use the cut and paste method to recreate any forms that I know I will definitely be changing.  It was a pain but much quicker than encountering any issue as the deadline fast approaches.
Once again thanks to EE for the big help right when needed.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question