Prev: What do people actually use Access for?
Next: One User-Two Locks-Same Database
From: Matthew Wells on 5 Apr 2010 02:36
Hello.

I am using docmd.transfertext to export a pass through (to SQL Server) query
to a txt file. When I execute

DoCmd.TransferText acExportDelim, "MyExportSpec", "qryPassThru", sFile,
True

I keep getting the error

Operation is not supported for this type of object.

I've tested it using tables and standard Access queries and it works, but
not with pass through. Does anyone know how to do this without looping
through the recordset?

Thanks.


--
Matthew Wells
matthew.wells(a)firstbyte.net

From: Rich P on 5 Apr 2010 11:35
I have dealt with this very situation many years ago (10+ years ago) and
have spent a great deal of time experimenting with ways to bypass
looping. My findings/results are as follows: This type of
functionality can be achieved by using the OpenRowset() function of sql
server. This will bring in a dataset from the sql server to the Access
mdb in one shot -- that is -- populate a receiving table in the Access
mdb in one shot without looping through the query. Here are the
caveats: The OpenRowset() function only works on the machine where the
sql server (engine) is installed. It does not work remotely. The
other caveat is that OpenRowSet() is manipulated through ADO and not a
passthrough query.

Note: ADO.Net is all about this very issue of transferring data from a
sql server to another application (a .Net application, Access,
Excel,...) in one shot and has very successfully achieved this
functionality. As a matter of fact, .Net has a whole new paradigm
called "Linq To Sql Entities" which takes this issue to a whole new
level of simplicity (for the computer - not the human :). But this is
in the .Net world.

In Access you are resigned to looping if the sql server engine is not
installed on your local machine (server) and you can't use OpenRowSet().
You will read the contents of your passthrough query into a Recordset
object and then populate a receiving table BY looping through the
recordset object. Then you can perform the DoCmd.TransferText operation
from the receiving table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: Albert D. Kallal on 5 Apr 2010 16:00

Just copy the data to a temp table local, and then export that.

The data has to flow to the local client so there not a additional cost in
terms network bandwidth or performance penalty.

so, the code looks like:

On Error Resume Next
CurrentDb.Execute "drop table t1"
On Error GoTo 0
CurrentDb.Execute "select *.* into t1 from pq"
DoCmd.TransferText acExportDelim, , "t1", "c:\test.txt", True


So, you don't have to loop at all, you just execute the transfer text on
local table. If this is done a lot, then of course I would consider using a
temp mdb file to eliminate the issue of bloat.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com


From: Albert D. Kallal on 5 Apr 2010 16:25

"Rich P" <rpng123(a)aol.com> wrote in message
news:4bba0329$0$89399$815e3792(a)news.qwest.net...

>
> In Access you are resigned to looping if the sql server engine is not
> installed on your local machine

Are you talking about access or something else here?

You can use a local in-memory ADO recordset, and then save that as xml
if you want, or even as text (but, it will not result in a csv file).

However, in this case, one wants to use transfertext then just read on:

> You will read the contents of your passthrough query into a Recordset
> object and then populate a receiving table BY looping through the
> recordset object.

Why write any looping at all? Why not just execute a make table query and
pull the data local, and then transfer it out using transfer text?

CurrentDb.Execute "select *.* into t1 from pq"
DoCmd.TransferText acExportDelim, , "t1", "c:\test.txt", True

I count a big huge two lines of code here? Do you see any looping? And, I
suppose we could use a append query to an existing table to allow the use of
a export spec.

The above two lines of code works just fine in access. All this talk about
looping and .net ADO recordsets is just detracting from this problem and it
not really much relevant at all here.

At the end of the day, no looping is needed.

A simple two lines of code is rather sufficient to solve this problem.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com


 | 
Pages: 1
Prev: What do people actually use Access for?
Next: One User-Two Locks-Same Database