export table....pls help me....

[erin_0502]

can i export specific column or selected column to text file???????????

 

[Rabbie]

Just use a MakeTable query to copy the column(s) you want to export to a temporary table. Then use DoCmd.TransferText to export the data to your text file.

 

[Dennisk]

you could do away with the tempory table and just use the query. Don't forget a query is a record sourse too.

 

[erin_0502]

thank for your attention.
can u give me example to make table query, because i already have coding for export text...

 

[Rabbie]

As Dennisk said you dont need to make the table just make an ordinary query that selects the column(s) you need and save it. Use the query name instead of a table name when you export the data.

 

[erin_0502]

sory....i dont want using the manual step....
but using coding visual basic....
can u help me....

 

[Rabbie]

If you read the Access help for DoCmd.TransferText you will see that you need to supply either a Table Name or a Query Name. "Instead of using an SQL statement, you must first create a query and then specify the name of the query in the Table Name argument." you could update the querydef of your query to change the SQL in VBA but you would need to look in Access help and other places to see how to do that.

If it is always the same column that you are exporting then just set up the query once and trigger the export from the VBA on your form.

 

[Ian Mac]

Hi,

Firstly, please don't post related questions in 2 different threads, reading this one doesn't allow anyone to help you with the code you have in the other one. (I they aren't related I appologise)

Secondly, please please please, do not have titles like "pls help me...", I don't usually bother with them as I have to go into each one to find out what the problems are. If it says 'help with api..." then it's not something I need to read as i don't have the knowledge.
Also, urgent isn't something I tend to bother with, everyone has urgent requests.

That said,

the following code may help you:

Public Sub Main()

Dim db                  As DAO.Database
Dim strFilename         As String
Dim strExportLocation   As String
Dim strSQL              As String
Dim qdfOutput           As DAO.QueryDef

Set db = DBEngine(0)(0)

strSQL = "SELECT YourField1, YourField2, YourField3, "
strSQL = strSQL & "YourField4, YourField5, YourField6, "
strSQL = strSQL & "YourField7, YourField8, ETC FROM YourTable"""

Set qdfOutput = db.CreateQueryDef("qryTemp", strSQL)
sExportLocation = "Z:\"

strFilename = strExportLocation & "MyTestTextFile.txt"
DoCmd.TransferText acExportDelim, , "qryTemp", strFilename, True

DoCmd.DeleteObject acQuery, qryTemp

Set qdfOutput = Nothing
Set db = Nothing

End Sub

This creates a temporary query to export, then deletes it.
Something to be mindful of, this will cause the database to grow in size, you will need to compact regularly if you run this quite often.

Also, can I ask why you want to export MSys tables to text files (other thread)

Cheers,