How do I save a query as a text file

[steve goodrich]

I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate the
fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve

 

[Douglas J. Steele]

You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" & [Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

 

[steve goodrich]

Thanks Douglas

I created the query as you suggested and it worked perfectly.
How do I export the query using the TransferText method?
And would it be possible to link it to a command button on my form?

Regards
Steve

You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" & [Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate the
fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve

 

[Douglas J. Steele]

Look up TransferText in the Help file.

The code in your button's Click event will be something like:

Private Sub MyButton_Click()

DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt"

End Sub

If you want to prompt the user for the name of the file to use, see
http://www.mvps.org/access/api/api0001.htm at "The Access Web". (Yes, I know
that's overwhelming looking, but all you need to do is copy everything
between Code Start and Code End and paste it into a new module. Once you've
done that, put code like the sample at the top of the page into your
routine)

Private Sub MyButton_Click

Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(myStrFilter, "Text Files (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If Len(strSaveFileName) > 0 Then
DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt"
End If

End Sub

Unfortunately, I don't believe you'll be able to get field names at the top
of the file.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas

I created the query as you suggested and it worked perfectly.
How do I export the query using the TransferText method?
And would it be possible to link it to a command button on my form?

Regards
Steve

You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" & [Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate the
fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve

 

[steve goodrich]

Douglas
I pasted your text into the buttons on click event and changed the text for
my own query and location but when I click the button an error message is
displayed stating "Compile error: Variable not defined" I click ok and it
takes me back to the vb page with "asExportDelim" highlighted
any idea what I'm doing wrong?
thanks
Steve

Look up TransferText in the Help file.

The code in your button's Click event will be something like:

Private Sub MyButton_Click()

DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt"

End Sub

If you want to prompt the user for the name of the file to use, see
http://www.mvps.org/access/api/api0001.htm at "The Access Web". (Yes, I
know that's overwhelming looking, but all you need to do is copy
everything between Code Start and Code End and paste it into a new module.
Once you've done that, put code like the sample at the top of the page
into your routine)

Private Sub MyButton_Click

Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(myStrFilter, "Text Files (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If Len(strSaveFileName) > 0 Then
DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt"
End If

End Sub

Unfortunately, I don't believe you'll be able to get field names at the
top of the file.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas

I created the query as you suggested and it worked perfectly.
How do I export the query using the TransferText method?
And would it be possible to link it to a command button on my form?

Regards
Steve

You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" &
[Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate the
fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve

 

[Douglas J. Steele]

Sorry, my typo. It's acExportDelim. (I told you you should look TransferText
up in the help file! <g>)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas
I pasted your text into the buttons on click event and changed the text
for my own query and location but when I click the button an error message
is displayed stating "Compile error: Variable not defined" I click ok and
it takes me back to the vb page with "asExportDelim" highlighted
any idea what I'm doing wrong?
thanks
Steve

Look up TransferText in the Help file.

The code in your button's Click event will be something like:

Private Sub MyButton_Click()

DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt"

End Sub

If you want to prompt the user for the name of the file to use, see
http://www.mvps.org/access/api/api0001.htm at "The Access Web". (Yes, I
know that's overwhelming looking, but all you need to do is copy
everything between Code Start and Code End and paste it into a new
module. Once you've done that, put code like the sample at the top of the
page into your routine)

Private Sub MyButton_Click

Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(myStrFilter, "Text Files (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If Len(strSaveFileName) > 0 Then
DoCmd.TransferText asExportDelim, , "NameOfQuery",
"C:\Folder\File.txt"
End If

End Sub

Unfortunately, I don't believe you'll be able to get field names at the
top of the file.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas

I created the query as you suggested and it worked perfectly.
How do I export the query using the TransferText method?
And would it be possible to link it to a command button on my form?

Regards
Steve

You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" &
[Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate
the fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve

 

[steve goodrich]

Hi Douglas

Corrected the typo and it worked - I'm nearly there!
In between our posts I've been trying different things to try and get this
to work, I created a macro using the transfer text action and added a button
to my form to run it. This works the same as the code you supplied me with.
Both methods place quotes at the beginning and end of the string of text.
I also opened my query and selected save as from the file menu, save as
type-text. When you click the Export button you get options, one of which
allows you to remove the quotes.- which is the default setting. (select none
from the Text Qualifier box)
I would prefer just to click a button on my form as this is a job that will
need doing ever 4 hours by 16 different people, some of which would not know
how to export the query manually.

The client has been very specific in how they want the text file, and they
don't want quotes.
My final question is there a small amendment that I could add to the macro
or code that will produce a text file with no quotes"

Thanks again for all your help

Regards

Steve

Sorry, my typo. It's acExportDelim. (I told you you should look
TransferText up in the help file! <g>)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas
I pasted your text into the buttons on click event and changed the text
for my own query and location but when I click the button an error
message is displayed stating "Compile error: Variable not defined" I
click ok and it takes me back to the vb page with "asExportDelim"
highlighted
any idea what I'm doing wrong?
thanks
Steve

Look up TransferText in the Help file.

The code in your button's Click event will be something like:

Private Sub MyButton_Click()

DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt"

End Sub

If you want to prompt the user for the name of the file to use, see
http://www.mvps.org/access/api/api0001.htm at "The Access Web". (Yes, I
know that's overwhelming looking, but all you need to do is copy
everything between Code Start and Code End and paste it into a new
module. Once you've done that, put code like the sample at the top of
the page into your routine)

Private Sub MyButton_Click

Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(myStrFilter, "Text Files (*.txt)",
"*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If Len(strSaveFileName) > 0 Then
DoCmd.TransferText asExportDelim, , "NameOfQuery",
"C:\Folder\File.txt"
End If

End Sub

Unfortunately, I don't believe you'll be able to get field names at the
top of the file.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Douglas

I created the query as you suggested and it worked perfectly.
How do I export the query using the TransferText method?
And would it be possible to link it to a command button on my form?

Regards
Steve

message You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" &
[Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate
the fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve

 

[Douglas J. Steele]

If you've save the specification that you create when you go through the
File menu, you can tell the TransferText action (or method) to use that
specification. You provide the name of the specification as the second
argument.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas

Corrected the typo and it worked - I'm nearly there!
In between our posts I've been trying different things to try and get this
to work, I created a macro using the transfer text action and added a
button to my form to run it. This works the same as the code you supplied
me with. Both methods place quotes at the beginning and end of the string
of text.
I also opened my query and selected save as from the file menu, save as
type-text. When you click the Export button you get options, one of which
allows you to remove the quotes.- which is the default setting. (select
none from the Text Qualifier box)
I would prefer just to click a button on my form as this is a job that
will need doing ever 4 hours by 16 different people, some of which would
not know how to export the query manually.

The client has been very specific in how they want the text file, and they
don't want quotes.
My final question is there a small amendment that I could add to the
macro or code that will produce a text file with no quotes"

Thanks again for all your help

Regards

Steve

Sorry, my typo. It's acExportDelim. (I told you you should look
TransferText up in the help file! <g>)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas
I pasted your text into the buttons on click event and changed the text
for my own query and location but when I click the button an error
message is displayed stating "Compile error: Variable not defined" I
click ok and it takes me back to the vb page with "asExportDelim"
highlighted
any idea what I'm doing wrong?
thanks
Steve
Look up TransferText in the Help file.

The code in your button's Click event will be something like:

Private Sub MyButton_Click()

DoCmd.TransferText asExportDelim, , "NameOfQuery",
"C:\Folder\File.txt"

End Sub

If you want to prompt the user for the name of the file to use, see
http://www.mvps.org/access/api/api0001.htm at "The Access Web". (Yes, I
know that's overwhelming looking, but all you need to do is copy
everything between Code Start and Code End and paste it into a new
module. Once you've done that, put code like the sample at the top of
the page into your routine)

Private Sub MyButton_Click

Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(myStrFilter, "Text Files (*.txt)",
"*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If Len(strSaveFileName) > 0 Then
DoCmd.TransferText asExportDelim, , "NameOfQuery",
"C:\Folder\File.txt"
End If

End Sub

Unfortunately, I don't believe you'll be able to get field names at the
top of the file.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Douglas

I created the query as you suggested and it worked perfectly.
How do I export the query using the TransferText method?
And would it be possible to link it to a command button on my form?

Regards
Steve

message You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" &
[Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate
the fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve

 

[steve goodrich]

Douglas

Many thanks for all your help.
It's working perfectly
Regards
Steve

If you've save the specification that you create when you go through the
File menu, you can tell the TransferText action (or method) to use that
specification. You provide the name of the specification as the second
argument.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas

Corrected the typo and it worked - I'm nearly there!
In between our posts I've been trying different things to try and get
this to work, I created a macro using the transfer text action and added
a button to my form to run it. This works the same as the code you
supplied me with. Both methods place quotes at the beginning and end of
the string of text.
I also opened my query and selected save as from the file menu, save as
type-text. When you click the Export button you get options, one of
which allows you to remove the quotes.- which is the default setting.
(select none from the Text Qualifier box)
I would prefer just to click a button on my form as this is a job that
will need doing ever 4 hours by 16 different people, some of which would
not know how to export the query manually.

The client has been very specific in how they want the text file, and
they don't want quotes.
My final question is there a small amendment that I could add to the
macro or code that will produce a text file with no quotes"

Thanks again for all your help

Regards

Steve

Sorry, my typo. It's acExportDelim. (I told you you should look
TransferText up in the help file! <g>)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas
I pasted your text into the buttons on click event and changed the text
for my own query and location but when I click the button an error
message is displayed stating "Compile error: Variable not defined" I
click ok and it takes me back to the vb page with "asExportDelim"
highlighted
any idea what I'm doing wrong?
thanks
Steve
message Look up TransferText in the Help file.

The code in your button's Click event will be something like:

Private Sub MyButton_Click()

DoCmd.TransferText asExportDelim, , "NameOfQuery",
"C:\Folder\File.txt"

End Sub

If you want to prompt the user for the name of the file to use, see
http://www.mvps.org/access/api/api0001.htm at "The Access Web". (Yes,
I know that's overwhelming looking, but all you need to do is copy
everything between Code Start and Code End and paste it into a new
module. Once you've done that, put code like the sample at the top of
the page into your routine)

Private Sub MyButton_Click

Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(myStrFilter, "Text Files (*.txt)",
"*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If Len(strSaveFileName) > 0 Then
DoCmd.TransferText asExportDelim, , "NameOfQuery",
"C:\Folder\File.txt"
End If

End Sub

Unfortunately, I don't believe you'll be able to get field names at
the top of the file.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Douglas

I created the query as you suggested and it worked perfectly.
How do I export the query using the TransferText method?
And would it be possible to link it to a command button on my form?

Regards
Steve

message You could create a query that has a single field:

SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" &
[Surname]
FROM MyTable
ORDER BY [Id]

You could then export that query using the TransferText method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a query with the following fields
Id Staff Number First Name Surname

I need to save the query as a text file using the Tilde to separate
the fields, so it would look something like this.

1001~12345678~joe~smith
1002~87654321~fred~jones

etc

Using Access 97 & Windows XP

Any help would be appreciated

Steve