CODEWITHSUNDEEP
ms-accessvba
Paolo Donatelli
Paolo Donatelli

Reputation: 31

Export all tables to txt files with export specification

I have a Access DB containing several different tables, each with a different structure (number & names of fields, number of rows, title).

What I would like to do is to export all these tables into txt files, with a given separator ("|"), point as decimal separator, quotes for strings.

I have browsed the internet and what I got was:

I get an error messagge ("object does not exist") and I think it is related to the fact that the export specification is "sheet-specific", i.e. does not apply to tables with different fields and fieldnames.

Can you help me? thanks!!

EDIT. I post also the original code I run. As I said before, I am new to VBA, so I just looked for a code on the web, adapted it to my needs, and run.

Public Sub ExportDatabaseObjects()
On Error GoTo Err_ExportDatabaseObjects

Dim db As Database
Dim db As DAO.Database
Dim td As TableDef
Dim sExportLocation As String
Dim a As Long

Set db = CurrentDb()

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

For a = 0 To db.TableDefs.Count - 1
    If Not (db.TableDefs(a).Name Like "MSys*") Then
        DoCmd.TransferText acExportDelim, "Export_specs",  db.TableDefs(a).Name, sExportLocation & db.TableDefs(a).Name & ".txt", True
    End If
Next a

Set db = 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

Before running the code, I manually exported the first table saving the Export_specs to a file.

Consider a db with two tables, A and B. When I run the code A is properly exported, then I get the following errore message "3011 - The Microsoft Access database engine could not find the object 'B#txt'. Make sure the object exists and that you spell its name and the path name correctly. If 'B#txt' is not a local object, check your network connection or contact the server administration".

Upvotes: 0

Views: 1721

Answers (1)

Peter Lake
Peter Lake

Reputation: 338

So, it's kind of complex. I've created a routine that imports files using ImportExport Specs, you should be able to easily adapt to your purpose. The basic operation is to create a spec that does exactly what you want to one file. Then, export this spec using this code:

Public Function SaveSpecAsXMltoTempDirectory(sSpecName As String)

Dim oFSO As FileSystemObject
Dim oTS As TextStream

Set oFSO = New FileSystemObject
Set oTS = oFSO.CreateTextFile("C:\Temp\" & sSpecName & ".xml", True)
oTS.Write CurrentProject.ImportExportSpecifications(sSpecName).XML

oTS.Close
Set oTS = Nothing
Set oFSO = Nothing

End Function

Then open this file in Notepad and replace the file name with some placeholder (I used "FILE_PATH_AND_NAME" in this sample). Then, import back into database using this code:

Public Function SaveSpecFromXMLinTempDirectory(sSpecName As String)

Dim oFSO As FileSystemObject
Dim oTS As TextStream
Dim sSpecXML As String
Dim oSpec As ImportExportSpecification

Set oFSO = New FileSystemObject
Set oTS = oFSO.OpenTextFile("C:\Temp\" & sSpecName & ".xml", ForReading)
sSpecXML = oTS.ReadAll
For Each oSpec In CurrentProject.ImportExportSpecifications
    If oSpec.Name = sSpecName Then oSpec.Delete
Next oSpec
Set oSpec = CurrentProject.ImportExportSpecifications.Add(sSpecName, sSpecXML)

Set oSpec = Nothing
oTS.Close
Set oTS = Nothing
Set oFSO = Nothing

End Function

Now you can cycle thru the files and replace the placeholder in the spec with the filename then execute it using this code:

Public Function ImportFileUsingSpecification(sSpecName As String, sFile As String) As Boolean

Dim oSpec As ImportExportSpecification
Dim sSpecXML As String
Dim bReturn As Boolean

'initialize return variable as bad until function completes
bReturn = False
'export data using saved Spec
'   first make sure no temp spec left by accident
For Each oSpec In CurrentProject.ImportExportSpecifications
    If oSpec.Name = "Temp" Then oSpec.Delete
Next oSpec
sSpecXML = CurrentProject.ImportExportSpecifications(sSpecName).XML
If Not Len(sSpecXML) = 0 Then
    sSpecXML = Replace(sSpecXML, "FILE_PATH_AND_NAME", sFile)
    'now create temp spec to use, get template text and replace file path and name
    Set oSpec = CurrentProject.ImportExportSpecifications.Add("Temp", sSpecXML)
    oSpec.Execute
    bReturn = True
Else
    MsgBox "Could not locate correct specification to import that file!", vbCritical, "NOTIFY ADMIN"
    GoTo ExitImport
End If

ExitImport:
    On Error Resume Next
    ImportFileUsingSpecification = bReturn
    Set oSpec = Nothing
    Exit Function
End Function

Obviously you'll need to find the table name in the spec XML and use a placeholder on it as well. Let me know if you can't get it to work and i'll update for export.

Upvotes: 2

Related Questions