Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 12-20-2005, 10:38 AM   #1
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 77 Times in 45 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
Export All Database Objects Into Text Files

The ExportDatabaseObjects() function will allow you to export all of your database objects into individual text files for each object [tables, forms, queries, macros, modules]. You can specify where the text files will be exported to by changing the sExportLocation string to a valid path. The Application.SaveAsText method is another undocumented Access option. The exported text files contain everything [properties, VBA, data, etc.] about the object. Similar to the Access documenter but better and this allows you to export the objects and then import them into the same database or any other database of your choosing. You can reverse the method and import [an exported] a text object by switching the Application.SaveAsText with Application.LoadFromText. The ExportDatabaseObjects() function can be used as an alternative method to back up your database objects.

Code:
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:\Temp\" '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
    
    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
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
    
End Sub
This is how you import a text file that was exported using the Application.SaveAsText method...
Code:
Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt"

__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline  
The Following User Says Thank You to ghudson For This Useful Post:
JamesSF (05-05-2014)
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export All Database Objects Into Text Files mmmcpher General 12 09-12-2012 06:28 AM
Problem Export Table binary instead of text DanSmith Tables 0 11-22-2005 11:56 PM
[SOLVED] Can Access read information from unformatted text files? bURKeNSTiEN General 3 01-01-2003 01:22 PM
Export to Comma Delimited Text Cindy_B General 7 12-10-2002 07:24 AM
[SOLVED] Importing Ole Objects (Word files) into SQL Server from Access 97 AlistairG General 0 11-25-2002 03:55 AM




All times are GMT -8. The time now is 12:50 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World