How To Rebuild Objects From Text Files?

[Wayne]

I've read that one method of repairing a misbehaving database is to
save all database objects as text and then rebuild them from the text
files. I've used the following code posted by Lyle Fairfield to
accomplish the first step:

Private Sub SaveObjectsAsTe xt()
path = CurrentProject. path & "\ObjectsAsText \"
SaveDataAccessP agesAsText
SaveFormsAsText
SaveReportsAsTe xt
SaveModulesAsTe xt
MsgBox "All Done Saving Access Objects as Text"
End Sub
Private Sub SaveDataAccessP agesAsText()
Dim FileName As String
Dim Name As String
Dim DataAccessPage As AccessObject
For Each DataAccessPage In CurrentProject. AllDataAccessPa ges
Name = DataAccessPages .Name
FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
SaveAsText acDataAccessPag e, Name, FileName
Next DataAccessPage
MsgBox "All Done Saving Data Access Pages as Text"
End Sub
Private Sub SaveFormsAsText ()
Dim FileName As String
Dim Name As String
Dim Form As AccessObject
For Each Form In CurrentProject. AllForms
Name = Form.Name
FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
SaveAsText acForm, Name, FileName
Next Form
MsgBox "All Done Saving Forms as Text"
End Sub
Private Sub SaveReportsAsTe xt()
Dim FileName As String
Dim Name As String
Dim Report As AccessObject
For Each Report In CurrentProject. AllReports
Name = Report.Name
FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
SaveAsText acReport, Name, FileName
Next Report
MsgBox "All Done Saving Reports as Text"
End Sub
Private Sub SaveModulesAsTe xt()
Dim FileName As String
Dim Name As String
Dim Module As AccessObject
For Each Module In CurrentProject. AllModules
Name = Module.Name
FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
SaveAsText acModule, Name, FileName
Next Module
MsgBox "All Done Saving Modules as Text"
End Sub

How do I then rebuild the database objects from the text files that
have been created?

[lyle fairfield]

On May 9, 5:11*am, Wayne <cqdigi...@volc anomail.comwrot e:

I've read that one method of repairing a misbehaving database is to
save all database objects as text and then rebuild them from the text
files. *I've used the following code posted by Lyle Fairfield to
accomplish the first step:

Private Sub SaveObjectsAsTe xt()
* * path = CurrentProject. path & "\ObjectsAsText \"
* * SaveDataAccessP agesAsText
* * SaveFormsAsText
* * SaveReportsAsTe xt
* * SaveModulesAsTe xt
* * MsgBox "All Done Saving Access Objects as Text"
End Sub

Private Sub SaveDataAccessP agesAsText()
* * Dim FileName As String
* * Dim Name As String
* * Dim DataAccessPage As AccessObject
* * For Each DataAccessPage In CurrentProject. AllDataAccessPa ges
* * * * Name = DataAccessPages .Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acDataAccessPag e, Name, FileName
* * Next DataAccessPage
MsgBox "All Done Saving Data Access Pages as Text"
End Sub

Private Sub SaveFormsAsText ()
* * Dim FileName As String
* * Dim Name As String
* * Dim Form As AccessObject
* * For Each Form In CurrentProject. AllForms
* * * * Name = Form.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acForm, Name, FileName
* * Next Form
MsgBox "All Done Saving Forms as Text"
End Sub

Private Sub SaveReportsAsTe xt()
* * Dim FileName As String
* * Dim Name As String
* * Dim Report As AccessObject
* * For Each Report In CurrentProject. AllReports
* * * * Name = Report.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acReport, Name, FileName
* * Next Report
MsgBox "All Done Saving Reports as Text"
End Sub

Private Sub SaveModulesAsTe xt()
* * Dim FileName As String
* * Dim Name As String
* * Dim Module As AccessObject
* * For Each Module In CurrentProject. AllModules
* * * * Name = Module.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acModule, Name, FileName
* * Next Module
MsgBox "All Done Saving Modules as Text"
End Sub

How do I then rebuild the database objects from the text files that
have been created?

Did I ever post this half-page? The whole pages can be found at:

http://www.ffdba.com/downloads/Save_...ts_As_Text.htm
or
http://www.ffdba.com/downloads/Save_...ts_As_Text.htm

http://www.ffdba.com/downloads/Save_...ts_As_Text.dat
or
http://www.ffdba.com/downloads/Save_...ts_As_Text.dat
for download,

Use the whole page or module.

[Rick Brandt]

Wayne wrote:

I've read that one method of repairing a misbehaving database is to
save all database objects as text and then rebuild them from the text
files. I've used the following code posted by Lyle Fairfield to
accomplish the first step:

[snip]

How do I then rebuild the database objects from the text files that
have been created?

Just as there is a SaveAsText() function there is also a LoadFromText()
function. The latter is whaty you would use to rebuild the objects.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[lyle fairfield]

On May 9, 5:33*am, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On May 9, 5:11*am, Wayne <cqdigi...@volc anomail.comwrot e:

I've read that one method of repairing a misbehaving database is to
save all database objects as text and then rebuild them from the text
files. *I've used the following code posted by Lyle Fairfield to
accomplish the first step:

Private Sub SaveObjectsAsTe xt()
* * path = CurrentProject. path & "\ObjectsAsText \"
* * SaveDataAccessP agesAsText
* * SaveFormsAsText
* * SaveReportsAsTe xt
* * SaveModulesAsTe xt
* * MsgBox "All Done Saving Access Objects as Text"
End Sub

Private Sub SaveDataAccessP agesAsText()
* * Dim FileName As String
* * Dim Name As String
* * Dim DataAccessPage As AccessObject
* * For Each DataAccessPage In CurrentProject. AllDataAccessPa ges
* * * * Name = DataAccessPages .Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhhnn") &
".txt"
* * * * SaveAsText acDataAccessPag e, Name, FileName
* * Next DataAccessPage
MsgBox "All Done Saving Data Access Pages as Text"
End Sub

Private Sub SaveFormsAsText ()
* * Dim FileName As String
* * Dim Name As String
* * Dim Form As AccessObject
* * For Each Form In CurrentProject. AllForms
* * * * Name = Form.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhhnn") &
".txt"
* * * * SaveAsText acForm, Name, FileName
* * Next Form
MsgBox "All Done Saving Forms as Text"
End Sub

Private Sub SaveReportsAsTe xt()
* * Dim FileName As String
* * Dim Name As String
* * Dim Report As AccessObject
* * For Each Report In CurrentProject. AllReports
* * * * Name = Report.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhhnn") &
".txt"
* * * * SaveAsText acReport, Name, FileName
* * Next Report
MsgBox "All Done Saving Reports as Text"
End Sub

Private Sub SaveModulesAsTe xt()
* * Dim FileName As String
* * Dim Name As String
* * Dim Module As AccessObject
* * For Each Module In CurrentProject. AllModules
* * * * Name = Module.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhhnn") &
".txt"
* * * * SaveAsText acModule, Name, FileName
* * Next Module
MsgBox "All Done Saving Modules as Text"
End Sub

How do I then rebuild the database objects from the text files that
have been created?

Did I ever post this half-page? The whole pages can be found at:

http://www.ffdba.com/downloads/Save_...ts_As_Text.htm
orhttp://www.ffdba.com/downloads/Save_ADP_Object s_As_Text.htm

http://www.ffdba.com/downloads/Save_...ts_As_Text.dat
orhttp://www.ffdba.com/downloads/Save_ADP_Object s_As_Text.dat
for download,

Use the whole page or module.

LoadFromText is a hidden and undocumented procedure.
It may be worthwhile to note that
LoadFromText AcObjectType, ObjectName, FilePath
overwrites the object named ObjectName with whatever instructions are
in FilePath.
There is, TTBOMK, NO recovery from this. The old object now belongs to
the ages, but not to you. Over many years LoadFromText has never
failed me, but if I give it wrong or foolish instructions, it carries
them out, without any warning, just as it carries out any other
instructions.
In summary, the making of backups and/or safe copies may be a
worthwhile expenditure of time and resources before using
LoadFromText.
Coincidentally, I am doing that right now, in an effort to change all
"Guidance" objects and references to same, to "StudentSuccess "
objects. Ain't politics grand? I expect that StudentSuccess staff will
be much more effective than Guidance staff.
The first thing I did was to make a safe copy of the ADP (it could
have been MDB) file.

[lyle fairfield]

On May 9, 8:55*am, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On May 9, 5:33*am, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On May 9, 5:11*am, Wayne <cqdigi...@volc anomail.comwrot e:

I've read that one method of repairing a misbehaving database is to
save all database objects as text and then rebuild them from the text
files. *I've used the following code posted by Lyle Fairfield to
accomplish the first step:

Private Sub SaveObjectsAsTe xt()
* * path = CurrentProject. path & "\ObjectsAsText \"
* * SaveDataAccessP agesAsText
* * SaveFormsAsText
* * SaveReportsAsTe xt
* * SaveModulesAsTe xt
* * MsgBox "All Done Saving Access Objects as Text"
End Sub

Private Sub SaveDataAccessP agesAsText()
* * Dim FileName As String
* * Dim Name As String
* * Dim DataAccessPage As AccessObject
* * For Each DataAccessPage In CurrentProject. AllDataAccessPa ges
* * * * Name = DataAccessPages .Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acDataAccessPag e, Name, FileName
* * Next DataAccessPage
MsgBox "All Done Saving Data Access Pages as Text"
End Sub

Private Sub SaveFormsAsText ()
* * Dim FileName As String
* * Dim Name As String
* * Dim Form As AccessObject
* * For Each Form In CurrentProject. AllForms
* * * * Name = Form.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acForm, Name, FileName
* * Next Form
MsgBox "All Done Saving Forms as Text"
End Sub

Private Sub SaveReportsAsTe xt()
* * Dim FileName As String
* * Dim Name As String
* * Dim Report As AccessObject
* * For Each Report In CurrentProject. AllReports
* * * * Name = Report.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acReport, Name, FileName
* * Next Report
MsgBox "All Done Saving Reports as Text"
End Sub

Private Sub SaveModulesAsTe xt()
* * Dim FileName As String
* * Dim Name As String
* * Dim Module As AccessObject
* * For Each Module In CurrentProject. AllModules
* * * * Name = Module.Name
* * * * FileName = path & Name & Format(Now(), "yyyymmddhh nn") &
".txt"
* * * * SaveAsText acModule, Name, FileName
* * Next Module
MsgBox "All Done Saving Modules as Text"
End Sub

How do I then rebuild the database objects from the text files that
have been created?

Did I ever post this half-page? The whole pages can be found at:

http://www.ffdba.com/downloads/Save_...ts_As_Text.htm
orhttp://www.ffdba.com/downloads/Save_ADP_Object s_As_Text.htm

http://www.ffdba.com/downloads/Save_...ts_As_Text.dat
orhttp://www.ffdba.com/downloads/Save_ADP_Object s_As_Text.dat
for download,

Use the whole page or module.

LoadFromText is a hidden and undocumented procedure.
It may be worthwhile to note that
LoadFromText AcObjectType, ObjectName, FilePath
overwrites the object named ObjectName with whatever instructions are
in FilePath.
There is, TTBOMK, NO recovery from this. The old object now belongs to
the ages, but not to you. Over many years LoadFromText has never
failed me, but if I give it wrong or foolish instructions, it carries
them out, without any warning, just as it carries out any other
instructions.
In summary, the making of backups and/or safe copies may be a
worthwhile expenditure of time and resources before using
LoadFromText.
Coincidentally, I am doing that right now, in an effort to change all
"Guidance" objects and references to same, to "StudentSuccess "
objects. Ain't politics grand? I expect that StudentSuccess staff will
be much more effective than Guidance staff.
The first thing I did was to make a safe copy of the ADP (it could
have been MDB) file.

Access and the COM objects that can be exposed through VBA, JET and
ADO are marvelously powerful. Those posters here who denigrate them
are just plain wrong. This is my preliminary code. I claim it changes
everything in my ADP that refers to Guidance, from Guidance to
StudentSuccess. Is it perfect? No. For instance there are some Labels
and Captions which will have become "StudentSuccess " that I will
change interactively to "Student Success" Is it Beta? Not that yet;
it's my first whack at this since 1998. But IMO it's worth the couple
of hours (since beginning work this morning) spent creating it,
because next time any client says, I want to change ALL Matildas to
Rosemarys I can do that in 30 seconds. And clients do make such
requests, even when they promise that they won't. And if you WANT the
NEXT contract, sometimes it's better to say, "Sure, we can have that
tomorrow, no problem", instead of, "It's going to cost you MORE, and
how does late August sound?".

Option Compare Database
Option Explicit

Private Const OldComponent$ = "Guidance"
Private Const NewComponent$ = "StudentSuccess "

Private Sub ScanComponent()
Dim AccessObject As AccessObject
Dim FileNumber%
Dim ObjectName$
Dim SQL$
Dim Script$
Dim TempFullPath$
Dim TempPath$

' change the table name
' -------------------
On Error Resume Next
SQL = "sp_rename 'GuidanceStaff' , 'StudentSuccess Staff'"
CurrentProject. Connection.Exec ute SQL
On Error GoTo 0
' -------------------

' change references from OldComponent to NewComponent in
Procedures, Views and (SQL) Functions
' -------------------
SQL = "SELECT sc.text"
SQL = SQL & " FROM SysComments sc"
SQL = SQL & " JOIN SysObjects so"
SQL = SQL & " ON sc.ID = so.ID"
SQL = SQL & " WHERE so.Name = "

For Each AccessObject In CurrentData.All StoredProcedure s
ObjectName = AccessObject.Na me
Script = CurrentProject. Connection.Exec ute(SQL & "'" &
ObjectName & "'")(0)
AlterComponent True, ObjectName, Script
Next AccessObject

For Each AccessObject In CurrentData.All Views
ObjectName = AccessObject.Na me
Script = CurrentProject. Connection.Exec ute(SQL & "'" &
AccessObject.Na me & "'")(0)
AlterComponent True, ObjectName, Script
Next AccessObject

For Each AccessObject In CurrentData.All Functions
ObjectName = AccessObject.Na me
Script = CurrentProject. Connection.Exec ute(SQL & "'" &
AccessObject.Na me & "'")(0)
AlterComponent True, ObjectName, Script
Next AccessObject
' -------------------

' get temp path
' -------------------
TempPath = Environ$("temp" )
If Len(TempPath) = 0 Then TempPath = CurDir$()
' -------------------

' change references from OldComponent to NewComponent in Forms,
Reports and (SQL) Modules
' and their names
' objects whose names include OldComponent will not be deleted
' -------------------
For Each AccessObject In CurrentProject. AllForms
ObjectName = AccessObject.Na me
SaveAsText acForm, ObjectName, TempPath & "\" & ObjectName
Next AccessObject

For Each AccessObject In CurrentProject. AllReports
ObjectName = AccessObject.Na me
SaveAsText acReport, ObjectName, TempPath & "\" & ObjectName
Next AccessObject

For Each AccessObject In CurrentProject. AllModules
ObjectName = AccessObject.Na me
SaveAsText acModule, ObjectName, TempPath & "\" & ObjectName
Next AccessObject
' -------------------

' -------------------
For Each AccessObject In CurrentProject. AllForms
ObjectName = AccessObject.Na me
FileNumber = FreeFile()
TempFullPath = TempPath & "\" & ObjectName
Open TempFullPath For Binary As #FileNumber
Script = String(LOF(File Number), vbNullChar)
Get #FileNumber, , Script
Close #FileNumber
Kill TempFullPath
If InStr(Script & " " & ObjectName, OldComponent) <0 Then
ChangeScript False, ObjectName, Script
FileNumber = FreeFile
Open TempFullPath For Binary As #FileNumber
Put #FileNumber, , Script
Close #FileNumber
LoadFromText acForm, ObjectName, TempFullPath
Kill TempFullPath
End If
Next AccessObject

For Each AccessObject In CurrentProject. AllReports
ObjectName = AccessObject.Na me
FileNumber = FreeFile()
TempFullPath = TempPath & "\" & ObjectName
Open TempFullPath For Binary As #FileNumber
Script = String(LOF(File Number), vbNullChar)
Get #FileNumber, , Script
Close #FileNumber
Kill TempFullPath
If InStr(Script & " " & ObjectName, OldComponent) <0 Then
ChangeScript False, ObjectName, Script
FileNumber = FreeFile
Open TempFullPath For Binary As #FileNumber
Put #FileNumber, , Script
Close #FileNumber
LoadFromText acReport, ObjectName, TempFullPath
Kill TempFullPath
End If
Next AccessObject

For Each AccessObject In CurrentProject. AllModules
ObjectName = AccessObject.Na me
If ObjectName <"ChangeCompone nt" Then
FileNumber = FreeFile()
TempFullPath = TempPath & "\" & ObjectName
Open TempFullPath For Binary As #FileNumber
Script = String(LOF(File Number), vbNullChar)
Get #FileNumber, , Script
Close #FileNumber
Kill TempFullPath
If InStr(Script & " " & ObjectName, OldComponent) <0
Then
ChangeScript False, ObjectName, Script
FileNumber = FreeFile
Open TempFullPath For Binary As #FileNumber
Put #FileNumber, , Script
Close #FileNumber
LoadFromText acModule, ObjectName, TempFullPath
Kill TempFullPath
End If
End If
Next AccessObject

End Sub

Private Sub ChangeScript(By Val SQL As Boolean, ByRef ObjectName$,
ByRef Script$)
Dim Iterator&
If SQL Then
DropSQLObject ObjectName
Script = Replace(Script, "ALTER", "CREATE")
End If
For Iterator = 1 To 3
ObjectName = Replace(ObjectN ame, _
StrConv(OldComp onent, Iterator), _
StrConv(NewComp onent, Iterator), , vbBinaryCompare )
Script = Replace(Script, _
StrConv(OldComp onent, Iterator), _
StrConv(NewComp onent, Iterator), , vbBinaryCompare )
Next Iterator
ObjectName = Replace(ObjectN ame, OldComponent, NewComponent,
vbTextCompare)
Script = Replace(Script, OldComponent, NewComponent,
vbTextCompare)
End Sub

Private Sub AlterComponent( ByVal SQL As Boolean, ByRef ObjectName$,
ByRef Script$)
If InStr(Script, OldComponent) <0 Then
ChangeScript SQL, ObjectName, Script
DropSQLObject ObjectName
CurrentProject. Connection.Exec ute Script
End If
End Sub

Public Sub DropSQLObject(B yVal ObjectName$)
Dim SQL$
SQL = "IF EXISTS (SELECT * FROM sys.views WHERE object_id =
OBJECT_ID(N'[dbo].[ObjectName]'))"
SQL = SQL & " DROP VIEW [dbo].[ObjectName]"
SQL = Replace(SQL, "ObjectName ", ObjectName)
CurrentProject. Connection.Exec ute SQL
End Sub
Yes, I knw it's for an ADP, but i suspect changing ti to work in an
MDB is no more than 15 minutes work.

[Larry Linson]

And if you WANT the NEXT contract, sometimes it's better

to say, "Sure, we can have that tomorrow, no problem", instead
of, "It's going to cost you MORE, and how does late
August sound?".

But sometimes, Lyle, wouldn't it be satisfying to tell them? :-)

Larry

[lyle fairfield]

On May 9, 9:00*pm, "Larry Linson" <boun...@localh ost.notwrote:

*And if you WANT the NEXT contract, sometimes it's better
*to say, "Sure, we can have that tomorrow, no problem", instead
*of, "It's going to cost you MORE, and how does late
*August sound?".

But sometimes, Lyle, wouldn't it be satisfying to tell them? *:-)

*Larry

I have told too many, Larry; wayyyyyyyyyyyy too many. I'm trying to
learn humility in my old age. Hasn't worked yet though. Maybe
tomorrow; maybe not.

[Larry Linson]

"lyle fairfield" <ly************ @gmail.comwrote

But sometimes, Lyle, wouldn't it be satisfying to tell them? :-)

I have told too many, Larry; wayyyyyyyyyyyy too
many. I'm trying to learn humility in my old age.
Hasn't worked yet though. Maybe tomorrow;
maybe not.

Hmm. You'd think, after all these years, we'd have earned the right not to
be humble. After all, aren't we legends in our own minds?

And, wasn't there an old song that went something like this?

"There's no tomorrow when you're older'n dirt."

[lyle fairfield]

On May 18, 2:57*am, Wayne <cqdigi...@volc anomail.comwrot e:

I've used the code from the linked page that Lyle has posted above to
save and then load as text. *The saving bit works fine but then the
code fails at:

app.LoadFromTex t acForm, Name, FileName

with the error:

Runtime error '2285'
(Database Name) can't create the output file.

Not sure what's causing this. *Any help would be appreciated.

The code, in total, creates text files and a new copy of the database.
It uses another instance of the Access application to do so. If you
leave some of the code out; it's likely to err. So, first question,
did you leave some of the code out?

[Wayne]

No, I'm using the code in total that I copied from your linked page.
It creates the text files and a new database minus the objects in a
new folder, but stops with the error at the line of code that I posted
when trying to recreate the forms. I don't have any data pages, so the
forms are the first thing that it is trying to recreate.