Alfonso Esteban Gonzalez Sencion wrote:
I have the problem when I consult the table in access, no queries.
I see. Interesting problem. Except for the possible remedies/areas of
investigation I've suggested, I can't offer anything else at this time.
Pass-through queries? What is that and how can I do that?
Pass through queries are sent directly to the database using the native
SQL dialect of the database in question. That may sound a bit daunting
to you if you are relatively new to this! 8)
In the case of Oracle, when you are using linked tables, the Jet
database engine of Access first processes the query at the client (user)
computer. Access then passes the statement to the server which
processes it and returns results. Jet then has to process those results
at the client again.
In a pass through query, Access passes the statement directly to the
server and accepts the results, bypassing Jet.
The big advantage of using linked table queries is that you may use the
query builder in Access. Because you are going through Access's
database engine, Jet, the resultant SQL from the query builder is in Jet
dialect.
With pass through queries, you have to use the SQL dialect/syntax of the
database, in your case, Oracle.
So, for instance, given tableA and tableB which link from tableA.A_PK to
tableB.B_A_FK a select query would appear as:
Jet:
Select
FieldA1 [Field Name],
FieldB1 [Another field Name]
from
tableA INNER JOIN tableB ON tableA.A_PK = tableB.B_A_FK
Oracle:
Select
FieldA1 "Field Name",
FieldB1 "Another Field Name"
from
tableA,
tableB
where
A_PK = B_A_FK
How do you make a pass through query?
Simply open a new query on the database window. I use A97 and A2003,
but I assume it's the same in A2000 - click on Query->SQL
Specific->Pass-Through. You'll end up with the SQL view. You can type
in your Oracle query here, using Oracle syntax.
Next, you must establish the connection. Click on View->Properties and
then, on the query properties window, click the builder button (the
little 3 dot button) on the ODBC Connect Str property.
You will then be faced with the Select datasource pop up dialog you are
used to seeing when you choose to link to ODBC tables the way you are
probably used to now.
Then run your query and have loads of fun!!!! 8) 8)
--
Tim -
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto