How to export ACCESS table with varying dimension to a .txt file using VBA

[mabrynda]

Good morning everybody,
I have an ACCESS project, in which I was expotring some tables as TXT files to be used as input for some MATLAB code. Until now, these tables were "static" - means the number of columns and the column names were always the same for a given table. I was using then short exporting modules (example of code below) that I wrote for each table after saving the Export Specification for each table in the Export wizzard. Everything was working smoothly, but now I have one table, which is created by the user based on his selection of several fields. This means I have a table to export, which still has a same name but in which the number of fields and their names change everytime the routine is ran. The exporting module does not work anymore since the Export Specification is not the same anymore (I guess this is the problem...). Is there any way to circumvent this?

Thanks in advance for any suggestion,

Marcin

Function TXTsendTblGlobFactorFinLev()
  DoCmd.TransferText transfertype:=acExportDelim, _
   specificationname:="GlobFactorFinLev Export Specification", _
   TableName:="GlobFactorFinLev", _
   Filename:="C:\MATLAB\DB\GlobFactorFinLev.txt", _
   hasfieldnames:=True
End Function

[NeoPa]

Not straightforward ly no.

I wouldn't like to take you through a process of amending stored Import/Export specs, assuming that would even work.

It's not something supported by Access natively.

[mabrynda]

Thanks NeoPa.
Well, I will have to do it manually I guess...It's not such a big deal, since I have only one special table to export.

Thanks again,

Marcin

[NeoPa]

Sorry the answer was not more hopeful.

[mabrynda]

Hi NeoPa,
I think I found the way to do it. It's a little tricky but should work fine. I can export any table (with no export specs) to TXT file using DoCmd.OutputTo. The problem is that the resulting txt file has some sort of "lines" from the ACCESS table. But I think, I will be able to elminate these "lines" with a short code written in MATLAB.

What about that?

M.

[NeoPa]

I didn't get any lines in my test, and I don't know what MATLAB is, but it sounds like you have a very workable solution there if you can do that. Well done.

[mabrynda]

Hey,
Attached is an example of how my file looks like if it is exported with DoCmd.OutputTo. But I think I will get it clean after MATLAB treatment...

M.

------------------------------------------------------------------------------------------
|    Barcode     |             Descr             |    Item_Price     |      Inv_Qty      |
------------------------------------------------------------------------------------------
|         100001 | LEAD ROOF                     |            $11.00 |               990 |
------------------------------------------------------------------------------------------
|         100004 | W/M CPVC W/AIR CHAMBERS       |            $22.00 |               980 |
------------------------------------------------------------------------------------------
|         100005 | W/M PEX W/AIR CHAMBERS        |             $1.00 |               990 |
------------------------------------------------------------------------------------------
|         100006 | ICE CPVC W/AIR CHAMBER        |             $2.00 |               970 |
------------------------------------------------------------------------------------------
|         100007 | ICE COPPER W/AIR CHAMBER      |             $3.00 |               990 |
------------------------------------------------------------------------------------------
|         100008 | STUD 2-H FOR WOOD             |             $4.00 |               980 |
------------------------------------------------------------------------------------------
|         100010 | STRAINERS 2-3 ALL PURPOSE     |             $6.00 |               990 |
------------------------------------------------------------------------------------------
|         100011 | 2-H MICKEY                    |             $7.00 |               977 |
------------------------------------------------------------------------------------------
|         100012 | STOUT BRACKET 10-18 SPAN      |             $8.00 |               990 |
------------------------------------------------------------------------------------------
|         100013 | 2-H STRAIGHT                  |             $9.00 |               990 |
------------------------------------------------------------------------------------------
|         100014 | 0630-C2814 H/B COPPER         |            $11.00 |               990 |
------------------------------------------------------------------------------------------
|         100015 | 15X60 PROTECTIVE LINER        |             $4.90 |               990 |
------------------------------------------------------------------------------------------
|         100016 | LH CAST IRON VILLAG 60X30     |           $264.60 |               990 |
------------------------------------------------------------------------------------------

[mabrynda]

By the way, MTLAB is a pretty powerful software for mathematics/physics/ingeneering applications.
Here is their website: http://www.mathworks.c om

Marcin

[NeoPa]

But I think I will get it clean after MATLAB treatment...

Sounds like a good idea Marcin :)

[ADezii]

If MATLAB will accept it, you can use VBA Code to Export the Data contained in your Table to a Delimited Text File. Neither the Number of Fields nor their Names would matter, since this info can be easily extracted from the Recordset created from the Table to do the actual Export. I won't even attempt this unless I know that a Delimited Format will be acceptable by MATLAB. A Fixed-Width Format could also be generated but would be more complicated. Just let me know.