Export table fields !

[achalrikhi]

Hello Friends,

I had an issue for exporting access table data to a text file format but I found a simple solution now. Like this :

DoCmd.TransferText acExportDelim, "exportdata", "Data", "C:\Users\welcome\Desktop\output.txt", True

this is working very fine. Only issue is that I do not want certain columns from the table and I don't know how to use select statement and where ? for eg.

I want a query like this :

Select Field 1, Field 3, Filed 4 from < TABLE NAME > where SERIES ( Table field ) = "EQ"

Please advice that how and where to put this query to export data along with Do.Cmd statement.


Regards

Achal

 

[Joe4]

Just create a query that has only the fields you want to export.
Then, just select that name of the Query in the TransferText command instead of the Table name (you can export queries just as easily as you can export tables).

 

[achalrikhi]

Dear Joe,

Thank you very much for suggesting this simple and beautiful way. I did that but then the error occurs as :

Run-time error '3011' :
The Microsoft Office Access Database engine could not find the object 'Output.txt'. Make sure the object exists and that you spell its name and the path name correctly.

It might look foolish to ask this simple thing but how can it be ? It can not exist as I want to create that output and these all code is for that. How can it exist before I click the button ?

Please advice

Regards

Achal

 

[Joe4]

Run-time error '3011' :
The Microsoft Office Access Database engine could not find the object 'Output.txt'. Make sure the object exists and that you spell its name and the path name correctly.

It might look foolish to ask this simple thing but how can it be ? It can not exist as I want to create that output and these all code is for that. How can it exist before I click the button ?

I think that is a "red herring" error message. Usually when I see something like that, it is because there is actually an issue with the file path you are trying to use, either it is incorrect (maybe a typo), or you are trying to write to a folder that you do not have access or write permissions to.

 

[achalrikhi]

I think that is a "red herring" error message. Usually when I see something like that, it is because there is actually an issue with the file path you are trying to use, either it is incorrect (maybe a typo), or you are trying to write to a folder that you do not have access or write permissions to.

Then how come it does not give error with the old code ? The old code generates the "Output.txt". ( Though it is not as per my requirement )

Am I making a mistake in coding ? I could not find any spelling mistake either.

Achal

 

[Joe4]

Does you query run without errors?
Can you post the line of exact line of code that you are trying to run to export your query?

 

[achalrikhi]

Does you query run without errors?
Can you post the line of exact line of code that you are trying to run to export your query?

Sure sir :

These are the lines I am using for exporting the data :

Private Sub CmdOutput_Click()


Select Case MsgBox(" Do you want to convert CSV file to text file ?", vbYesNo)
Case vbYes
DoCmd.TransferText acExportDelim, "exportdata", "Data", "C:\Users\welcome\Desktop\output.txt", True
MsgBox (" CSV file converted to text file successfully ")
End Select


End Sub

"Data" is a table name & "exportdata" is a specification.

Achal

 

[Joe4]

I thought that you were trying to export the Query now, not the table?
If so, that need to be the name of the query.
Also, your query will have a different export specification than the table did.
Did you create a new export specification for the query export?
Then you need to be sure to update that argument of the command to use that specification.

 

[achalrikhi]

I thought that you were trying to export the Query now, not the table?
If so, that need to be the name of the query.
Also, your query will have a different export specification than the table did.
Did you create a new export specification for the query export?
Then you need to be sure to update that argument of the command to use that specification.

Dear Joe,

You are absolutely right, I am using the old specification made for the table. I am making a new specification and use it accordingly. I will be posting the result. Thank you so much for advising and helping.

Regards

Achal

 

[achalrikhi]

Dear Joe,

Hurray... ! I made a new specification for the new query and wow...! it worked like magic. Thank you so much. Only the Date column have the format like " 6/1/2015 0:00:00 " Can this be as simple as " 6/1/2015 " ?

And I just wanted to know if it is possible to duplicate the first column and paste it in the last column and then change all the headers name in output ? Is it possible by query in anyway ?

Regards
Achal