TransferText from a table with a selection-parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

is there any way to only selectively export rows of a table using the
transfertext command and a parameter to dynamically select some rows of the
table?

The only way i currently know of is to create a form which which has a
stored procedure with a param as its data source. but since this only makes
everything harder to understand later i would like to clearly keep everything
in one place.

so thanks in advance for your anwer,

jonas
 
Hi, Jonas.
is there any way to only selectively export rows of a table using the
transfertext command and a parameter to dynamically select some rows of the
table?
Click to expand...

The TransferText( ) method doesn't have a parameter for "selective records."
However, there's no reason one cannot use a query for identifying these
selective records and then using the TransferText( ) method to export that
query.

In the following example, a temporary query named qryTemp must be created
first. (It can have any valid SQL statement. It will be overwritten by the
procedure, so don't worry what's in the original SQL statement of this
query.) A public procedure is created to call the TransferText( ) method:

Public Sub TransferTextWParam(sDataSrc As String, sFldName As String,
sCriteria As String)

On Error GoTo ErrHandler

Dim qry As QueryDef
Dim sqlStmt As String

sqlStmt = "SELECT * " & _
"FROM " & sDataSrc & _
" WHERE (" & sFldName & " = '" & sCriteria & "');"

Set qry = CurrentDb().QueryDefs("qryTemp")
qry.sql = sqlStmt
DoCmd.TransferText acExportDelim, "SpecName", qry.Name,
"C:\Work\XferText.txt", True

CleanUp:

Set qry = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in TransferTextWParam( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

.... where "SpecName" is the name of the export specification and
"C:\Work\XferText.txt" is the path and file name of the exported file. This
example uses a string parameter, so if your parameter is either numeric or a
date, change the SQL syntax and the parameter passed to the procedure
appropriately.

This procedure can be called from another procedure as in the following
example:

Public Sub testXferTextWParam()

On Error GoTo ErrHandler

Dim sTable As String
Dim sField As String
Dim sParam As String

sTable = "tblMyTable"
sField = "SomeField"
sParam = "SomeValue"

Call TransferTextWParam(sTable, sField, sParam)

Exit Sub

ErrHandler:

MsgBox "Error in testXferTextWParam( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Hi Gunny,

this looks like a good aproach to me.

Unfortunately i forgot to mention that i need the whole thing to be usable
with ADO in Access 2002.

I'm able to get a recordset typed ADODB.Recordset from the server.
So... how can i get TransferText to handle this one accordingly?


Thanks in advance,
Jonas
 
Hi, Jonas.
I'm able to get a recordset typed ADODB.Recordset from the server.
So... how can i get TransferText to handle this one accordingly?
Click to expand...

You can't. The TransferText( ) method requires a table, a linked table, or
a saved query. A Recordset Object is "none of the above." You'll need to
use the same data source as you used to create the Recordset Object, instead
of using this Recordset Object for the TransferText( ) method.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Hi, Jonas.
i guess i did not get you... can you go a lil more into detail?
Click to expand...

Sure. TransferText( ) requires a table, a linked table, or a saved query in
order to take the records contained in -- or identified by (as in the case of
a query) -- that object and save these records in a text file. TransferText(
) doesn't understand what a RecordSet Object is. However, when you created
that RecordSet Object, it was based upon some table. Use that table as the
data source for the query that TransferText( ) can later use.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Hi Gunny,


hum... okay, now I'm sure I got what you wanted to say.

My problem is now to put this information into some form of usable code. :)

Oh, and something I still want to mention, since I'm not sure if I already
did and whether you know:
I'm using an SQL-Server as backend (ADO). So I can't use a 'serverside'
temporary table where I store the recordset inside because there may be
several users accessing the data and then writing into the same temporary
table.

So i need a solution that works with some kind of clientside temporary table
or something like that.

Jonas
 
Hi, Jonas.
I'm using an SQL-Server as backend (ADO). So I can't use a 'serverside'
temporary table where I store the recordset inside because there may be
several users accessing the data and then writing into the same temporary
table.
Click to expand...

Each of your users can use separate temp tables in your SQL Server database.
Contact your DBA for instructions and any necessary permissions that he
needs to grant your users.

Perhaps you don't have a DBA or you can't get organizational permission to
create temp tables in SQL Server, and you'd like to store a copy of these
records temporarily in the Access database? (SQL Server would be far more
efficient, so that's what I'd recommend.) If so, a MakeTable query or Append
query into an existing table will protect the records from being changed
while the other users are fiddling with the originals stored in SQL Server.
Use this temp table as the data source for the TransferText( ) method.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top