Converting Access Query To Pass-Through Query

Dec 23, 2005

Can anyone help? I have a query that queries against 11 linked views against an SQL Server backend.

The query is running dog slow, so I want to convert it to a pass-through query so that the processing is done server side rather than Access having to drag thousands of records across the network, but don't know where to start.

The problem is that I need to convert the Access SQL to a version of SQL that SQL Server understands.

Is there a tool I can use that does this automatically (keeping my fingers crossed here)? If not then does anyone know where I can find out how to do this?

View Replies


ADVERTISEMENT

Modifying Access Pass Through Query Using Querydefs

Feb 8, 2012

I have a pass through query which I want to add a parameter to the end.

The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.

I do not want to retype or try and paste all of this code into a vba module.

Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?

A simple way of looking at this could be

querytemplate:-

"set nocount off

Select UWREF
from policy
where UWREF like '"

parameter:- (picked up from a text box on a form)
"ABC123"

additional text to follow:-
"%' set nocount on"

In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.

View 1 Replies View Related

Update Access Table From A Pass-through Query?

Jun 28, 2012

I want update a table (in Access 2010) based on the results from a pass-through query that pulls data from an old Fox Pro database.

With the following query I push data into my access table (everything works fine):

Code:

INSERT INTO Bolle ( esercizio, numero_documento, ...)
SELECT Bolle_Arca.[esercizio], Bolle_Arca.[numero_documento], Bolle_Arca.[testata_codice], ...
FROM Bolle_Arca
WHERE
Bolle_Arca.[testata_codice] NOT IN (SELECT Bolle.testata_codice FROM Bolle)

When I try to update the same table using the same pass-through query:

Code:
UPDATE Bolle INNER JOIN Bolle_Arca ON Bolle.riga_codice = Bolle_Arca.riga_codice
SET Bolle.esercizio = Bolle_Arca.esercizio
, Bolle.numero_documento = Bolle_Arca.numero_documento, ...
WHERE Bolle.spedito = False
and Bolle.data_invio_mail_spedito is null
and (Bolle.tracking is null or Bolle.tracking = '')
and Bolle.testata_codice = Bolle_Arca.testata_codice;

I get this error: "Operation must use an updatable query"

View 3 Replies View Related

I Am Trying To Run A Pass-through Query In Access With A Variable That A User Enters

Nov 29, 2007

I am trying to run a pass-through query in access with a variable that a user enters in a form. I can't seem to get this to work. Can somebody please help? The variable should be where the 121212 is in the code.

Code


1:FROM doc..eco_summary es
2:INNER JOIN
3:((((mart..DM_Map dmm LEFT JOIN mart..DM_PI dpi ON dmm.Acct_ID = dpi.Acct_ID)
4:INNER JOIN mart..DM_Note dmn ON dmm.Acct_ID = dmn.Acct_ID)
5:INNER JOIN mart..DM_ACCT dma ON dmn.Acct_ID = dma.Acct_ID)
6:LEFT JOIN mart..DM_RE dmr ON dmn.Acct_ID = dmr.Acct_ID) ON es.L_loannum = dmm.Acct_ID
7:INNER JOIN weis..eco_loan_origination elo ON es.L_num = elo.num
8:where es.L_num = 121212

View 1 Replies View Related

Converting Access To Access Project Query Problem

Sep 18, 2007

Hello All,

I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.

However I have one issue that I am having issues with.

I have a query that determines a few collumns by running information through a vb function and returning the result.

The issue is I do not know how to convert this code from Access to SQL so that it will work.

The code is

SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Field, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later
FROM TESTSTAT
WHERE [LOG_NO] LIKE '*'
ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);

Can someone provide me with a sutible sql equvalent to this code

Thank You
Mike

View 7 Replies View Related

Converting Access To Access Project Query Problem

Sep 18, 2007

Hello All,

I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.

However I have one issue that I am having issues with.

I have a query that determines a few collumns by running information through a vb function and returning the result.

The issue is I do not know how to convert this code from Access to SQL so that it will work.

The code is

SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Field, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later
FROM TESTSTAT
WHERE [LOG_NO] LIKE '*'
ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);

Can someone provide me with a sutible sql equvalent to this code

Thank You
Mike

View 2 Replies View Related

Pass-Through Query Help

Jul 13, 2007

I'm trying to create a PTQ and just cant seem to find the source table.

The name of the source table is PRM1_ORG_MTRX3_N

If I write my query like this, I get an error code that states username.PRM1_ORG_MTRX3_N is an undefined name. It adds my userid to the beginning of the table name.

SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME

FROM
PRM1_ORG_MTRX3_N as T128

WHERE
T128.AS_OF_DATE like '1/1/2007'

Then I rewrote the query like this, and I got and error code that said PRM1.ORG_MTRX3_N is an undefined name. Does anyone know what else I can try here to get this query going? Thanks


SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME

FROM
PRM1.ORG_MTRX3_N as T128

WHERE
T128.AS_OF_DATE like '1/1/2007'

View 2 Replies View Related

Pass Through Query

Jun 30, 2006

Can you do a pass through Query to a pivot table when your query has parameters? I'm reading like you can't, but nothing has been said concretely yet. When I do it, it gives me an error "Trouble Obtaining Data" when I try and set the layout.

View 1 Replies View Related

How To Pass Parameter To Query?

Dec 6, 2007

Hi

I have created a simple query that is used as the record source of one of my forms. I want to pass the query a different criterion each time the form is opened.

Any suggestion/example on who to do that will be very much appreciated.

Thanks,
B

View 4 Replies View Related

Pass-Through Query Question

Jun 6, 2006

I have an Access front-end connected to Oracle tables. When the user opens up the main form to add a new entry, I need to be able to pull the next sequential record number. However, I can't get this to happen, without filling out the entire record, saving, and then it will committ the number.

Can this be done with a pass-through query to the Oracle table, find the next sequential number, and then add it to my record id (ie, 06-1050, with 06 being the year and 1050 the next sequential number). I do have two fields in the Oracle table TXTYear and LNGNumber. This concatenated field is related to three additional tables that have more information. Each of these tables need to cascade a new record.

Confused? me too. any help would be appreciated.

View 1 Replies View Related

Pass-through Query Causing An Error

Jan 4, 2006

I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".

The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!

Does anyone have any idea what is causing this?:confused:

View 2 Replies View Related

Where Clause In A Pass Thorugh Query

Jul 19, 2006

Hello

Any hellp would be appericated.

I have a pass thorugh query that connets to a Oracle database with a where clause with a between start date and end date.

I need the start date and End Date to come from a form within MS Access.

Is this possible? if so how do I code the where clause to get the start and end date parameters from the form?

View 3 Replies View Related

Two ODBC Connections Within One Pass-through Query???

Oct 23, 2007

I have a query that I want use as a pass-through query (SQL Server databases).

The query has a sub-query that references another database. The query runs fine in Enterprise Manager because EM allows me to access both databases.

If I wanted to I could link both tables through ODBC and then run an Access query that would do the same, however, I would rather use a pass-through-query for this.

The problem is that a pass-through-query in MS Access only allows me ONE ODBC connection (as far as i can tell).

My question is:
Is there a way I can run a sub-query (referencing a different database. i.e. second ODBC connection) which references a second database?

Is this possible?


Thanks
-Gary

View 1 Replies View Related

Pass Variable In Query Criteria

Mar 3, 2008

Is there any way to pass a variable from one criteria on a query to five other criterias in the same query.

I am using this code to select which person the query is running on

Like "*" & [Enter Part of The Landman Name or Enter For All Records] & "*"

I have five areas in this query where I would need to add the name, I just want to do it once?

Thanks.

Fen how

View 14 Replies View Related

How Do I Pass A Parameter From A Form To A Query?

Sep 11, 2005

Hi

I am struggling with what seems like should be a straightforward task. Unexpectedly however it has become an infuriatingly difficult one (no doubt due to my complete novice status).

I have a very basic d/base (3 tables) that I have been searching using basic SQL queries. I want to create a simple 'search' form that produces the results of my various queries without the need to work in SQL. Enter one or more search criteria, hit ENTER, results presented in datasheet perhaps?

Thought this would have been easy but I've had no luck. Can you please explain how I can pass a parameter from a form to a query?

Many thanks

Stuck21

View 1 Replies View Related

Pass Rpt Start And End Dates To Query

Nov 25, 2005

I'm a couple of years removed from Access and shaking off the rust. I hope someone can help with something that may be obvious but I'm missing.

I'm simply looking to pass the begin and end dates to a query driving reports. The calendar form includes unbound text boxes, txtBeginDate and txtEndDate. My code populates the text boxes correctly; the user clicks on the calendar date, clicks the calendar day, then the Begin (or End) Date control, and each populates the respective textbox.

But when I run the query or report, I'm prompted for the parameters. This is what I have in the query's Date field criteria:

Between [Forms]![frm_Calendar]![txtBeginDate] And [Forms]![frm_Calendar]![txtEndDate]

I'm just drawing blank on what I did several years ago to make this work. If anyone can help, I'm most appreciative.

View 6 Replies View Related

SQL Pass Through Query/ms Acces To Oracle

Jun 3, 2005

I'm getting an error (SQL command not properly ended) but main thing is, I can't get it to recognize the ms access db.
In a nutshell...I want to query the oracle db for a number and count, then scan a number into msaccess and have it 'find' the matching number in oracle and return the count only to ms access. More comments below between the code and here are table names for both oracle and access:
ORACLE: Table name is: batch_status bs
Field names are: batch_num and Batch size

MS ACCESS: Table name is tblbatchstatus
Field names are: batchnum and idcount

CODE in PASSTHROUGH QUERY:
select batch_num,BATCH_SIZE
from batch_status bs
(this works and selects all batch numbers and sizes from oracle)
(If I say 'where batch_num = 60024 -----this works also....)
However...when I add the below part...I get errors...it can't seem to match to access database....


SELECT tblbatchstatus.Batchnum, tblbatchstatus.Idcount
FROM tblbatchstatus;

where batch_num = tblbatchstatus.batchnum

And I want to just say: where batch_num = <<<scanned batch number>>> and return the id count.

View 1 Replies View Related

Pass Variables To Query Part 2

Aug 15, 2007

i have this query that i have several buttons that trigger it. how do i pass over a unique variable with each button. each button needs to append a different event. right now i have it set static to "3rd Party Denial" but i need that to be dynamic based on which button is pressed. PLEASE HELP!!!!!!
Code:INSERT INTO [Status Log] ( Status, Edit_Date, Event, Claim_ID )SELECT [Status Lookup].Status, Now() AS Expr1, "3rd Party Denial" AS Expr3, [Claim Report Info].[Claims Header].Claim_IDFROM [Status Lookup] RIGHT JOIN ([Claim Report Info] LEFT JOIN [Status Log] ON [Claim Report Info].[Claims Header].Claim_ID = [Status Log].Claim_ID) ON [Status Lookup].status = [Status Log].StatusGROUP BY [Status Lookup].Status, Now(), "3rd Party Denial", [Claim Report Info].[Claims Header].Claim_IDHAVING ((([Status Lookup].Status)=[Forms]![claiminformation]![ReportForm]![reportstatus1]) AND (([Claim Report Info].[Claims Header].Claim_ID)=[Forms]![claiminformation]![ReportForm]![Report_ClaimID]));
currently i have a macro running when clicked.
that macro runs 2 quieres and a report.
i'm doing this because thats the only way i know how.

View 7 Replies View Related

Forms :: Pass Listbox Value To Query?

Jan 14, 2015

I just got stuck with the following. I have a listbox where I have some values. I want to use the value of the first listbox to pass this to a query. I have the below code:

Code:
Private Sub List28_GotFocus()
' Limit the subject emails in the list box
Me.List28.RowSource = "SELECT [Subject] " & "FROM tbl_eMail_Archive " & " WHERE [FolderName] " & "Me.List23.Value"
' Refresh the list box
Me.List28.Requery
End Sub

why it is not working?

View 2 Replies View Related

Modules & VBA :: Pass Through Query With Parameters

Jan 22, 2015

This is my first time writing a pass through query pinging sql tables using an input parameter from a form. I have gotten as far as executing the query but I can't seem to display the result to ensure it's pulling the right records. I also want to be able to append the records to a table. Below is the code I have written so far:

Sub GETRT()

Code:
Dim db As DAO.Database
Dim QDF As QueryDef
Dim STRSQL As String
Dim RS As Recordset
STRSQL = "SELECT * FROM LAB_MESR.ODM_RT_DAYS" & _
"WHERE LOCATION_ID=" & [Forms]![PARMS]![STR_NBR]
Set db = CurrentDb
Set QDF = db.QueryDefs("001:GET_LT")
QDF.SQL = STRSQL
End Sub

View 5 Replies View Related

Modules & VBA :: Pass Filter To Query

Feb 17, 2014

I have a subform (this subform's source object can change to one of three diffrent forms) that allows the users to filter. I have a requirement to output the filtered data to excel. I am creating a query based on the subforms record source called qryExport. I now want to pass the subforms filter to qryExport. I have the string setup now I just need to know how to pass the data into the filter property of the query.

View 3 Replies View Related

Update Query/Converting

May 9, 2005

Dear all,


i have searched /researched and looked at hundreds of posts, and no luck.

Im litlle bit out of my depth, i have posted this before but just got confused.

i have two update querys, the first one identifys if a persons age (by date of birth) is under 16 a check box identifys if this person is "ticked" as junior.

the second update query identifys by (date of birth) if the person is over 16 a check box identifys if this person is "ticked" as senior.

The results are returned into my main query in two columns, these columns return -1 or 0 for junior or senior.

I need to produce a report that returns J or S (Junior or Senior) is there a way of converting these results to one column and converting the -1, 0 to J or S.

Please see attached file.

very lost, still searching for the answer. been on this foe three weeks now.

any help appricated.

View 4 Replies View Related

Pass Dates From Form To Report To Query

Jan 29, 2008

I am trying to enter dates in a form that calls a report that invokes a query that uses the dates. It has been a less then satisfying experience. I am getting a Run-Time error 3122. Is it possible and I need to work on syntax or do I need to think of another way? BTW how do I lookup the Run-Time errors?
Thanks for helping an old guy learn new tricks.
Jim

View 5 Replies View Related

Pass A Set Of Current Records In A Form To A Query

Feb 15, 2007

I have a query set up. I need to pass a set of current records ( as I select them in a drop-down menu) in a form to the query as parameters. How would I do it with or without VB? Thanks !

EDIT: Forgot to mention that all combo boxes are bound so they are not customized dialog-boxes which are unbound

View 3 Replies View Related

Append Query Pass ID Format And Data

Jul 20, 2007

Hello,

Is it possible to pass a AutoNumber Format variable and data to a new table using an Append Query? If not does anyone know what I can try?

Thanks!

View 1 Replies View Related

Pass Multiple Values On Form To Query

Jun 28, 2005

Hi there. What I'm attempting to do is pass multiple values from a multiple selection list box as criteria for an Append Query. Is there any way to do this? The DB keeps track of Real Estate boards and the forms that they use, I would like to be able to select all the boards in a given state/province, but have the ability to deselect some within that province if I don't need them in the query (this is the criteria i'm trying to pass). I don't need to use a list box, any control that would allow me to pass multiple values would be great. Anyone able to help? Thanks very much.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved