Export Access Data with VBA

[bitwise]

I want to export some access table data into a *.csv file. I wrote the following script:

Sub ExportAllTables()
Dim objAccess As AccessObject, obj As Object
Set obj = Application.CurrentData
Dim strFile As String

'loop through all the tables in the db and export the data
For Each objAccess In obj.AllTables
strFile = "C:\" & objAccess.Name & ".csv"

'if it is not a microsoft system table then export
If (StrComp(Left$(objAccess.Name, 4), &quot;MSys&quot;, vbTextCompare) <> 0) Then
DoCmd.TransferText acExportDelim, , objAccess.Name, strFile, False
End If
Next objAccess
End Sub

Ok, that works great. The problem is I want to export the text with the text qualifier set to {none}. Currently text in the file looks like this:

1,&quot;Text&quot;,121,&quot;More Text&quot;

I want it to look like this:

1,Text,121,More Text

How can I modify my script above to do this generically.

Thanks,
-bitwise

 

[CPUburn]

hi,

don't know if this will work, but when you manually IMPORT (this runs a little wizard) a text file, you're able to save the specifics for it under a given name (advanced), I would give it a try, you can there set the text delimiter to nothing and then add the parameter to the export you're carrying out.
Can't test it right now; have 2K and 97 both installed (for some customer who wanted to stay in 97) and some functionalities are &quot;a little&quot; messed up...

i'd appreciate a reply if it works though!

hope you get there this way.

cpuburn

 

[bitwise]

Yeah, I've tried that. You can make a export specification for each table and pass it to the second paramater of the TransferText function but that is time consuming and annoying. I have a crap load of tables to export I simply want to specify that the text qualifier be set to {none} for every table. Is it possible to make a generic export specification in access and use that for each table?

Thanks,
-bitwise

 

[Kib]

Heya
You might want to try investigating schema.ini files
I believe it can be used to give more specifics to import/export in more general terms, not table by table.
Although not sure if it has the option for the text qualifier. Worth looking maybe though.
Also, is this reb?

Hope it works.
-Kib

 

[CPUburn]

hi,

sorry, lost track of the thread --> what Kib said is what I meant, but I didn't describe it all too well..

CPU-burn