[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

save pass through query

Posted on 2006-05-26
14
Medium Priority
?
311 Views
Last Modified: 2011-10-03
i have created a pass through query and it works fine, but each time i close and reopen access and try running query, i have to go through the process of selecting the pass through query again.

is there a way to save this?

the pass through runs a MS SQL command...
0
Comment
Question by:ellandrd
  • 5
  • 5
  • 4
14 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 960 total points
ID: 16770627
what do u mean, select the proces of selecting pass thru query
once u define it as pass thru (Im assuming u went from query designer, menu option Query, SQL Specific, Pass Thru)
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16770647
yes i created it by > menu option Query, SQL Specific, Pass Thru, but how do i save it?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 960 total points
ID: 16770664
u just save it, and its supposed to stay that way

when u look at the list of queries when your database window is open

u get a symbol next to your query

normal query is like two windows, one on top of another
union is like two circles together

and pass thru normally has a globe/earth

what have u got?

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1040 total points
ID: 16771691
I think I had this in testing once where I'd been dicking about (another of my technical terms) with a passthrough no end.
I tend not to do much through the UI with them.
Code is our friend :-)

Just create a new one from scratch.
Give it the same SQL and the correct connect string.
If they're right and it runs - it should save and stay saved.

0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1040 total points
ID: 16771698
(P.S. By "i have to go through the process of selecting the pass through" I imagine you meant the ODBC source?)
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16774712
what have u got?

yes, but it dont stay...
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16774779
ok i must be doing something wrong

can somebody tell me how to create one probably?  im trying to run a stored procedure from MS SQL server 2000.

 
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1040 total points
ID: 16774887
Function fPassThrough(strQueryName as string, strSQL as string)
   
    Dim db As Database
    Dim qdf As QueryDef
   
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(strQueryName , strSQL)
   
    qdf.Connect = "ODBC;DSN=YourSQLDSN"
   
    Set qdf = Nothing
    Set db = Nothing
   
End Function
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 960 total points
ID: 16774949
u have a pass thru query, but you also want to run a stored procedure?
My understanding is a pass thru query is just sql that you run on server side but is defined in client
stored procedure is sql defined on server and run on server


if u just want to run a stored procedure, could u not go the simple way and use EXECUTE
e.g. (its something along these lines, cant remember full syntax)

   Set objConn = New ADODB.Connection
   objConn.ConnectionString = strconn
   sConnStr = "Provider=SQLOLEDB;SERVER=HERO11;DATABASE=myDB;Trusted_Connection=Yes;"

   objConn.Open
   Set cmd = CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = sConnStr
   cmd.CommandText = "NameOfStoredProcGoesHere"
   cmd.CommandType = adCmdStoredProc

'If you have any parameters - add them like this
   cmd("@SomeValue") = "wow"

'Now run the stored procedure
   cmd.Execute



Another way, similar to that from Leigh

Set ws = DBEngine.CreateWorkspace("A", "Admin", "", dbUseODBC)
Set con = ws.OpenConnection("TestConnection", dbRunAsync, False, "ODBC;DSN=DNET;UID=;PWD=;DATABASE=mydb")
Set qry = con.CreateQueryDef("", "EXECUTE mystoredproc")  
con.Execute "EXECUTE mystoredproc", dbRunAsync
Set con=Nothing
Set ws=Nothing

0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1040 total points
ID: 16775915
Indeed I prefer to execute SP's through an ADO command (or just right from the connection - for quickness).
But if all you want is an execution then the Passthrough can be plenty as it does indeed run a SQL statement on the server.

Function fPassThrough(strQueryName as string, strSPName as string)
   
    Dim db As Database
    Dim qdf As QueryDef
   
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(strQueryName)
   
    With qdf
        .ReturnsRecords = False
        .SQL = "EXECUTE " & strSPName
        .Connect = "ODBC;DSN=YourSQLDSN"
        .Execute
        .Close
    End With
   
    Set qdf = Nothing
    Set db = Nothing
   
End Function

Indeed - an improvement on this (and something often done) is to have a single saved Passthrough query.
And whenever you want to execute some code (or have it as a record returning scratch pad) you just change the SQL of it and execute it.
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16782954
ok i dont understand?

where does this code go? in a new marco?  how can i execute it?  i connect to the Ms access db by PHP using ODBC.

will this code run when i connect?
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1040 total points
ID: 16782975
Huh?  Umm Why didn't you mention that before?
All VBA is immediately useless.
But it was only to create passthroughs for you upon request - on the fly as it were.

But you should be able to create your passthrough in advance through the UI just fine.
Is it still not working?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 960 total points
ID: 16783121
PHP? Perhaps question might of been better suited in the PHP section, cos it was in MS Access, the assumption was u wanted in in MSAccess VBA.

Try this site, it gives an example
http://www.sitepoint.com/print/php-microsoft-sql-server
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16783172
Is it still not working?

yes i got it created and it seems to save now,
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question