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 Replies


ADVERTISEMENT

Query..not Return Double?? Tables - Oracle

Feb 8, 2007

Ok. I have tables vinculate to ORACLE, the primary key of Oracle is double, from Access of query return one number rounding 1,00000902026541907589E+20 this is the problem, in query i have this funtion:
IIF(isnull([CAMPO1]);[CAMPO2];[CAMPO1]) this return one number ok field(CAMPO1) and other bad(CAMPO2 it's rounding). I need that return this query two doubles. example:

bad - 1,00000902026542E+20 = 100000902026542000000
ok - 1,00000902026541907589E+20 = 100000902026541907589

help me...:confused:

My english is bad bad bad... :D

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

Need Equalent MS Access Query For An Oracle Query

Apr 19, 2007

This is a Oracle query... its working in Oracle but didnt work in access...

select v.code_number,v.vehicle_number,v.company_code,r.fc _valid_to,
i.next_due_date
from (vehicledetails v left outer join rtodetails r
on v.code_number=r.code_number AND v.code_number='SMR5'
left outer join insurancedetails i
on v.code_number=i.code_number);


I need equalent Access query for this.... anyone plz help....

View 5 Replies View Related

ORACLE PROBLEM - Linked Tabled-Update Query

Jun 1, 2007

Hello Everyone,

Here is my issue:

1. I have linked a table which does not allow me to update any entries.
2. In this linked table I have to update one column that contains all null values
3. I have another table that contains the values required for that one column in the linked table

When I try running an update query after doing an Inner Join, i get the Operation must use an updateable query error...is there ANY work around at all ? Please let me know . any help would be greatly appreciated.

View 6 Replies View Related

Acces Pack And Go

Oct 2, 2006

I am looking for a way to export my database and all linked files into one folder with automatic updated links. I would like to transfer the complete project to an other computer. Is this possible? How to do? Thank you.

View 1 Replies View Related

Acces Or MySQL

Oct 16, 2006

Which is better fo running a website which is small?

MySQL or Access?

Sathu

View 1 Replies View Related

Acces Hide Field Value

May 10, 2005

Hi i've got a table with a couple of fields

What's in the fields is irrelevant but i'd like to
change that to *****
the original value should stay there
but when you view the record it should only give *****

for example

field value : HELLO
when viewed: *******

Does anyone know how this could be done I ones knew how it was done but can't find it anymore

plz help

View 3 Replies View Related

Every Acces Screen Has Its One Toolbar Btn

Dec 21, 2005

I only want the active screen on the toolbar. and all other screens like the DB screen and forms that are not active to be hidden on the toolbar.

I hope you can understand it. else i wil make a screendump of what i want.

View 1 Replies View Related

Pictures/PHotos In MS Acces

Jun 25, 2007

I'm not brilliant at using MS Access - can't use the VBA codes or anything like that.

basically, I want to set up a database of photogrpahs where you store thephotograph in Access, and then run a query to search for specific photos, which brings up a form showing the picture.

I seem to be OK at doing all of this so far except I can't get the photo to appear in the form - it just displays a link which you double click on to get to the photo.

I've tried to look up how to sort this on the web but don't understand the way people speak about access :confused:

can anyone help???

View 12 Replies View Related

Finished Acces Db. How Should I Set Up For Users?

Feb 28, 2008

I have completed a db and am satisfied with the structure and forms. The object window is hidden when the database is opened, and the main entry form automatically opens for the user. This db will be in a network folder and several users will have access to it. I want people to be able to enter data and run reports, but not make any changes to the database itself, or accidentally delete the database file or some other change to the structure. What is the proper way to set up the folder rights and anything else associated with the previous question?

Thanks.

View 1 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 Do You Create Acces Data Pages For Update

Jan 22, 2006

I am a first time user of access. i am trying to create an Data Access Page which will list all entries in a table and then allow me to add, change and delete them. I have created the page using the wizard but it will not let me add, delete or update. i view all the entries at one time but that is all. the navigation toolbar shows up at the bottom but it is grayed out. any ideas on what i may be doing wrong?


thanks
brian

View 5 Replies View Related

Selective Data Acces Depending On Login

Jan 11, 2006

I have a two level security system to protect my date. The first uses the MS Access security, the second is a simple login system wherby a user enters their name and personal password to gain a customised view of the data eg if the user is me, I can see all tool bars, other users cannot. What I would like to be able to do is write a query wherby I can use the users login details to diplay only their data. An example of the script I built is as follows

'IF USERNAME AND PASSWORDS CORRECT THEN LOGIN TO THE MAIN SWITCHBOARD

If Me.qpwd.Value = DLookup("PWD", "AUTHORISED", "USERID = '" & Me.quserid.Value & " '") Or UCase$(Me.qpwd.Value) = "abcd" Then
DoCmd.Close
DoCmd.OpenForm "Course Booking System"
Else
MsgBox "Invalid Password"
End If

Is there a way of including a select criteria like the following

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID="Me.qpwd.Value"

I have tried several differnet versions for this syntax without success!

Any help would be greatly appreciated.

Regards

Peter

View 3 Replies View Related

Handling Filter In A Ms Acces(2000) Forms

Oct 19, 2006

hi,

i am currently dealing with a form which does not show data after it has been opened.

it shows the data after removing the filters from the form when it is opened.

in fact the filters are set to false

Me.FilterOn = False.

so kindly help me out from this problem

thanking you

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

DateDiff Function - It Doesn't Work In Some Version Of Acces ?

Apr 11, 2008

Hi All

I've created a database where a query who'se result is loaded on a form uses following function --

DateDiff('d',Date(),[Target Date])-(DateDiff('ww',Date(),[Target Date])*2)-(Select Count(*) from [tblHolidays] where [HolidayDate] between Date() and [Target Date])


I've notice that 2 ppl get an error message when they try to open database, with thatt function coming on an error message
Does this mean they have older access version ?
How can I tell ? Any advise on what to do ?

View 2 Replies View Related

Populating A Listbox In Excel With A Growing Table In Acces... Uhh...

Oct 13, 2004

So here's my problem. I'm trying to update a listbox in excel with values that are being pushed in a table in access. The problem is that there are repeated values in the column in access, but I only want to show each value once, regardless of how many times it appears in the table. I would give a code sample but I'm having trouble getting started...

Any help is appreciated.

--patrick-->

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







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