By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

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

P: 24
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

Expand|Select|Wrap|Line Numbers
  1. Function TXTsendTblGlobFactorFinLev()
  2.   DoCmd.TransferText transfertype:=acExportDelim, _
  3.    specificationname:="GlobFactorFinLev Export Specification", _
  4.    TableName:="GlobFactorFinLev", _
  5.    Filename:="C:\MATLAB\DB\GlobFactorFinLev.txt", _
  6.    hasfieldnames:=True
  7. End Function
Mar 24 '10 #1

✓ answered by NeoPa

Not straightforwardly 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.

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Not straightforwardly 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.
Mar 24 '10 #2

P: 24
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
Mar 24 '10 #3

NeoPa
Expert Mod 15k+
P: 31,494
Sorry the answer was not more hopeful.
Mar 24 '10 #4

P: 24
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.
Mar 24 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 24 '10 #6

P: 24
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.
Expand|Select|Wrap|Line Numbers
  1. ------------------------------------------------------------------------------------------
  2. |    Barcode     |             Descr             |    Item_Price     |      Inv_Qty      |
  3. ------------------------------------------------------------------------------------------
  4. |         100001 | LEAD ROOF                     |            $11.00 |               990 |
  5. ------------------------------------------------------------------------------------------
  6. |         100004 | W/M CPVC W/AIR CHAMBERS       |            $22.00 |               980 |
  7. ------------------------------------------------------------------------------------------
  8. |         100005 | W/M PEX W/AIR CHAMBERS        |             $1.00 |               990 |
  9. ------------------------------------------------------------------------------------------
  10. |         100006 | ICE CPVC W/AIR CHAMBER        |             $2.00 |               970 |
  11. ------------------------------------------------------------------------------------------
  12. |         100007 | ICE COPPER W/AIR CHAMBER      |             $3.00 |               990 |
  13. ------------------------------------------------------------------------------------------
  14. |         100008 | STUD 2-H FOR WOOD             |             $4.00 |               980 |
  15. ------------------------------------------------------------------------------------------
  16. |         100010 | STRAINERS 2-3 ALL PURPOSE     |             $6.00 |               990 |
  17. ------------------------------------------------------------------------------------------
  18. |         100011 | 2-H MICKEY                    |             $7.00 |               977 |
  19. ------------------------------------------------------------------------------------------
  20. |         100012 | STOUT BRACKET 10-18 SPAN      |             $8.00 |               990 |
  21. ------------------------------------------------------------------------------------------
  22. |         100013 | 2-H STRAIGHT                  |             $9.00 |               990 |
  23. ------------------------------------------------------------------------------------------
  24. |         100014 | 0630-C2814 H/B COPPER         |            $11.00 |               990 |
  25. ------------------------------------------------------------------------------------------
  26. |         100015 | 15X60 PROTECTIVE LINER        |             $4.90 |               990 |
  27. ------------------------------------------------------------------------------------------
  28. |         100016 | LH CAST IRON VILLAG 60X30     |           $264.60 |               990 |
  29. ------------------------------------------------------------------------------------------
Attached Files
File Type: txt Marcin1.txt (2.6 KB, 286 views)
Mar 24 '10 #7

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

Marcin
Mar 24 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
@mabrynda
Sounds like a good idea Marcin :)
Mar 24 '10 #9

ADezii
Expert 5K+
P: 8,638
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.
Mar 24 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.