how to save Passthrough Query result

[SKV]

I have a pass-through query that is running on a remote database (through ODBC connection).

If I try to save the results of the query, it gets saved to the remote database and not on my Machine.

How can I save these results on my local machine. Due to IT restrictions I am not allowed to save results on the server.

Any pointers???

 

[Joe4]

When you say "local machine", do you actually mean on your computer's physical hard-drive (usually the C drive) or on the network your PC uses?

We have a similar situation, in which the database is on a SQL server, and our computers are on a separate network. We got around this by mapping one of the drives from our non-SQL network to the machine that the SQL network is housed on. So the queries that are run from there can be saved to a netweork drive that we have access to from our local machines.

Your IT guys should be able to set that up for you.

 

[SKV]

Yes I mean local machine. I want to save the results on the local machine.
Is there a work around for this??

When you say "local machine", do you actually mean on your computer's physical hard-drive (usually the C drive) or on the network your PC uses?

We have a similar situation, in which the database is on a SQL server, and our computers are on a separate network. We got around this by mapping one of the drives from our non-SQL network to the machine that the SQL network is housed on. So the queries that are run from there can be saved to a netweork drive that we have access to from our local machines.

Your IT guys should be able to set that up for you.

 

[SydneyGeek]

If I need to grab data from another system I usually do this:

1. Pass-through to grab what I need
2. Make-table based on the pass-through to turn it onto a local data set. Then you can treat it as Access data; using Access functions and calcs for example, will be much quicker once the data is in an Access table. Otherwise each record is evaluated on ther server app before you pull it across.

Something else to consider; if you will be converting and trashing large recordsets, make sure you set the DB to Compact On Close (Tools > Options). You can also create the new table in another Access DB if you want to keep the size down. It's an option on the dialog when you nominate the table name.

Denis

 

[SKV]

Denis,

I tried your steps and when I try to make a table out of the pass through query, then it makes the table in the remote server only and NOT on my local machine. May be I am doing in wrong process. Can you please specify you Step 2 in more detail.

My intent is just to capture the result of a pass-through query (from a remote server) to a local machine in a table in access

Thanks
SKV

If I need to grab data from another system I usually do this:

1. Pass-through to grab what I need
2. Make-table based on the pass-through to turn it onto a local data set. Then you can treat it as Access data; using Access functions and calcs for example, will be much quicker once the data is in an Access table. Otherwise each record is evaluated on ther server app before you pull it across.

Something else to consider; if you will be converting and trashing large recordsets, make sure you set the DB to Compact On Close (Tools > Options). You can also create the new table in another Access DB if you want to keep the size down. It's an option on the dialog when you nominate the table name.

Denis

 

[SydneyGeek]

First step: create a pass-through query (qptMyQuery)
Second step: New query, using qptmyQuery as the data source. Select all fields, change it to a Make-table, select the destination and give the table a name.

That should create a local Access table for you.

Denis

 

[SKV]

Denis,

When I try to run the query after following the step 2, I am asked for a ODBC connection. What information should I put here? Without this information it is not letting me to run step 2.

Thanks
SKV

First step: create a pass-through query (qptMyQuery)
Second step: New query, using qptmyQuery as the data source. Select all fields, change it to a Make-table, select the destination and give the table a name.

That should create a local Access table for you.

Denis

 

[SydneyGeek]

Hi, I don't create an ODBC source as such -- I use a connection string with the pass-through. See this post...
http://www.mrexcel.com/forum/showpost.php?p=1445753&postcount=7

I have never been prompted for an ODBC source using this method.

Denis

 

[Gardoglee]

If you use ODBC then you need to add this to the Properties for the Pass-through query if you do not want to answer the dialog box each time.

Open the query in design view, right click on the blue bar across the top of the query editing box (to see which might require taking the box out of "Maximize"), click on properties, and then click the box with three dots (aka Build) to the right of the line in the properties box labelled "ODBC Connect Str".

This will take you to the "Select Data Source" dialog of the ODBC manager. Pick the User or System ODBC driver you set up for the target database. You will be shown a login dialog box for the database, into which you put your identifying characteristics like User ID and Password. You will then see a dialog box which ask whether you want to save the password in the connect string.

This will eventually put into the ODBC Connect Str property a string which will look something like "ODBC;DSN=DatabaseODBCIdentifier;UID=YourUserID;PWD=YourPassword;
DBQ=TheOracleDatabaseConnectionName;DBA=W;APA=T;EXC=F;FEN=T;
QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;
MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"

If you say to save the Password, then this will automaticaly work until you change your password. If you do not save the password, then you will have to enter it each time. And of course, if you distribute this to other users then they will look to the database as if they are you (not cool - don't do that). If you need to distribute this then manually edit out the User ID and password parameters before you distribute, or before you put this into a shared Access database.

And BTW, thank you Mr SydneyGeek. I would not have thought of that idea, and it solved my problem very nicely.

 

[SydneyGeek]

You're welcome. And thanks for posting the extra detail.

Denis