Pass through query to SQL Stored Proc, pass parameter

Hi all,

First post here and i have a question.

I currently have a pass through query the run a stored proc from sql.
The Stored proc take 1 parameter which is the timeid. In our access DB file we have a table called DateStamp that holds the time is for the current period.

How do i call the stored proc from sql while passing it the value from the date stamp table (which is only 1 row and hold the time id value)?

my current code is :

	Code:
	Exec  p_TMIreport @iTimeId=201208



Post your answer or comment

comments powered by Disqus
I am having difficulty finding a solution to my problem. I am using MS Access 2003 and I'm trying to create a pass-through query to a SQL Server. I wish to run the same SQL on multiple databases on the server using a single query. I know I could use a giant union query for this but i prefer not to use that method because I do not want to have to update this query every time a new database is added to the server. After searching for information I saw that some people had listed two possible solutions to this issue. Either use a Cursor and loop through the databases in the Master.sys.databases table, or use the built-in yet not officially supported stored procedure sp_MSforeachdb. My attempts at using both of these methods give me the same error. Whatever procedure I use only seems to return the first database in the list and then stops the loop. My SQL for the Cursor method is below:


	Code:
	SET NoCount ON

DECLARE @DBName VarChar(200)
DECLARE @SQL nVarChar(500)

DECLARE DBCursor CURSOR FOR
SELECT Name FROM master.sys.databases WHERE Name Like '%analysis'

OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DBName

WHILE @@FETCH_STATUS=0
BEGIN

SELECT @SQL='SELECT ' + '''' + @DBName + '''' + ' AS DBName, Name AS Company, ID AS CompanyID FROM ' + @DBName +
'.dbo.Company WHERE ID>0'
EXEC sp_ExecuteSQL @SQL

FETCH NEXT FROM DBCursor INTO @DBName

END

CLOSE DBCursor
DEALLOCATE DBCursor

The SQL I am trying for the built-in stored procedure is below:


	Code:
	EXEC sp_MSforeachdb 'IF ''?'' Like ''%analysis'' SELECT ''?'' AS DBName, Name AS Company, ID AS CompanyID FROM ?..Company
WHERE ID>0'

Both of these SQL statements return the same results but they both stop after only reading from 1 database. The SQL Server database I am connecting to is version 2005. If anyone could help on this I would greatly appreciate it. Thanks!

Im trying to create a pass through query to sql server using vba in access 2000. The only trouble is every example ive come across including the one with access 2000 uses old dao syntax. Anybody got an example of a pass through query with a parameter that they could post here.

Much appreciated

Hi there,

I've, today, created my first pass-through query (wahooooo!) which links my Access 2007 db to a query in SQL Server.

Is there a way that I can refresh the SQL Server query from Access?

For example, when I "refreshall" from Access, the pass-through always remains at the same number of records. Because I have no visible access to SQL Server, I was advised that the query in SQL Server needs to be refreshed each time and that I can do this via Access, but not sure how to.

My apologies if I am using incorrect terminology here, but I hope you get a gist of what I am asking.

Many thanks.

Hello,

This takes alot of different things into account but i'm thinking its the query itself that is the main problem. Apologese if i am mistaken and put this in the wrong place.

I have a Pass through Query which is actioned when a button gets pushed on a form.

Basically the functionality is that of websites when you are ordering things. So when you put in your post code it finds the addresses for you to select one and it puts it into the main form.

The problem i'm having is multipul use. When say Person 1 put the post code in They're looking for it stalls for 5 seconds (to access the data i'm assuming) Then brings up the data they've selected. However. with more than one person using the database at any given time if Person 2 then comes along and searches for the post code they want within close proximity to person1's search. it will return person1's results in person2's search.

It could be any number of factors. The VBA code used to access the query could be wrong. The method used to do this could be flawed for all i know. or the ODBC connection might be set up wrong. So again i apologise if this isn't the right part of the forum to post.

i know its a bit vague on detail so if there is any information you need to assist i can provide.

Cheers

Hi all,

I have an MS Access 2007 front end displaying data provided by parameterised pass through queries to SQL. Although the data comes across without any problem it's not presented as I'd like it in the form. I'm referring particularly to a text box which contains a brief description of the results of an examination. The 'Report' field in the SQL table is a Varchar (Max) due to the number of characters and when the query is executed manually the entire amount of text is displayed, but when executed as through VBA the textbox limits the display to 255 characters.

Any thoughts...??

Any help is appreciated I'm banging my head against the wall here.

Andy

How do I export a pass-through query to Excel. I know how to export an Access query using docmd.transferspreadsheet.

I'm passing select queries to Pervasive SQL. I'd like to be able to export the query results directly to Excel.

I have a pass through query but the stored procedure on SQL server requires a parameter.
How do I pass the parameter in the following code?

At the moment the pass through query just says:
EXEC Sproc @Name

Dim i As Integer
Dim iNumCols As Integer
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("Query1")

Application.SetOption "Show Status Bar", True
StatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.workBook
Dim oSheet As Excel.workSheet

'uncomment these 2 lines for debugging
'oApp.Visible = True
'oApp.UserControl = True

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Add the field names in row 1
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next

'Add the data starting at cell A1
oSheet.Range("A2").CopyFromRecordset rs

I am having problems editing pass through queries. I have several stored procedures set up on an SQL server and pass through queries set up in access. Each stored procedure uses 3 input parameters (2 of which are datetime data types). The queries run when I manually enter the parameters in the sql view of the queries but I would like the users to enter the parameters in text boxes in a form and these variables to be passed to the pass through queries/reports which will in turn open them.

I have tried forcing the pass through query to look up the textbox values on a loaded form but they do not recognise the datetime variables.

I have also used recordsets to open the stored procedures in VBA. This works fine but I cannot think how to show the results in the queries or reports.

I have also tried the OpenQuery command but can not think of a way of passing the parameters to the pass through query.

Any ideas would be gratefully appreciated.

Thanks in advance,

Anna

Dear MS Access Expert,

Suppose I have a MDB that has ODBC links to SQL Server. All major tables are on SQL Server.

Suppose my backend is between 0.5 - 1 GB and the number of concurrent users is 50. Can I get good performance (assume good client / server design) if all queries are built using the Access query grid and stored in the MDB?

How good is Access at translating my Access Queries for SQL Server? Can I make all types of queries in Access (Select, Update, Totals, Union) for SQL server backend? (I know I can't use TRANSFORM.)

How much more effecient is executing Stored Procedures using ADO and bounding the resulting recordset to a form vs. bounding my forms to Access Queries.

What about bounding reports to pass-through queries? Is there a difference in performance between a pass-through query vs. a stored procedure in a read - only situation?


There seem to be alot of options when building Client / Server Access FE applications and I am trying to figure things out. Note: I have also looked at ADP and many people are suggesting to opt for MDB (ACCDB) using ODBC links to SQL Server.

Thanks

Peter.

I'm trying to write a pass through query (first time) using access 2003.

I need to store the ODBC password within my query so when it run my autoexec macro it does not prompt for a password.

I'm having two issues:

#1 - my pass through query is referring referring to a table per below but it is giving me an ODBC error.
#2 - I don't know the language of how to store the password. (In some cases, I have two passwords for two different systems that I am grabbing data from)

HELP!
Thanks!

Table Name - LENOX_VIEW_BRANDS_SHIP_REC
I want all fields from this table to be dumped into this table - Shippingdetail

This is my sql: (that is not working)

SELECT LENOX_VIEW_BRANDS_SHIP_REC.* INTO Shippingdetail
FROM LENOX_VIEW_BRANDS_SHIP_REC;

I have a pass-through query that is passing a pair of variable parameters to execute an oracale procedure.

Here it is:

Exec P_AssetTagUpdate ('ABC', 'DEF')

note, here we are trying to change a pc's tag number from ABC to DEF.

User gets the message:

ODBC call failed
[Oracle][ODBC] Syntax error or access violation (#0)

Another note: the user can execute the exact statement in oracle SQL +. Anyone know what the problem is?

Thanks.

Hi
i have an Access Front End, using linked table and pass through queries to a SQL Server BE.

one of the tables on the SQL Server, holds records of 85,000 organisations.

In access i have a form with a list box for the list of organisations, but because of the large number of organisation records, i query the organisation name by text in a text box, before populating the list box.

at the moment i have the 85,000 records on a linked table, and then i query the linked table in VBA using
WHERE (tblOrg.Org.text) Like ""*"" & [Forms]![Org]![search] & ""*""))

my question is, would a pass through query be faster than querying the 85,00 0 records in the linked table, and if it is possible how would i do it?

my pass through query sql is
SELECT org_id, org
FROM dbo.tblOrg
GROUP BY org_id, org

can i adhere the text search parameter to it.

apologies if this is loose questioning

regards
Roosn

I know there are probably 20 different ways of doing this but I'm new at pass through queries so I want to learn the proper syntax. I have a stored procedure that counts the amount of e-mail addresses on file for a particular employee. Basically a fast version of DCount("ID", "EMAILS", "EMPLOYEEID = " & ID).

Code: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[EmployeeEmailCount] @EMPID as bigInt, @EMPCount as Int Output AS BEGIN SET NOCOUNT ON; SELECT @EMPCount = Count(*) From EmployeeContactEmails where EmployeeID = @EMPID END I created as simple pass through query:
Code: exec "employeeemailcount" @EMPID = 6 This should return 2 but I get an error:

Code: Procedure of Function 'EmployeeEmailCount' expects parameter '@EMPCount', which was not supplied" If I just add @EMPCount to the end of the query it tells me I have to declare the scalar value @EMPCount. How should the syntax read for the output variable?

Thanks in advance

I don't know if this is thebest place for this, but here goes....

I have an Access FE using MYSQL 4.1 BE. I have several forms and PassThrough queries (using MYSQL syntax not supported or available in Access).

How can I use form field values to use as criteria for these queries?

FOr example, in a pure Access database. I may have a form 'Employees' with a text box 'EmployeeName'. THen have a query....

SELECT * FROM tblEMployees WHERE EmployeeName = [Forms]![Employees]![EmployeeName]

If I were to add this variable to my pass-through queries, it errors. How can I do the same thing, passing my form fileds to an Access Query? I am trying to do this right in the SQL view of the queries, not in VBA or Macros or anything.

Thanks,
Scott

I created a stored procedure called BuildAttendanceEvents_GroupByEmp
the parameters of this procedure is @LoadFrom, @LoadTo

To run this stored procedure from the MS Access, I created a pass-through query, at the Database Window. This query is connected to the same database where the stored procedure is located at. In this pass-through query, I wrote the following code to run it:

DECLARE @LoadFrom datetime, @loadTo datetime;
EXEC BuildAttendanceEvents_GroupByEmp @LoadFrom, @LoadTo;



I was expecting that this pass-through query will ask me to enter it parameters values, but it didn't ask.

Thus, how can I convert this pass-through query to a parameterized query specially if I would like to automate it??

Thanks,

I am using a Pass Through query to execute a Store Procedure on SQL Server 2000. The Store procedure checks whether the user is a member of the Domain administrators group. Unfortunatel, it returns a null value. If I set the store Procedure to check on any other domain role it works fine, so i know the code is correct.

I realise this is more of a SQL Server problem, but there may be help available from one of you guys out there.

In addition, can anyone advice of a good forum for SQL Server?

Dave

Hey All,

I need a little help with one of my update queries.
Pretty much all I want to do is update one table (in Access 2007) based on the results from my pass-through query that pulls data from SQL Server.

Please look at my update query below:

UPDATE
[SQL (2) Vendor cost export]
INNER JOIN
[Product Cost]
ON
([SQL (2) Vendor cost export].[Item id] = [Product Cost].Product_Id)
AND
([SQL (2) Vendor cost export].[Vendor id] = [Product Cost].Pharmacy)
SET [Product Cost].[Phamracy Product ID] = [SQL (2) Vendor cost export]![Vndr item id], [Product Cost].Cost = [SQL (2) Vendor cost export]![Std cost];

[SQL (2) Vendor cost export] is the pass through query that pulls data from SQL server and Product Cost is the table I am trying to update based on the results from pass-through query.

Access gives me an error message: "Operation must use an updatable query" when I try to run this.

Can you see any errors? Any help would be greatly appreciated.

Thanks


Hi all,

I want to fetch more than 2000 records from a sql pass through query to excel file. i use the following code

DoCmd.OutputTo acOutputQuery, "Q_3_Monthly_LC", acFormatXLS,txt,False
this code fetches less than 17000 rows,so i tried to use this code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_3_Monthly_LC”,txt,False
It is also not working and shows an error message as
error:3251 operation is not supported for this type of object.Please, close b_all.xls file (Excel spreadsheet).

plese help me on this.

Hi
I was wondering if someone can help me with a passthrough query.

1) I have setup an ODBC connection on my system from my Sybase Server
2) I have tested this connection by linking some tables via OSBC from the Link Table manager
3) I have set up the connection correctly in the properties section of the query window to produce a Pass Through Query


	Code:
	ODBC;DSN=xxxxx Sybase Server;UID=xxx;PWD=xxx;LANGUAGE=us_english;DATABASE=xxx


	Code:
	SELECT dbo_action_log.* FROM dbo_action_log

However I get the following error message when I try to run the Pass Through query


	Code:
	ODBC -- call failed
[Microsoft]ODBC SQL Server Driver][SQL Server]dbo_action_log not found. Specify owner.objectname or use sp_help ...... etc.

I notice that I need to do a Pass Through query to Sybase but is Pass Through queries exclusive to SQL Server only?

Thanks

Hi!

We have an old application (as an Access Front-end to a sql-server) that I need to make changes to.

I have set up a pass-through query to the SQL-server and it works flawlessly, and I have also tied it up to the sub-form, which again is attached to the main form.

When I execute the sub-form independently it shows the data just as I would expect, but whenever I try to open the main form containing the sub-form, I get an error saying:

'You can't use a pass-through query or a non-fixed-column cross tab query as a record source for a subform or subreport.'

I've been looking everywhere for a solution, and everyone suggests I have to set up the columns properly - but I'm unable to find out where I can set these. I've read numerous posts regarding this, but I'm still not able to locate the "ColumnHeadings" referred to.

I've also found references to "child link" etc that needs to be disconnected for this to work, but I don't know how to do this, and I'm not even sure that's the solution.

I would have used a regular query if it wasn't for a specific masking function that we have implemented on the Sql Server, hence the pass-through query.

Can anyone give me a few pointers?

Thanks in advance.

Best regards,

Beck

Is it possible to create a report based on a pass through query? I recently tried to do that, but everytime I try to do anything to that report (run it or modify it) the database locks up on me. Is there a way to do this?

I have just created a database to house all the information on the sales reps in my company. Then I created a pass through query to connect the sales rep information with the order information - so that I could see order volume by sales rep. I would like to be able to enter the sales rep number and a time period on a form and then be able to click a button to generate a report (via the pass through query) to show that sales rep's volume. Do you have any suggestions as to how to do that? What I tried to do (that isn't working) was create a report directly tied to the pass through query. Any assistance you can provide would be greatly appreciated.

I have created a pass through query to show the order volume for a specific group of sales reps. I would like to get a report set up based on that query that will summarize the data (i.e. tell the number of orders entered and the dollar amount for those orders instead of listing all order individually). I tried creating a report, but everytime I try to run it, my database locks up on me. Is there a way to generate a report based on a pass through query? Any assistance you can provide, would be greatly appreciated.

Hi I am trying to setup a pass through query to sql to create a temporary table, insert records and pull back the results after they have been passed through a UDF.

create table #UAI
(User_Abstract_ID varchar(30))

Insert #UAI values ('XURBB_424384')
Insert #UAI values ('XURBB_424385')
Insert #UAI values ('XURBB_424386')

SELECT dbo.GET_CITATION (user_abstract_id) from #UAI

drop table #UAI

This works fine in SQL server query window but Access doesnt think the table exists/wont run this/wont pull back results.

Any ideas on this, many thanks in advance for your help

Oliver

So here's my setup. We have a database with several tables that have over 255 fields. So we must use pass-through queries to access some of the data.

Usually no problem.

Here is my pass-through query:

qry001: SELECT db001.table001.field001 from db001.table001

If I execute this, it instantly spits me to datasheet view with all the millions of records ready for viewing.

If I modify it to:

SELECT db001.table001.field001 from db001.table001 WHERE db001.table001.field001 = '123'

It works fine and displays the one record instantly.

Now on to the problem

When I make a new query, using this pass-through query as a data source such as:

SELECT qry001.field001 FROM tblLocal001 LEFT JOIN qry001 ON tblLocal.field001 = qry001.field001;

This query takes forever to run.

And the thing is, tblLocal001 only has one record in it. In production, this table will have thousands so I need to understand why this query is taking so long and fix it if possible.

Thanks for your help


Not finding an answer? Try a Google search.