Export all data in seperate files

[millertimecu32]

I am trying to export data from a query that will export the data into multipal tables.......... so i want all information from variable a to be on its own table and then all information from variable b to be on its on table........
I was thinking that this could be done throught a macro.... but wasn't sure if it can be done in a query?

 

[millertimecu32]

I am trying to export data from a query that will export the data into multipal tables.......... so i want all information from variable a to be on its own table and then all information from variable b to be on its on table........
I was thinking that this could be done throught a macro.... but wasn't sure if it can be done in a query?

Ignore question i found a work around.

Thanks

 

[MSAccessRookie]

Ignore question i found a work around.

Thanks

Work arounds are good, but if you allow us to view the work around, it might provide additional insight that could be used toward finding a proper solution.

 

[millertimecu32]

Yes you are correct about work arounds. I really didn't fix the problem. I just started exporting the data one query at a time. (wrote one query that i had to define a Parameter). Then export each query individually.

Any sugestions?

 

[mikebaldam]

Heres a module to do the work for you..

it backsup everything in the database (tables, queries, forms, modules ... everything) if u have any hassle let me know n ill give u an example mdb

Call it with...

Call ExportDatabaseObjects

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
    Dim sDate As String
    
    Set db = CurrentDb()
    sDate = Format(Now, "yyyymmdd-hhnnss")
    
    
    sExportLocation = "C:\Program Files\IT4Life\backups\" & sDate & "\" 'Do not forget the closing back slash! ie: C:\Temp\
    
    'Create Folder
    Dim FSO As New FileSystemObject
    Dim strPath As String, strCopy As String

    strPath = sExportLocation
    
    'MsgBox strPath
    If FSO.FolderExists(strPath) = True Then
      ' MsgBox "folder exsits"
       ' FSO.DeleteFolder (strPath)
    Else
        FSO.CreateFolder (strPath)
    End If
    
    
    
    For Each td In db.TableDefs 'Tables
        If Left(td.Name, 4) <> "MSys" Then
            DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & sDate & "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 & sDate & "Form_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Reports")
    For Each d In c.Documents
        Application.SaveAsText acReport, d.Name, sExportLocation & sDate & "Report_" & Replace(d.Name, "/", "-") & ".txt"
    Next d
    
    Set c = db.Containers("Scripts")
    For Each d In c.Documents
        Application.SaveAsText acMacro, d.Name, sExportLocation & sDate & "Macro_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Modules")
    For Each d In c.Documents
        Application.SaveAsText acModule, d.Name, sExportLocation & sDate & "Module_" & d.Name & ".txt"
    Next d
    
    For i = 0 To db.QueryDefs.Count - 1
        Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & sDate & "Query_" & Replace(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, "Data Backed Up"
    
    ''''''How to import
    ''''''
    ''''''Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt"
    
    'SaveSetting "Environment-Wales", "Options", "Backup", "0"
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblDBOptions SET tblDBOptions.BackupCount = 0 WHERE (((tblDBOptions.AutoID)=1));" 'update backup no
    DoCmd.SetWarnings False


Exit_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox err.Number & " - " & err.Description
    Resume Next
   ' Resume Exit_ExportDatabaseObjects
    
End Sub