RE: TransferText from a table with a selection-parameter
- From: "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 26 Sep 2005 09:13:02 -0700
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.
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.
"Jonas Truemper" wrote:
> 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
>
>
> "'69 Camaro" wrote:
>
> > Hi, Jonas.
> >
> > > i guess i did not get you... can you go a lil more into detail?
> >
> > 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.
> >
> >
> > "Jonas Truemper" wrote:
> >
> > > Hey,
> > >
> > > i guess i did not get you... can you go a lil more into detail?
> > >
> > > thanks
> > >
> > > "'69 Camaro" wrote:
> > >
> > > > 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?
> > > >
> > > > 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.
> > > >
> > > >
> > > > "Jonas Truemper" wrote:
> > > >
> > > > > 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
> > > > > "'69 Camaro" wrote:
> > > > >
> > > > > > 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?
> > > > > >
> > > > > > 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.
> > > > > >
> > > > > >
> > > > > > "Jonas Truemper" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
.
- References:
- RE: TransferText from a table with a selection-parameter
- From: '69 Camaro
- RE: TransferText from a table with a selection-parameter
- From: Jonas Truemper
- RE: TransferText from a table with a selection-parameter
- Prev by Date: Re: blocking access to more than 1 user
- Next by Date: RE: ChDir Question
- Previous by thread: RE: TransferText from a table with a selection-parameter
- Next by thread: Re: using modules to append and delete data
- Index(es):
Relevant Pages
|
