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 Replies


ADVERTISEMENT

Trim Text Box Causing Error?

Jul 6, 2006

Hi all, this is my first post, I'll try and make it a good one :-)

System:
Windows 2000
Office 2000

I have a small network of PC's here which have access to our central database. I have a simple form created using the label wizard which is used to print address labels for mailing lists.
On most of the PC's here it works fine but on one or two (which have no obvious difference in thier setup) I get an "ODBC--Call Failed" error and it won't run the report????
I have puzzled over this for a while when I decided that I would remove the Trim function from the lines of the address fields on the label, these are put in automatically by the label wizard. This stopped the error message and the reports now run.
I don't know why this is happening but I am hoping someone will.

Thanks,

Matt.

View 6 Replies View Related

DSNless Connection Causing Error....

Dec 15, 2005

I am forced to switch from a DSN connection to my database to a DSNless connection by my hosting company. I changed my connection string from this:

strConn = "dsnname"
set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open strConn

to the following:

set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:pathtodatabasemydb.mdb;"

I connect fine. But when I run a certain query, I get the following error:

Microsoft JET Database Engine error '80040e14'

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

The SQL statement in question here is:
SELECT MAX(week) AS LAST FROM Schedule WHERE Result <>''

If I change my connection back to the original DSN connection, everything is fine once again.

How would a different connection produce an error, and more importantly, how do I fix it?


Thanks,
Ted

View 3 Replies View Related

Modules & VBA :: Assigning Value To Variable Causing Error

Jul 19, 2013

Is the following possible? If so - how?

I have a series of several hundred variables being assigned values.

If the value assigned to the variable results in an error, I would like to assign a default value to the variable which caused the error.

How do I reference the variable, or line (or any info I can work with), which caused the error?

View 2 Replies View Related

Intermittent Generic Error Messages Causing A Major Inconvenience

May 26, 2006

Hi,

I am absolutely puzzeled with a problem we are having with an Ms Access 2000 application, which is the front end to a SQL Server database. We have a database which was working fine for over 6 months. And now all of sudden users are intermittently getting the error messages:

"Error Executing this command"
AND
"You cancelled the previous operation."

I removed the error handling code from the application to get more detailed error messages and we got the following error message:


The expression On Click you entered as the event property setting produced the following error: .

The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].

There may have been an error evaluating the function, event, or macro.


This is only intermittent and when they close the database and open it again it works fine for a little while and then the error occurs again. As you can imagine this is a major inconvenience for users.

I have searched the web and forums and there just seems to be 101 reasons why this could happen. Nothing has changed on our system which might cause this occur so I don't know why this is happening.

Could anybody provide any ideas on how I might go about trouble shooting it. We are thinking of opening a support call with Microsoft but the error messages are so generic that I'm not sure what help they could be.

Any help / ideas or suggestions would be greatly appreciated.

Many Thanks

View 4 Replies View Related

Criteria For Date Field Causing 'data Type Mismatch' Error

Jul 31, 2005

Hi,

I have a query with a field defined in the query as follows:

Next Bill Date:
IIf(IsNull([dtmLastBillDate]),
DateSerial(Year([dtmDateRecd]),Month([dtmDateRecd])+1,15),
DateSerial(Year([dtmLastBillDate]),Month([dtmLastBillDate])+[lngNumMonths],15))

I can't seem to set any criteria for this field without getting an error:
'Data Type Mismatch in Criteria Expression'

Examples of criteria that I've tried:
=#1/1/2005#
>#1/1/2005#
=Date()
Year([Next Bill Date]) = Year(Now())

All of the above generate that same error.
{I've seen many pages/posts regarding 'data type mismatch' but none seem to relate directly to this situation.}

I could use some help, fast!

Thanks in advance,
David

View 1 Replies View Related

Modules & VBA :: Send Meeting To Outlook - Address Item Line Causing Error

Mar 24, 2014

I'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.

Error message highlights the red line below with the following message:

Runtime error '438'
Object doesn't support this property or method

I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.

Code:
Private Sub cmdSend_Click()
Dim outApp As Object
Set outApp = CreateObject("Outlook.application")
Set outmail = outApp.CreateItem(olappointmentitem)

[Code] .....

View 10 Replies View Related

Query Causing Problem With Dates

Sep 26, 2005

ok i have a query with 2 fields,

Endtime: AccountTime
Starttime

both date input from another query, if i run without any criteria it works and get some results:

Endtime Starttime
9/6/2005 6:24:09 AM9/6/2005 6:23:53 AM
9/6/2005 6:24:16 AM9/6/2005 6:21:20 AM
9/6/2005 6:24:34 AM9/6/2005 6:24:22 AM
9/6/2005 6:26:06 AM9/6/2005 6:25:51 AM
9/6/2005 6:26:15 AM9/6/2005 6:25:00 AM

and i have some inputs on a form (type - genreal date) with the values:
startdate:
09/06/2005 00:00:01
enddate:
09/06/2005 23:59:59

and the condition is on starttime field:
Between [Forms]![Main]![startdate] And [Forms]![Main]![enddate]

but i get no results....????

any help?
thanks in advance.

Dal

View 1 Replies View Related

Reports :: Sum Function In Report Causing Re-sort Of Query

Aug 6, 2014

I have a report that runs off a query that is sorted in descending order the price of something. This price column is in the middle of the report. Every time I try to add a function (sum or count of a column for example) in the report footer or header however, my report is then immediately resorted in ascending values of the first column.

View 2 Replies View Related

Queries :: Numbering Column Causing Query To Stop Responding

Aug 5, 2013

I ran some code to number old records in a column to order items in a datasheet subform however after updating the column in the inventory transactions table when opening the main form that relies on it. It runs a query to determine the customers balance due and the query for that is taking forever to run like 2-3 minutes or more however reverting to an old copy of the data it runs fine and all I did was renumber records in one column most of which had 1s in them the code to re-number the records:

Code:
Dim rst As DAO.Recordset
Dim i As Long
Dim OID As Long
Dim stringSQL as String
i = 1

[Code] .....

I continued playing with this today and the query that is breaking the 2nd level subquery that actually references the inventory transactions table that I changed runs fine but go up a level to subquery1 which references subquery2 and a sum of payments query and it chokes however sum of payments only refernces the payments table.

I did some more testing today Subquery2 and sum of payments query both run faster than what I can time with a stopwatch but when combined they take approximately 1:45 there are only 5 columns in subquery1 four from subquery2 and 1 from the sum of payments 3 of the first 4 are sum and the last is group by and then the one from sum of payments is group by

Subquery 2:

Code:
SELECT
CLng((nz([UnitsSold])*nz([UnitPrice]))*(1-nz([Discount]))*100)/100 AS [Line Total],
CLng([Line Total]*(1+nz([SalesTaxRate]))*100)/100 AS [Line Total With Tax],
[Line Total With Tax]-[Line Total] AS [Line Tax],
[Inventory Transactions].*

[Code] .....

View 3 Replies View Related

Queries :: Setting Parameter Labels Is Causing Query To Return No Data

Jul 3, 2014

My parameters are linked to a form and say:

[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null

Ordinarily this works fine in returning either the selected value or all values if left null.

I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].

The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.

If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.

The crosstab data isn't specifically the problem but needing to set the parameter names seems to be

I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels

It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...

View 7 Replies View Related

Queries :: Form To Select Parameter - Causing New Field In Query Design

Jun 16, 2015

I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.

Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.

View 1 Replies View Related

Queries :: Soft Coded Date Filter Causing Query To Run Slow

Jan 2, 2015

created a query (in Access 2010) that joins several linked tables (to an Oracle database). The query runs in about 20 seconds when I filter with a hard coded date (e.g., #12/31/2014#). The Oracle table column Im filtering on is defined as date/time.

When I attempt to change the hard coded value to a soft coded value (e.g., Forms![Form1]![Latest_Extract_Date]), the query runs over 5 minutes. In this case, the form field has the exact same value (12/31/2014).

Ive encountered similar issues using Access 2000, 2003, etc. This is quite frustrating. Does Access interpret #date value# is a special way? Is there a way to trick Access into the thinking a soft coded date is a hard coded date?

View 1 Replies View Related

Procedure Works Great First Pass, But Not The Second Pass

Jul 15, 2005

Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.

View 8 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

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







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