当前位置: 动力学知识库 > 问答 > 编程问答 >

Export Access table to delimited txt file using VBScript

问题描述:

I need export my table from DB Access 2007 to delimited txt file with ; using VBScript.

My code below:

 Set accDB = CreateObject("Access.Application")

accDB.visible = true

accDB.automationsecurity = 1

accDB.OpenCurrentDatabase("D:\Users\db2015.mdb")

accDB.DoCmd.TransferText acExportDelim, ";", "tb2015", "D:\Users\tb2015.txt", False

accDB.CloseCurrentDatabase

accDB.Quit

Set accDB = Nothing

But I have error: tb2015.txt not found

I have tried add in my code this:

 accDB.DoCmd.OpenQuery "SelectQuery", acNormal, acEdit

accDB.DoCmd.OutputTo acOutputTable, "tb2015", "txt", "D:\Users\tb2015.txt"

In this case I don't have error but the tb2015.txt is delimited with | and not with ;

Please help me.

Thank you in advance.

Edit #1

This is the new code, but I have error:

 Set accDB = CreateObject("Access.Application")

accDB.visible = true

accDB.automationsecurity = 1

accDB.OpenCurrentDatabase("D:\Users\db2015.mdb")

Const acExportDelim = 2

accDB.DoCmd.TransferText acExportDelim, , "tb2015", "D:\Users\tb2015.txt", False

accDB.CloseCurrentDatabase

accDB.Quit

Set accDB = Nothing

Edit #2

The solution is this code:

 Const acExportDelim = 2

Set accDB = CreateObject("Access.Application")

accDB.visible = true

accDB.automationsecurity = 1

accDB.OpenCurrentDatabase("D:\Users\db2015.mdb")

accDB.DoCmd.OpenQuery "myView", acNormal, acEdit

accDB.DoCmd.TransferText acExportDelim, "tb2015", "tb2015", "D:\Users\tb2015.txt"

accDB.CloseCurrentDatabase

accDB.Quit

Set accDB = Nothing

网友答案:

What happens here is:

VBScript doesn't know acExportDelim, so it is interpreted as unknown variable, with value 0.

In the context of DoCmd.TransferText, 0 = acImportDelim.

So it tries to import tb2015.txt, and you get the "file not found" error.

Copy Const acExportDelim = 2 from the Access Object catalog, and add it to your script. And remove the ";" SpecificationName parameter.

Note: acOutputTable = 0, that's why your DoCmd.OutputTo runs (by accident).

To avoid such errors, add Option Explicit to the top of your script, it enforces variable declaration.

分享给朋友:
您可能感兴趣的文章:
随机阅读: