add hint in access query

[zwasdl]

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

Thanks,

Wei

[Albert D. Kallal]

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

[zwasdl]

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

[chris.nebinger]

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.com 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

[zwasdl]

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

[chris.nebinger]

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.com 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

[Tim Marshall]

zw****@gmail.com 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

[zwasdl]

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

[Tim Marshall]

zw****@gmail.com 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

[Tim Marshall]

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

[Albert D. Kallal]

If I use pass-through query, how can I convert access sql to raw

oracle sql?

Unless oracle has some ability to do this, no, you can't You can even send
oracle execute commands with a pass through.
pass-through means msaccess/jet does not touch the sql...it is all 100%
oracle....

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?

I am not sure I understand the above question? You moved the data to oracle
(or it was there in the first place). You are now running some quires built
in ms-access against that data? (what does copying the db schema have to do
with this?) I mean, sure, there is some oracle tools to import the data if
that is what you are asking??? What import options, and what options oracle
has to import ms-access data, and the db schema is going to be that of
oracle. I not aware that is it common that MySql, Sybase, Sql-server, or
ms-access are in the business of making tools to transfer their data to
oracle. Quite sure this is going to be a oracle question...check in their
news groups....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[zwasdl]

Albert,
I'm not talking about transfering data to Oracle. The Data were in
Oracle, and I just use MS/Access to access them via ODBC.
I want to link tables in two Oracle databases with MS/Access (through
linked table) and still be able to use Oracle hints. Is it possible?

Thanks,

Wei

Albert D. Kallal wrote:

If I use pass-through query, how can I convert access sql to raw
oracle sql?

Unless oracle has some ability to do this, no, you can't You can even send
oracle execute commands with a pass through.
pass-through means msaccess/jet does not touch the sql...it is all 100%
oracle....

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?

I am not sure I understand the above question? You moved the data to oracle
(or it was there in the first place). You are now running some quires built
in ms-access against that data? (what does copying the db schema have to do
with this?) I mean, sure, there is some oracle tools to import the data if
that is what you are asking??? What import options, and what options oracle
has to import ms-access data, and the db schema is going to be that of
oracle. I not aware that is it common that MySql, Sybase, Sql-server, or
ms-access are in the business of making tools to transfer their data to
oracle. Quite sure this is going to be a oracle question...check in their
news groups....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Tim Marshall]

zw****@gmail.com wrote:

Albert,
I'm not talking about transfering data to Oracle. The Data were in
Oracle, and I just use MS/Access to access them via ODBC.
I want to link tables in two Oracle databases with MS/Access (through
linked table) and still be able to use Oracle hints. Is it possible?

Although I dealt with PTQs, I think if you'd try my solution, you'd see
that it is quite possible.

Create the DSNs and then link the tables, instead of creating the PTQs
as I described. Once you do it, you'll see it's really a no brainer.

8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me