Rebuilding a form

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

Lars Brownies

A2003: I inherited an mdb of which one form occasionally causes corruption.
No backup available, I made one now. I also took measures to prevent this
mdb from further corruption. I want to create a new form and drag all fields
from the old form on top of that new form. Then I'm planning to copy the
form's code in the new form's module. Is this the right way to go? Could
this solve the corruption? Any other advice on how to do this?

Since the corruption appears only occasionally it's hard to test.

Thank in advance,
Lars
 
Lars

You are confident that the corruption is caused by the form, and not by
something else? For example, a wireless connection, a "flakey" network,
multiple users sharing a single front-end, ...?

Regards

Jeff Boyce
Microsoft Access MVP
 
Lars Brownies said:
A2003: I inherited an mdb of which one form occasionally causes
corruption. No backup available, I made one now. I also took measures to
prevent this mdb from further corruption. I want to create a new form and
drag all fields from the old form on top of that new form. Then I'm
planning to copy the form's code in the new form's module. Is this the
right way to go? Could this solve the corruption? Any other advice on how
to do this?

Since the corruption appears only occasionally it's hard to test.
Click to expand...

If there is something gone wrong in the Form, I wouldn't trust anything
short of (1) re-creating the Form from scratch or (2) using SaveAsText and
LoadFromText (those are about the most recommended, and best
unofficially-documented but officially-undocumented features of Access) to
take the Form to text, then re-create it. Most any other approach still
leaves you exposed to transferring whatever has gone wrong into the "new"
Form with something that you copy.

But, as Jeff pointed out, there are different kinds of corruption, and
multiple possibilities as a cause.

Larry Linson
Microsoft Office Access MVP
 
Larry said:
If there is something gone wrong in the Form, I wouldn't trust anything
short of (1) re-creating the Form from scratch or (2) using SaveAsText and
LoadFromText (those are about the most recommended, and best
unofficially-documented but officially-undocumented features of Access) to
take the Form to text, then re-create it. Most any other approach still
leaves you exposed to transferring whatever has gone wrong into the "new"
Form with something that you copy.

But, as Jeff pointed out, there are different kinds of corruption, and
multiple possibilities as a cause.

Larry Linson
Microsoft Office Access MVP
Click to expand...

The Save module code below was adapted (rather quickly) from a post by
Lyle Fairfield. Note that the link at his web site for this doesn't
work anymore. I don't know who did the Import code (I modified it
slightly).

Public Sub SaveFormsAsText(path As String)
Dim Filename As String
Dim strName As String
Dim theForm As Form
Dim ctnr As Container
Dim MyDB As DAO.Database
Dim DocForm As Document

Set MyDB = CurrentDb
For Each ctnr In MyDB.Containers
If ctnr.Name = "Forms" Then
For Each DocForm In ctnr.Documents
strName = DocForm.Name
Filename = path & strName & Format(Now(), "yyyymmddhhnn") & ".txt"
SaveAsText acForm, strName, Filename
DoEvents
Next DocForm
End If
Next ctnr
DoEvents
MsgBox "All Done Saving Forms as Text"
End Sub


Public Sub ImportDatabaseObjects()
On Error GoTo Err_ImportDatabaseObjects

Dim strFilename As String
Dim ObjName As String
Dim ObjType As String
Dim acObjType As Integer
Dim Index As Integer
Dim ImportLocation As String
Dim ImportDir As String

ImportLocation = "C:\MyExport\*.*"

strFilename = Dir(ImportLocation)
Do
ImportDir = "C:\MyExport\"
ObjName = Left(strFilename, Len(strFilename) - 16)
strFilename = ImportDir & strFilename
Application.LoadFromText acForm, ObjName, strFilename
strFilename = Dir
Loop Until strFilename = ""
Exit_ImportDatabaseObjects:
Exit Sub
Err_ImportDatabaseObjects:
'Debug.Print ObjName & "." & ObjType & "." & Err.Number & "." &
End Sub

How I used it:

Private Sub cmdImportForms_Click()
Call ImportDatabaseObjects
MsgBox ("Done Importing Forms.")
End Sub

Private Sub cmdSaveFormsAsText_Click()
Call SaveFormsAsText("C:\MyExport")
End Sub

After exporting, I just put the files I wanted to import into the
directory. A smoother way would be to have a form select the directory
using a file picker and populate a listbox for the user to choose which
form text files to import. The problem I had was that a call to a Class
Module I used to maintain a global date value for a pop-up calendar form
wouldn't convert to A2K3. Even though the database ran fine in A97, a
message came up saying that the conversion could not be accomplished
because the VBA code is corrupt. Replacing the call to the Class Module
with a global date variable solved the problem for now. Reconstituting
the forms from text allowed me to see which form caused the problem. As
Larry pointed out, SaveAsText and LoadFromText are officially
undocumented, but they managed to help me solve that problem.

James A. Fortune
(e-mail address removed)
 
Thanks Jeff.
You are confident that the corruption is caused by the form,
No.

and not by something else? For example, a wireless connection, a "flakey"
network, multiple users sharing a single front-end, ...?
Click to expand...

No wireless connection, citrix-network is fine, users have their own FE,
since recently. The corruption occurs sometimes when changing the form. And
it occurs occasionally when I open the form.

Lars
 
Thanks James, Larry,
I will try to do so.

James A. Fortune said:
The Save module code below was adapted (rather quickly) from a post by
Lyle Fairfield. Note that the link at his web site for this doesn't work
anymore. I don't know who did the Import code (I modified it slightly).

Public Sub SaveFormsAsText(path As String)
Dim Filename As String
Dim strName As String
Dim theForm As Form
Dim ctnr As Container
Dim MyDB As DAO.Database
Dim DocForm As Document

Set MyDB = CurrentDb
For Each ctnr In MyDB.Containers
If ctnr.Name = "Forms" Then
For Each DocForm In ctnr.Documents
strName = DocForm.Name
Filename = path & strName & Format(Now(), "yyyymmddhhnn") &
".txt"
SaveAsText acForm, strName, Filename
DoEvents
Next DocForm
End If
Next ctnr
DoEvents
MsgBox "All Done Saving Forms as Text"
End Sub


Public Sub ImportDatabaseObjects()
On Error GoTo Err_ImportDatabaseObjects

Dim strFilename As String
Dim ObjName As String
Dim ObjType As String
Dim acObjType As Integer
Dim Index As Integer
Dim ImportLocation As String
Dim ImportDir As String

ImportLocation = "C:\MyExport\*.*"

strFilename = Dir(ImportLocation)
Do
ImportDir = "C:\MyExport\"
ObjName = Left(strFilename, Len(strFilename) - 16)
strFilename = ImportDir & strFilename
Application.LoadFromText acForm, ObjName, strFilename
strFilename = Dir
Loop Until strFilename = ""
Exit_ImportDatabaseObjects:
Exit Sub
Err_ImportDatabaseObjects:
'Debug.Print ObjName & "." & ObjType & "." & Err.Number & "." &
End Sub

How I used it:

Private Sub cmdImportForms_Click()
Call ImportDatabaseObjects
MsgBox ("Done Importing Forms.")
End Sub

Private Sub cmdSaveFormsAsText_Click()
Call SaveFormsAsText("C:\MyExport")
End Sub

After exporting, I just put the files I wanted to import into the
directory. A smoother way would be to have a form select the directory
using a file picker and populate a listbox for the user to choose which
form text files to import. The problem I had was that a call to a Class
Module I used to maintain a global date value for a pop-up calendar form
wouldn't convert to A2K3. Even though the database ran fine in A97, a
message came up saying that the conversion could not be accomplished
because the VBA code is corrupt. Replacing the call to the Class Module
with a global date variable solved the problem for now. Reconstituting
the forms from text allowed me to see which form caused the problem. As
Larry pointed out, SaveAsText and LoadFromText are officially
undocumented, but they managed to help me solve that problem.

James A. Fortune
(e-mail address removed)
Click to expand...
 
Amazing how this works!
I succesfully saved and imported the particular form and so far no signs of
corruption.

Lars
 
For multiple versions and releases, Access suffered an intermittent type of
corruption in which the code in a form's module would simply cease to
work... it was still there, you could still see it, you could even edit it,
but it just didn't work. I "fondly" nicknamed this "The Amazing
Biodegradable Forms Feature". It made the transition from 16-bit (Access
2.0) to 32-bit (Access 97) and even to Access 2000. Maybe I am just
creating fewer databases and forms these days, but I don't think I've
encountered it with Access 2002 or 2003, yet never heard any cheers from the
Northwest about "fixing it", so I don't know if it just happened to have
been eliminated when some Access code was replaced in a new release,
version, or Service Pack, or if someone did a search and destroy mission for
it and just isn't "crowing" about it, or if it is still lurking, waiting to
catch me at the most inopportune time.

It is sad for me to think about how many forms I re-created from scratch
before learning about SaveAsText and LoadFromText. <SIGH>

Larry Linson
Microsoft Office Access MVP
 
Larry,

Can this "undocumented" method be used to transfer forms, reports, and
queries from a development database to a test (and then production)
database?

Thanks!

Larry
 
It could be used in such a fashion, but since all applications should be
split into a front-end (containing the queries, forms, reports, macros and
modules), linked to a back-end (containing the table and relations), all you
should have to do is replace each user's front-end database when you're
ready to start using a new version.
 
Douglas,

The client site is set up like that. However, they are using Office 2000
and I am using Office 2003, so I can't create the MDE file locally and send
it to them. I have to copy the components from my MDB file to theirs and
then create the MDE to distribute to the desktops. I was thinking this
"feature" could be scripted - which would save me a significant amount of
time.

Larry
 
I would think it would be far safer to give them a complete MDB rather than
trying to replace bits and pieces within it.
 
Ah yes, and thus the beauty of a home-grown change management system for
Access.
Meaning I track - in another Access database - every component I change,
along with the request number.

Larry
 
The client site is set up like that. However, they are using
Office 2000 and I am using Office 2003, so I can't create the MDE
file locally and send it to them.
Click to expand...

A professional Access developer owns copies of the versions her
clients use and shouldn't have this problem. While it's not possible
to get a copy of A2000 any longer, you should look into getting a
licensed copy from the client for this purpose. Alternatively,
perhaps the client could provide you remote access to a system on
which you can compile the MDE in A2000.

In my opinion, your approach is completely crazy.
 
Larry Kahm said:
Can this "undocumented" method be used to
transfer forms, reports, and queries from a
development database to a test (and then
production) database?
Click to expand...

Yes, I have used SaveAsText from one DB, and LoadFromText from another --
don't remember all the details of "why".

But, as best I remember, both were the same version of Access.

Larry Linson
Microsoft Office Access MVP
 
David W. Fenton said:
. . . While it's not possible to get a copy of
A2000 any longer, you should look into
getting a licensed copy from the client for
this purpose. Alternatively, perhaps the client
could provide you remote access to a system on
which you can compile the MDE in A2000.
Click to expand...

I would wager that you _can_ get a copy of A2000... possibly a brand-new
copy in shrink-wrap... from ebay, or other auction site, an "obsolete
software site", or a local computer swap meet. A few years ago, well after
Access 2000 was availble, I was wandering through the Third Saturday
computer swap meet in downtown Dallas, and, lo, one of the vendors had a
handful of still-shrink-wrapped copies of Access 1.0 or Access 1.1.

I had a colleague who was in dire need of a copy of Access 2.0, long after
it was "unavailable" who bid on and got a copy via ebay for a very
reasonable price; another needed the Access 97 Developer Edition, which was
very hard to find by that time, and got it via ebay (but he never would tell
me what he had to pay for it... just "you really don't want to know"). Sad
to say, by the time he and his partner got their application ready to roll
out, he found their customers didn't want to install it with the Access 97
runtime, and had to get a later version of Access and runtime, anyway.

Larry Linson
Microsoft Office Access MVP
 
Larry Linson said:
Yes, I have used SaveAsText from one DB, and
LoadFromText from another -- don't remember
all the details of "why".

But, as best I remember, both were the same
version of Access.
Click to expand...

But, for distributing, I am of the school that thinks you should send the
users the complete MDE.

Larry Linson
Microsoft Office Access MVP
 
Back
Top