Export All Database Objects Into Text Files

[mmmcpher]

TXT files not generating

I have just found a thread called Export All DataBase Objects into Text Files, which was posted on 05/10/2004. This is exactly the type of thing I need.

I used your code and changed sExportLocation from "C:\Temp\" to my server location.

I created a new module for this code. All compiled with no errors.

The proplem is that no txt files are being created. I'm not receiving any Error messages, or message boxes.

So to be honest I'm baffled as I have gone through the code with a toothbrush and cannot seem to find any issues.

If you have any ideas I would really appreciate it.

Thanks in advance.

 

[mdemarte]

Transfer Text

I 'inherited' a program that has part of this. I have a macro that copies the tables one at a time using Transfer Text. I Export Fixed Width, using a Specification Name (file layout that you can create, save and reuse) for one table at a time to a named text file. I have never tried to export anything except tables though.

Note: when this form is open, NONE of the other tables are in use. Maybe that is why your data is not moving over.

[I'm off for a few days, so if you need more info, I won't be getting back to you for a bit.]

 

[ghudson]

I used your code and changed sExportLocation from "C:\Temp\" to my server location.

Will it work if you change the sExportLocation location to a valid directory on your hard drive? Remember to end the string with a back slash"\".

If not, post your code so we can see if you have missed something when you revised my code.

 

[mmmcpher]

Txt Files

Hi Guys,

Thanks for the responses.

Mdemarte, I tried your idea of using a macro which worked no issues with that. Thanks.

Although the macro is working I would like to understand as to why GHUDSON's code did not work for me.

Attached is the code which I have used, thanks to GHUDSON. My sExportLocation is "C:\TxtFiles\"


Option Compare Database
Option Explicit

Public Sub ExportDatabaseObjects()
On Error GoTo Err_ExportDatabaseObjects

Dim Db As Database
'Dim db As DAO.Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String

Set Db = CurrentDb()

sExportLocation = "C:\TxtFiles\" 'Do not forget the closing back slash! ie: C:\Temp\


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

Set c = Db.Containers("Forms")
For Each d In c.Documents
Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt"
Next d
'Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt" - Loads the txt files.
Set c = Db.Containers("Reports")
For Each d In c.Documents
Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & d.Name & ".txt"
Next d

Set c = Db.Containers("Scripts")
For Each d In c.Documents
Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & d.Name & ".txt"
Next d

Set c = Db.Containers("Modules")
For Each d In c.Documents
Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & d.Name & ".txt"
Next d

For i = 0 To Db.QueryDefs.Count - 1
Application.SaveAsText acQuery, Db.QueryDefs(i).Name, sExportLocation & "Query_" & Db.QueryDefs(i).Name & ".txt"
Next i

Set Db = Nothing
Set c = Nothing

MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation

Exit_ExportDatabaseObjects:
Exit Sub

So you guys are aware I have called my module ExpDB_Txt

Thanks in Advance.

 

[ghudson]

You are missing some of the error handling at the bottom of your code.

I can not explain why but I also can not call the function from a form even though it is a public function. I moved the function to the form and was able to call it from the onclick event of a command button. Attached is a working Access 2003 example.

HTH

 

[mmmcpher]

txt files

Ghudson,

Thanks for your advice. As you suggested I placed your code on a ONCLICK event. It is now working perfect.

Thanks Again.

Have a Merry Christmas and a Happy New Year.

 

[papyturbo]

Hi, everybody, Hi, ghudson.

First of all, thanks for the code in ExportDatabaseObjects() (locked thread). It turned out extremely useful in searching the contents of a very old Axs97 application.

Running your code, I ran into several problems including crashes, file name errors, ...

1- Crashes
Turned out to be due to corrupt forms and/or main forms containing corrupt subforms. These could not be opened or accessed in the database window.
I have succesfully used the code below to check for those, identify and delete the corrupt objects.
Will let anyone improve it with other similar objects (reports...) which had no problem in my case :

Public Sub TestCorruptForms()
Dim AO As AccessObject

    For Each AO In CurrentProject.AllForms
        CorruptForm AO.Name
    Next
    'Continue with reports, modules... ?
    'for each ao in CurrentProject.AllReports
        'CorruptReport AO.Name
    'For Each AO In CurrentProject.AllMacros
    'For Each AO In CurrentProject.AllModules
    
End Sub

Private Function CorruptForm(FormName As String) As Boolean
    On Error GoTo IsCorrupt
    DoCmd.OpenForm FormName, acDesign
    DoCmd.Close acForm, FormName
    CorruptForm = True 'en option, renvoie OK
Finally:
    Exit Function    '===========================
IsCorrupt:
    '2004         Mémoire insuffisante pour exécuter cette opération. Fermez les applications superflues, puis tentez à nouveau l'opération.
    If Err = 2004 Then
        Debug.Print "**** Form [" & FormName & "] is either kaputt, or contains a reference to a kaputt object (subform...)! (Error " & Err & ") To be repaired, cleaned or deleted -------"
        Resume Finally 'returns False
    Else
        Debug.Print Err, Err.Description
        'Stop
        'Resume
        MsgBox Err & ":" & Err.Description, , "An other error :o)"
    End If
End Function

2- Filenames
I found the most awkward query names, including "?" and double quotes!
Would you believe these queries run fine in Access? They do!
But hard time trying to create a file name with that.
So I added a simple function to your code :

'--------------------------------------------------
' Author    : ghudson.  Sub copied from: topic 99179 on access-programmers.co.uk (full link removed due to my low post count :o)
'             Thread name: Export All Database Objects Into Text Files
'Modified   : papyturbo.  Added CleanFileNames() control + export in a subdir, inside the current project's.
'--------------------------------------------------
Public Sub ExportDatabaseObjects(Optional ExportTables As Boolean = False)
    On Error GoTo Err_ExportDatabaseObjects

    Dim db As Database
    'Dim db As DAO.Database
    Dim td As TableDef
    Dim d As Document
    Dim C As Container
    Dim i As Integer
    Dim sExportLocation As String

    Set db = CurrentDb()

    sExportLocation = CurrentProject.Path & "\++ ExportText\"   'Do not forget the closing back slash! ie: C:\Temp\
    If ExportTables Then
        Debug.Print "Exporting Tables..."
        For Each td In db.TableDefs    'Tables
            If Left(td.Name, 4) <> "MSys" Then
                'will not work on french Windows/Office.
                'Error 3441         Le séparateur du champ de spécification du fichier texte est identique au séparateur décimal ou au délimiteur de texte.
                ' /*error 3441 message is bull....., in this case
                DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
            End If
        Next td
    End If

    Debug.Print "Exporting Forms..."
    Set C = db.Containers("Forms")
    For Each d In C.Documents
        Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & CleanFileNames(d.Name) & ".txt"
    Next d

    Debug.Print "Exporting Reports..."
    Set C = db.Containers("Reports")
    For Each d In C.Documents
        Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & CleanFileNames(d.Name) & ".txt"
    Next d

    Debug.Print "Exporting Macros..."
    Set C = db.Containers("Scripts")
    For Each d In C.Documents
        Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & CleanFileNames(d.Name) & ".txt"
    Next d

    Debug.Print "Exporting Modules..."
    Set C = db.Containers("Modules")
    For Each d In C.Documents
        Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & CleanFileNames(d.Name) & ".txt"
    Next d

    Debug.Print "Exporting Queries..."
    For i = 0 To db.QueryDefs.Count - 1
        Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & CleanFileNames(db.QueryDefs(i).Name) & ".txt"
    Next i

    MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation

Finally:
    On Error Resume Next
    Set db = Nothing
    Set C = Nothing
    Exit Sub    '======================================
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    'added by papyturbo:
    Debug.Print Err, Err.Description
    Stop
    Resume
    Resume Finally
End Sub

'--------------------------------------------------
' 2011/02/15
' Author    : papyturbo.  Provides proper names for Windows files.
'             You may change the replacement character as wanted, provided it is a legal character...
'--------------------------------------------------
Private Function CleanFileNames(ObjectName As String) As String
Const REPLACEMENT$ = "_"
    CleanFileNames = Replace(ObjectName, "/", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, "\", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, ":", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, "*", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, "?", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, """", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, "<", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, ">", REPLACEMENT)
    CleanFileNames = Replace(CleanFileNames, "|", REPLACEMENT)
End Function

4- Bonus: instant search within every property of every object
It seems this code was mostly used to save a database's contents, and it is a great way to do it.
But, using Windows 7 (or any WindowsSearch/DesktopSearch engine), it turned out to be a fabulous development tool, to analyse someone else's code. I was searching for the use of a particular macro and, bing! within 20 seconds, I had the names of the buttons on the forms which used that macro (Had been searching for 6 hours with other tools!)

5-Errors exporting tables' contents.
As mentioned in the code above, I could not export the 'TransferText' part.
Did not insist though, since I had no use for it.
If I find time, I believe I would rather create a separate function dedicated to DATAbases : export all Tables' + fields' + relations' properties as well as debug the ExportText function (I suspect the problem to be with the non-english version of Windows/Access, but?)

Cheers,

Etienne

 

[Bobble]

Hello!

I use this code to export all my objects. But I have some "news" query with the name like "Query_~sq_cdd~sq_cfDefectReportRows_In.txt".

I don't have a query called "~sq_cdd~sq_cfDefectReportRows_In" in my programm. But I have a form called "fDefectReportRows_In".

What is this type of query?

Thanks

 

[papyturbo]

Hi Bobble,
query names with "~" are very common in access, if you look through the entire collection of querydefs. They are the temporary queries, created and stored by Access itself, when you use an SQL string in a property (source of a form, combo...).
For this one, you should be able to compare the SQL content of the query with the source of your form, or anything else within that form?

 

[Bobble]

Thank you for your answer!
I tried to export all my access application (forms, queries, modules). And I have some queries like this ( "~sq_cdd~sq_cfDefectReportRows_In" ).

Now, I will import the application in a new access file and I use:

Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt"
Application.LoadFromText acModule, "YourModuleName", "C:\Temp\Module_modTest.txt"
Application.LoadFromText acQuery, "YourQueryName", "C:\Temp\Query_qTest.txt"

Need I to import the query with "~", too ? If yes how can I import them? Because if I use the "LoadFromText acQuery" the query is imported like a "normal" query.
Or don't I need to import it? Or are there an other mode to do this?

Thanks

 

[papyturbo]

Hi,

no idea!

Besides, I don't see why you would export to text, then import from text, rather than directly create a new blank file and import from the old file?

If you import all objects directly through Access, it will certainly do it properly.

 

[Bobble]

I would export to text file to use SVN to manage the project versions. But, it is true, I can export all, compare and import only modified files directly from the Access file.

Thanks a lot!

 

[lutajuca]

Message "Hello lutajuca it appears that you have not posted on our forums in several weeks, why not take a few moments to ask a question, help provide a solution or just engage in a conversation with another member in any one of our forums? " led me to write these lines ...

In these forums, I learned a lot .... and finding good ideas .... This topic has helped me and I made myself a tool for searching my applications, those that I developed ... and all in one place .... Thank you!