Save As Text Question
- From: Wayne <cqdigital@xxxxxxxxxxxxxxx>
- Date: Mon, 23 Feb 2009 22:39:49 -0800 (PST)
I've used the following code which I'm fairly sure I found on this
newsgroup to save as text and then import:
Option Compare Database
Option Base 0
Option Explicit
Dim path$
Dim DateTimeString$
Dim app As Access.Application
Private Sub SaveMDBObjectsAsText()
Dim R As Reference
DateTimeString = Format(Now(), "yyyymmddhhnn")
path = CurrentProject.path & "\AS_TEXT_" & DateTimeString & "\"
MkDir path
Set app = New Access.Application
SaveDataAccessPagesAsText
SaveFormsAsText
SaveReportsAsText
SaveModulesAsText
SaveQueriesAsText
SaveMDBBase
LoadDataAccessPagesFromText
LoadFormsFromText
LoadReportsFromText
LoadModulesFromText
LoadQueriesFromText
On Error Resume Next
With app
With .CurrentProject
path = .FullName
End With
For Each R In .References
With R
If Not .BuiltIn Then
app.References.Remove R
End If
End With
Next R
For Each R In References
With R
If Not .BuiltIn Then
app.References.AddFromGuid R.Guid, R.Major,
R.Minor
End If
End With
Next R
.RunCommand acCmdCompileAndSaveAllModules
.CloseCurrentDatabase
' make MDE
' .SysCmd 603, Path, Replace(Path, ".mdb", ".mde")
.Quit
End With
Set app = Nothing
MsgBox "All Done with Text Backup"
End Sub
Private Sub SaveDataAccessPagesAsText()
Dim FileName$
Dim Name$
Dim DataAccessPage As AccessObject
For Each DataAccessPage In CurrentProject.AllDataAccessPages
Name = DataAccessPage.Name
FileName = path & Name & ".txt"
SaveAsText acDataAccessPage, Name, FileName
Next DataAccessPage
End Sub
Private Sub SaveFormsAsText()
Dim FileName$
Dim Name$
Dim Form As AccessObject
For Each Form In CurrentProject.AllForms
Name = Form.Name
FileName = path & Name & ".txt"
SaveAsText acForm, Name, FileName
Next Form
End Sub
Private Sub SaveReportsAsText()
Dim FileName$
Dim Name$
Dim Report As AccessObject
For Each Report In CurrentProject.AllReports
Name = Report.Name
FileName = path & Name & ".txt"
SaveAsText acReport, Name, FileName
Next Report
End Sub
Private Sub SaveMacrosAsText()
Dim FileName$
Dim Name$
Dim Macro As AccessObject
For Each Macro In CurrentProject.AllMacros
Name = Macro.Name
FileName = path & Name & ".txt"
SaveAsText acMacro, Name, FileName
Next Macro
End Sub
Private Sub SaveModulesAsText()
Dim FileName$
Dim Name$
Dim Module As AccessObject
For Each Module In CurrentProject.AllModules
Name = Module.Name
FileName = path & Name & ".txt"
SaveAsText acModule, Name, FileName
Next Module
End Sub
Private Sub SaveQueriesAsText()
Dim FileName$
Dim Name$
Dim GetQueryNames As ADODB.Recordset
Set GetQueryNames = CurrentProject.Connection.OpenSchema
(adSchemaViews)
With GetQueryNames
Do While Not .EOF
Name = .Fields("TABLE_NAME")
FileName = path & Name & ".txt"
SaveAsText acQuery, Name, FileName
.MoveNext
Loop
End With
End Sub
Private Sub SaveMDBBase()
Dim FileName$
Dim Name$
Name = Replace(CurrentProject.Name, CurrentProject.path, "")
' name it with XP sffix to enable later conversion to 2K
' Name = Replace(Name, ".", "XP.")
FileName = path & Name
SaveAsText 6, "", FileName
app.OpenCurrentDatabase FileName
End Sub
Private Sub LoadDataAccessPagesFromText()
Dim FileName$
Dim Name$
Dim DataAccessPage As AccessObject
For Each DataAccessPage In CurrentProject.AllDataAccessPages
Name = DataAccessPage.Name
FileName = path & Name & ".txt"
app.LoadFromText acDataAccessPage, Name, FileName
Next DataAccessPage
End Sub
Private Sub LoadFormsFromText()
Dim FileName$
Dim Name$
Dim Form As AccessObject
For Each Form In CurrentProject.AllForms
Name = Form.Name
FileName = path & Name & ".txt"
app.LoadFromText acForm, Name, FileName
Next Form
End Sub
Private Sub LoadReportsFromText()
Dim FileName$
Dim Name$
Dim Report As AccessObject
For Each Report In CurrentProject.AllReports
Name = Report.Name
FileName = path & Name & ".txt"
app.LoadFromText acReport, Name, FileName
Next Report
End Sub
Private Sub LoadModulesFromText()
Dim FileName$
Dim Name$
Dim Module As AccessObject
For Each Module In CurrentProject.AllModules
Name = Module.Name
FileName = path & Name & ".txt"
app.LoadFromText acModule, Name, FileName
Next Module
End Sub
Private Sub LoadMacrosFromText()
Dim FileName$
Dim Name$
Dim Macro As AccessObject
For Each Macro In CurrentProject.AllMacros
Name = Macro.Name
FileName = path & Name & ".txt"
app.LoadFromText acMacro, Name, FileName
Next Macro
End Sub
Private Sub LoadQueriesFromText()
Dim FileName$
Dim Name$
Dim GetQueryNames As ADODB.Recordset
Set GetQueryNames = CurrentProject.Connection.OpenSchema
(adSchemaViews)
With GetQueryNames
Do While Not .EOF
Name = .Fields("TABLE_NAME")
FileName = path & Name & ".txt"
app.LoadFromText acQuery, Name, FileName
.MoveNext
Loop
End With
End Sub
What is interesting and what I don't understand is this: If I run the
code in an A2000 format database using A2003 it works fine. If I run
the code in A2000 or A2003 format database using A2007 it runs fine.
If I run the code in an A2003 format database using A2003 it falls
over. On my desktop, it runs for a short time and then the database
locks up and gives the generic "application has died" error.
On my laptop, similar story as the desktop when using an A2003 format
database using A2003 ie. it runs for a short time and then locks
Access up and and I need to use Taskmanager to end the process. Also,
on the laptop, same story as the desktop when using A2007, it runs
fine.
If the problem was only occurring on the desktop, I'd suspect a
corrupt installation of A2003 on that machine, but it happens on both
machines. Can anyone suggest a reason why I'm seeing this
behaviour?
Also is there any evidence to suggest that A2007 is more robust when
handling databases of questionable integrity ie. suspected corrupt
forms etc?
.
- Follow-Ups:
- Re: Save As Text Question
- From: lyle fairfield
- Re: Save As Text Question
- Prev by Date: Re: Auto FE Updater Question
- Next by Date: How to capture error?
- Previous by thread: Repeat Section
- Next by thread: Re: Save As Text Question
- Index(es):
Relevant Pages
|
