On May 9, 8:55=A0am, lyle fairfield <lyle.fairfi...@[EMAIL PROTECTED]
> wrote:
> On May 9, 5:33=A0am, lyle fairfield <lyle.fairfi...@[EMAIL PROTECTED]
> wrote:
>
>
>
> > On May 9, 5:11=A0am, Wayne <cqdigi...@[EMAIL PROTECTED]
> 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. =A0I've used the following code posted by Lyle Fairfield to
> > > accomplish the first step:
>
> > > Private Sub SaveObjectsAsText()
> > > =A0 =A0 path =3D CurrentProject.path & "\ObjectsAsText\"
> > > =A0 =A0 SaveDataAccessPagesAsText
> > > =A0 =A0 SaveFormsAsText
> > > =A0 =A0 SaveRe****tsAsText
> > > =A0 =A0 SaveModulesAsText
> > > =A0 =A0 MsgBox "All Done Saving Access Objects as Text"
> > > End Sub
>
> > > Private Sub SaveDataAccessPagesAsText()
> > > =A0 =A0 Dim FileName As String
> > > =A0 =A0 Dim Name As String
> > > =A0 =A0 Dim DataAccessPage As AccessObject
> > > =A0 =A0 For Each DataAccessPage In CurrentProject.AllDataAccessPages
> > > =A0 =A0 =A0 =A0 Name =3D DataAccessPages.Name
> > > =A0 =A0 =A0 =A0 FileName =3D path & Name & Format(Now(),
"yyyymmddhhnn=
") &
> > > ".txt"
> > > =A0 =A0 =A0 =A0 SaveAsText acDataAccessPage, Name, FileName
> > > =A0 =A0 Next DataAccessPage
> > > MsgBox "All Done Saving Data Access Pages as Text"
> > > End Sub
>
> > > Private Sub SaveFormsAsText()
> > > =A0 =A0 Dim FileName As String
> > > =A0 =A0 Dim Name As String
> > > =A0 =A0 Dim Form As AccessObject
> > > =A0 =A0 For Each Form In CurrentProject.AllForms
> > > =A0 =A0 =A0 =A0 Name =3D Form.Name
> > > =A0 =A0 =A0 =A0 FileName =3D path & Name & Format(Now(),
"yyyymmddhhnn=
") &
> > > ".txt"
> > > =A0 =A0 =A0 =A0 SaveAsText acForm, Name, FileName
> > > =A0 =A0 Next Form
> > > MsgBox "All Done Saving Forms as Text"
> > > End Sub
>
> > > Private Sub SaveRe****tsAsText()
> > > =A0 =A0 Dim FileName As String
> > > =A0 =A0 Dim Name As String
> > > =A0 =A0 Dim Re****t As AccessObject
> > > =A0 =A0 For Each Re****t In CurrentProject.AllRe****ts
> > > =A0 =A0 =A0 =A0 Name =3D Re****t.Name
> > > =A0 =A0 =A0 =A0 FileName =3D path & Name & Format(Now(),
"yyyymmddhhnn=
") &
> > > ".txt"
> > > =A0 =A0 =A0 =A0 SaveAsText acRe****t, Name, FileName
> > > =A0 =A0 Next Re****t
> > > MsgBox "All Done Saving Re****ts as Text"
> > > End Sub
>
> > > Private Sub SaveModulesAsText()
> > > =A0 =A0 Dim FileName As String
> > > =A0 =A0 Dim Name As String
> > > =A0 =A0 Dim Module As AccessObject
> > > =A0 =A0 For Each Module In CurrentProject.AllModules
> > > =A0 =A0 =A0 =A0 Name =3D Module.Name
> > > =A0 =A0 =A0 =A0 FileName =3D path & Name & Format(Now(),
"yyyymmddhhnn=
") &
> > > ".txt"
> > > =A0 =A0 =A0 =A0 SaveAsText acModule, Name, FileName
> > > =A0 =A0 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_MDB_Objects_As_Text.htm
> > orhttp://www.ffdba.com/downloads/Save_ADP_Objects_As_Text.htm
>
> >http://www.ffdba.com/downloads/Save_MDB_Objects_As_Text.dat
> > orhttp://www.ffdba.com/downloads/Save_ADP_Objects_As_Text.dat
> > for download,
>
> > Use the whole page or module.
>
> LoadFromText is a hidden and undo***ented 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$ =3D "Guidance"
Private Const NewComponent$ =3D "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 =3D "sp_rename 'GuidanceStaff', 'StudentSuccessStaff'"
CurrentProject.Connection.Execute SQL
On Error GoTo 0
' -------------------
' change references from OldComponent to NewComponent in
Procedures, Views and (SQL) Functions
' -------------------
SQL =3D "SELECT sc.text"
SQL =3D SQL & " FROM SysComments sc"
SQL =3D SQL & " JOIN SysObjects so"
SQL =3D SQL & " ON sc.ID =3D so.ID"
SQL =3D SQL & " WHERE so.Name =3D "
For Each AccessObject In CurrentData.AllStoredProcedures
ObjectName =3D AccessObject.Name
Script =3D CurrentProject.Connection.Execute(SQL & "'" &
ObjectName & "'")(0)
AlterComponent True, ObjectName, Script
Next AccessObject
For Each AccessObject In CurrentData.AllViews
ObjectName =3D AccessObject.Name
Script =3D CurrentProject.Connection.Execute(SQL & "'" &
AccessObject.Name & "'")(0)
AlterComponent True, ObjectName, Script
Next AccessObject
For Each AccessObject In CurrentData.AllFunctions
ObjectName =3D AccessObject.Name
Script =3D CurrentProject.Connection.Execute(SQL & "'" &
AccessObject.Name & "'")(0)
AlterComponent True, ObjectName, Script
Next AccessObject
' -------------------
' get temp path
' -------------------
TempPath =3D Environ$("temp")
If Len(TempPath) =3D 0 Then TempPath =3D CurDir$()
' -------------------
' change references from OldComponent to NewComponent in Forms,
Re****ts and (SQL) Modules
' and their names
' objects whose names include OldComponent will not be deleted
' -------------------
For Each AccessObject In CurrentProject.AllForms
ObjectName =3D AccessObject.Name
SaveAsText acForm, ObjectName, TempPath & "\" & ObjectName
Next AccessObject
For Each AccessObject In CurrentProject.AllRe****ts
ObjectName =3D AccessObject.Name
SaveAsText acRe****t, ObjectName, TempPath & "\" & ObjectName
Next AccessObject
For Each AccessObject In CurrentProject.AllModules
ObjectName =3D AccessObject.Name
SaveAsText acModule, ObjectName, TempPath & "\" & ObjectName
Next AccessObject
' -------------------
' -------------------
For Each AccessObject In CurrentProject.AllForms
ObjectName =3D AccessObject.Name
FileNumber =3D FreeFile()
TempFullPath =3D TempPath & "\" & ObjectName
Open TempFullPath For Binary As #FileNumber
Script =3D String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Script
Close #FileNumber
Kill TempFullPath
If InStr(Script & " " & ObjectName, OldComponent) <> 0 Then
ChangeScript False, ObjectName, Script
FileNumber =3D 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.AllRe****ts
ObjectName =3D AccessObject.Name
FileNumber =3D FreeFile()
TempFullPath =3D TempPath & "\" & ObjectName
Open TempFullPath For Binary As #FileNumber
Script =3D String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Script
Close #FileNumber
Kill TempFullPath
If InStr(Script & " " & ObjectName, OldComponent) <> 0 Then
ChangeScript False, ObjectName, Script
FileNumber =3D FreeFile
Open TempFullPath For Binary As #FileNumber
Put #FileNumber, , Script
Close #FileNumber
LoadFromText acRe****t, ObjectName, TempFullPath
Kill TempFullPath
End If
Next AccessObject
For Each AccessObject In CurrentProject.AllModules
ObjectName =3D AccessObject.Name
If ObjectName <> "ChangeComponent" Then
FileNumber =3D FreeFile()
TempFullPath =3D TempPath & "\" & ObjectName
Open TempFullPath For Binary As #FileNumber
Script =3D String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Script
Close #FileNumber
Kill TempFullPath
If InStr(Script & " " & ObjectName, OldComponent) <> 0
Then
ChangeScript False, ObjectName, Script
FileNumber =3D 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(ByVal SQL As Boolean, ByRef ObjectName$,
ByRef Script$)
Dim Iterator&
If SQL Then
DropSQLObject ObjectName
Script =3D Replace(Script, "ALTER", "CREATE")
End If
For Iterator =3D 1 To 3
ObjectName =3D Replace(ObjectName, _
StrConv(OldComponent, Iterator), _
StrConv(NewComponent, Iterator), , vbBinaryCompare)
Script =3D Replace(Script, _
StrConv(OldComponent, Iterator), _
StrConv(NewComponent, Iterator), , vbBinaryCompare)
Next Iterator
ObjectName =3D Replace(ObjectName, OldComponent, NewComponent,
vbTextCompare)
Script =3D 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.Execute Script
End If
End Sub
Public Sub DropSQLObject(ByVal ObjectName$)
Dim SQL$
SQL =3D "IF EXISTS (SELECT * FROM sys.views WHERE object_id =3D
OBJECT_ID(N'[dbo].[ObjectName]'))"
SQL =3D SQL & " DROP VIEW [dbo].[ObjectName]"
SQL =3D Replace(SQL, "ObjectName", ObjectName)
CurrentProject.Connection.Execute 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.
|
