Solved

Access 2003 MDB Issue Revising Forms

Posted on 2014-01-10
12
462 Views
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.
0
Comment
Question by:mlcktmguy
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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¢
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
PatHartman:
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?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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".
0
 
LVL 84
Comment Utility
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.
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
Since moving from 2010 on WIn 7 machine, all development is being done on the Win XP 2003 machine.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 334 total points
Comment Utility
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
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
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.
0
 
LVL 26

Assisted Solution

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

DO NOT MAKE CHANGES TO ANY OBJECT IN ANY VERSION OTHER THAN Access 2003.
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!
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 334 total points
Comment Utility
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_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
    
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:mlcktmguy
Comment Utility
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now