473,883 Members | 1,798 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

add hint in access query

Hi,
I'm using MS Access to query against Oracle DB via ODBC. Is it
possible to use HINT in Access?

Thanks,

Wei

Oct 2 '06 #1
12 5295
If you make the query pass-though..then, you can use the raw oracle syntax,
and that would include using hint syntax in the sql...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
Oct 2 '06 #2
Albert,
Thanks for your reply.
If I use pass-through query, how can I convert access sql to raw
oracle sql? Do I have to do it manually, or access can do it for me?
Can I query across DB schema, which is to query against tables in
different schema? In access, we use "link table" to bring tables from
different schema together. How can I do it in raw oracle sql?

Thanks again,

Wei

Albert D. Kallal wrote:
If you make the query pass-though..then, you can use the raw oracle syntax,
and that would include using hint syntax in the sql...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
Oct 2 '06 #3
When you create a query in MS Access, it has to be in the Access
version of SQL. When you create a pass-through query, you do not get
the QBE grid, but rather, just a screen ready for you to type in your
SQL. Access doesn't look at it, but just submits it to Oracle to
process. You can use whatever SQL you would like. Oracle will then
give back the records that meet that criteria.
Chris Nebinger
zw****@gmail.co m wrote:
Albert,
Thanks for your reply.
If I use pass-through query, how can I convert access sql to raw
oracle sql? Do I have to do it manually, or access can do it for me?
Can I query across DB schema, which is to query against tables in
different schema? In access, we use "link table" to bring tables from
different schema together. How can I do it in raw oracle sql?

Thanks again,

Wei

Albert D. Kallal wrote:
If you make the query pass-though..then, you can use the raw oracle syntax,
and that would include using hint syntax in the sql...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
Oct 2 '06 #4
Right. But as far as I know, in Oracle we can't query tables from
multiple schema, where they can't see each other (For example, schema A
doesn't have access to table T in schema B). In Access, we can do it
through linked tables, but I can't control the query plan.

What I want is capability of both. Is it possible?

Thanks,

Wei

Oct 2 '06 #5
Lord, you can, but I wouldn't want to. you would leave it to Access to
join the tables, which would be horrendous.

I would rather pull the appropriate data from table a, put it in a temp
table, then modify the pass-through SQL on table b to only pull the
records that would be joined, then add it to a second table in Access.
Then, use a native query to display these. This is a horrible
solution, however.

Chris Nebinger
zw****@gmail.co m wrote:
Right. But as far as I know, in Oracle we can't query tables from
multiple schema, where they can't see each other (For example, schema A
doesn't have access to table T in schema B). In Access, we can do it
through linked tables, but I can't control the query plan.

What I want is capability of both. Is it possible?

Thanks,

Wei
Oct 2 '06 #6
zw****@gmail.co m wrote:
If I use pass-through query, how can I convert access sql to raw
oracle sql? Do I have to do it manually, or access can do it for me?
Can I query across DB schema, which is to query against tables in
different schema? In access, we use "link table" to bring tables from
different schema together. How can I do it in raw oracle sql?
By using different connect strings for the ptq.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Oct 3 '06 #7
Tim,
Would it be ok to show me how to use different connect strings in the
ptq?
For example, I have table T1 in schema A, and table T2 in schema B.
schema A can't access T2, and schema B can't access T1. c1 and c2 are
columns in T1 and T2.
How do I write the following query as a ptq?

select T1.*
from T1, T2
where T1.c1=T2.c2

If I login A, then B.T2 does not work in above query.
The connect strings are: u1/p1@A and u2/p2@B

Thannks a lot!

Wei

Oct 3 '06 #8
zw****@gmail.co m wrote:
Tim,
Would it be ok to show me how to use different connect strings in the
ptq?
For example, I have table T1 in schema A, and table T2 in schema B.
schema A can't access T2, and schema B can't access T1. c1 and c2 are
columns in T1 and T2.
How do I write the following query as a ptq?

select T1.*
from T1, T2
where T1.c1=T2.c2

If I login A, then B.T2 does not work in above query.
The connect strings are: u1/p1@A and u2/p2@B
Wei, to the best of my knowledge, you'd need to use a combination of MS
Access's native Jet engine and the pass through queries.

I use ODBC connections to access Oracle tables. To do this, I'd have to
set up two separate DSNs, one for schema A, the other for schema B.

Create two pass through queries, first. If you're not sure how to do
this, simply click on the new button in the query tab/view in the MS
Access database window and choose design view. With the query design
grid that opens, a pop up window entitled "Show Table" appears. Close
the pop up without choosing anything and on the Query menu select SQL
SPecific -Pass-Through. A blank SQL window now appears.

Write your statement, say:

SELECT *
FROM T1

Then, press alt-enter to open the Access properties window for this
query. The second text box should read "ODBC Connect Str". Click the
builder button and select the T1Dsn. Save the pass-thorugh query as
something convenient, I'd use ptqT1

Repeat for T2, using T2Dsn and save as something like ptqT2.

Now you have two objects saved in your MS Access query tab/view. What
you can do now, is construct a third query in the MS Access query
tab/view that is not Oracle SQL, but actually Jet SQL. In SQL it would
look something like:

Select T1.*
From T1 INNER JOIN T2 ON T1.c1 = T2.c2

And now you've got the two schema holding hands together, hurrah! 8)

There may well be a better way to do this in Oracle SQL only, but I'm
not versed well enough in Oraclese to know how to do it.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Oct 3 '06 #9
Tim Marshall wrote:
Create two pass through queries, first. If you're not sure how to do
this, simply click on the new button in the query tab/view in the MS
Access database window and choose design view. With the query design
grid that opens, a pop up window entitled "Show Table" appears. Close
the pop up without choosing anything and on the Query menu select SQL
SPecific -Pass-Through. A blank SQL window now appears.

Write your statement, say:

SELECT *
FROM T1
Wei, not sure how clear I was on this, but the SQL written in any
pass-through query would be the Oracle dialect you are used to working
with. Not Jet dialect (Jet being the engine packaged with MS Access).

I always work with Oracle tables from the same schema (possibly
different user names, but always the same schema). I prefer PTQ as
opposed to using linked Oracle tables because:

1) Joins are oh so much easier to write code for if you are writing VBA
to create dynamic SQL (by "dynamic", I mean SQL that is constructed in
response to choices made by the user on MS Access forms);

2) The linked table method absolutely dies when you are using a lot of
sub-queries of any type, at least with my data, anyway; and

3) Performance really slows when more than 5 or 6 tables are joined and
sometimes, especially when you have only some or even no control over
the Oracle data structure to modify or create indexes, the MS Access
linked table queries breakdown and don't perform properly altogether.

I find MS Access to be an absolutely wonderful tool for Oracle
administration and even better for application development.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Oct 3 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
5076
by: john smile | last post by:
Hi All, I want to lock 2 tables on 2 servers using TABLOCKX hint. These tables function as semaphores in my application. It means when the tables are locked then other users will not be able to access them and automatically they can not continue their works. I have tried using the following code, but it does not work. I always got the error :
2
25439
by: Stephen McMahon | last post by:
Background: I am currently working on a mission critical web based application that is accessed 24 hours a day by users from just about every time zone. We use MS SQL Server as our database and we have lots of problems with time-outs. We used to have lots of problems with locks until my management decided that we would use the WITH (NOLOCK) hint on EVERY select statement and WITH (ROWLOCK) on EVERY update statement. I have argued since...
1
2440
by: Mark P | last post by:
Is an insert with hint (in say std::set) only beneficial if the insertion occurs immediately before the pointed to location of the hint? Suppose the insertion occurs right after the hint location? Or suppose the insertion is near to the hint location but not adjacent? Is there a performance penalty for an approximate hint, a partial gain, or no gain? Thanks, Mark
6
7032
by: Mark P | last post by:
Some time ago I posted here about inserting into a set with a hint: http://groups-beta.google.com/group/alt.comp.lang.learn.c-c++/browse_thread/thread/fb75b00f73e979db/018b8d0eadb38dbf?q=%22STL+insert+with+hint%22+%22Mark+P%22&rnum=1&hl=en#018b8d0eadb38dbf I quoted the SGI STL docs describing a.insert(p, t), where p is the hint iterator and t is the inserted object: "Insert with hint is logarithmic in general, but it is amortized...
7
3355
by: piperzen | last post by:
Hi, I'm developing a set of language learning exercises for http://www.yale.edu/swahili , and we've run into a javascript question that the programmer doesn't know how to tackle (we mostly work in php, etc.). This is for an educational project that is available to the public for free, so I'm hoping someone might be able to volunteer a little time to help us sort this out. What we want to do is this:
14
5430
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB 7.2 environment, the choices the optimizer makes often seem flaky. But this last example really floored me. I was hoping someone could explain why I get worse response time when the optimizer uses two indexes, than when it uses one. Some context:
52
10023
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server 2005, and, since he already has licenses for Office Pro 2002, he wants to upgrade to that. I've been saying that we need to upgrade to Access 2003, not 2002, even if Office is kept at 2002. We are also looking to do a fair amount of...
7
12215
by: alexander324 | last post by:
I have an access table that has around 4000 records showing various carrier names, origin and destination city, state and zip along distance and cost etc. Is there a way to automate a process that will break up the table into multiple tables based on carrier names? For example, each separate table will only contain data for one particular carrier. Please help me with this. I am not very savvy with Access but need to finish up the project...
3
4617
by: suneelchetty | last post by:
I am firing a query which uses a table hint /*+ USE_CONCAT */ on a table which 250 million data with 13 indexes on it. The problem is query is taking a lot of time to execute i.e. more than 3 minutes. Query : SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY WHERE NORM_COUNTRY_CD = 'US' AND ((( NORM_CONAME_KEY1 ='WILM I' OR NORM_CONAME_KEY2 = 'WILM I' OR NORM_CONAME_KEY23 = 'WILM I' OR NORM_CONAME_KEYFIRST ='WILLIAM' ) AND...
0
9942
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10745
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10417
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7972
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7130
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5992
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4221
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3234
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.