By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Linked tables problem with ODBC Access 2000 and Oracle 8i

P: n/a
I am trying to use Access as a front end for extracting information from
an Oracle database. I started using linked tables but I am getting a
very curious behaviour.

When I consult the linked table in access, the total number of records
is OK but some records appear several times and some records do not
appear at all. It seems as if access or the ODBC drivers returns several
times the same record and skips some of the records, curiosly the total
number of records is OK!

If I import the table from oracle into access the data appeasr
correctly: Total number or records ok, and all records are present on
the table.

I am using ODBC drivers from Microsoft and Oracle. The behaviour is the
same on both cases.

For the moment I am using imported tables in access but I would rather
prefer to have linked tables. It is a cumbersome to reimport all the
data every time the oracle database is updated.

Any suggestions?
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Alfonso Esteban Gonzalez Sencion wrote:
For the moment I am using imported tables in access but I would rather
prefer to have linked tables. It is a cumbersome to reimport all the
data every time the oracle database is updated.


Are you getting this behaviour when looking at the tables directly or
via a query in which several of the linked tables are involved?

If the latter, depending on the number of tables involved, whether or
not the Oracle 8i tables are indexed where you make the joins, you can
have some problems. In the 8i database I regularly work with, I'm
limited to about 4 or 5 table joins before weird things start happening
such as dropping data on a whle column.

Do the 8i tables have primary keys already defined? Or do you have to
chose a column/field when you make the table link? If the latter,
sometimes a poorly chosen field that is not unique will produce weird
results in a linked table. The field you use for the primary key *must*
be a unique value.

Personally, I've gone to using pass-through queries myself. Generally
(though not always) faster, more reliable but you can still use Access
report and form writing capability.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #2

P: n/a
I have the problem when I consult the table in access, no queries.

Of course with queries I have also the problem.

Pass-through queries? What is that and how can I do that?

Tim Marshall wrote:
Alfonso Esteban Gonzalez Sencion wrote:
For the moment I am using imported tables in access but I would rather
prefer to have linked tables. It is a cumbersome to reimport all the
data every time the oracle database is updated.

Are you getting this behaviour when looking at the tables directly or
via a query in which several of the linked tables are involved?

If the latter, depending on the number of tables involved, whether or
not the Oracle 8i tables are indexed where you make the joins, you can
have some problems. In the 8i database I regularly work with, I'm
limited to about 4 or 5 table joins before weird things start happening
such as dropping data on a whle column.

Do the 8i tables have primary keys already defined? Or do you have to
chose a column/field when you make the table link? If the latter,
sometimes a poorly chosen field that is not unique will produce weird
results in a linked table. The field you use for the primary key *must*
be a unique value.

Personally, I've gone to using pass-through queries myself. Generally
(though not always) faster, more reliable but you can still use Access
report and form writing capability.

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
Alfonso Esteban Gonzalez Sencion wrote:
I have the problem when I consult the table in access, no queries.

Of course with queries I have also the problem.
Tim already mentioned this, but as you did not address it in your reply
I thought it might be worth mentioning it again.

Tim wrote:Do the 8i tables have primary keys already defined? Or do you have to
chose a column/field when you make the table link? If the latter,
sometimes a poorly chosen field that is not unique will produce weird
results in a linked table. The field you use for the primary key
*must* be a unique value.

--
To Email Me, ROT13 My Shown Email Address

Nov 13 '05 #5

P: n/a
It looks as though Access has somehoe identified the wrong primary key index.
I don't know about Oracle, but this can happend with Microsoft SQL Server when
there is a non-unique Clustered index, and Access always uses chooses the
clustered index if there is one, even if it is not a unique index.

On Thu, 21 Oct 2004 10:17:36 +0200, Alfonso Esteban Gonzalez Sencion
<se*****@alcatel.es> wrote:
I am trying to use Access as a front end for extracting information from
an Oracle database. I started using linked tables but I am getting a
very curious behaviour.

When I consult the linked table in access, the total number of records
is OK but some records appear several times and some records do not
appear at all. It seems as if access or the ODBC drivers returns several
times the same record and skips some of the records, curiosly the total
number of records is OK!

If I import the table from oracle into access the data appeasr
correctly: Total number or records ok, and all records are present on
the table.

I am using ODBC drivers from Microsoft and Oracle. The behaviour is the
same on both cases.

For the moment I am using imported tables in access but I would rather
prefer to have linked tables. It is a cumbersome to reimport all the
data every time the oracle database is updated.

Any suggestions?


Nov 13 '05 #6

P: n/a

As far as I know the field is unique. I have done a query to count the
number of instances of each field and there are no repetitions.

For more information here is the query with Access 2000

SELECT ID, KM_POINT_ID, TRANSMISSION_MEDIUM_TYPE
FROM BALISES
ORDER BY ID;

ID KM_POINT_ID TRANSMISSION_MEDIUM_TYPE
100 101 0
101 101 0
102 101 0
103 100 0
100 101 0
105 129 0
100 101 0
107 129 0
108 128 0
100 101 0
100 101 0
111 176 0
100 101 0
An here the query with Oracle

SQL> select ID,KM_POINT_ID, TRANSMISSION_MEDIUM_TYPE from balises order
by id;

ID KM_POINT_ID TRANSMISSION_MEDIUM_TYPE
---------- ----------- ------------------------
100 101 0
101 101 0
102 101 0
103 100 0
104 100 0
105 129 0
106 129 0
107 129 0
108 128 0
109 128 0
110 176 0

Very curious indeed....! The result in Access are not ordered by ID and
as you can see the record with ID appears several time. That does not
happen in Oracle

More curious still if I make the following query in Access.

SELECT ID, KM_POINT_ID, TRANSMISSION_MEDIUM_TYPE
FROM BALISES
GROUP BY ID, KM_POINT_ID, TRANSMISSION_MEDIUM_TYPE
ORDER BY ID;

I get the right results

ID KM_POINT_ID TRANSMISSION_MEDIUM_TYPE
100 101 0
101 101 0
102 101 0
103 100 0
104 100 0
105 129 0
106 129 0
107 129 0
108 128 0
109 128 0
110 176 0
111 176 0

And the total number of records is also equal to the previous access
query!!!! Therefore there are not repeated ID although in the first
acces query appears repeated ID!

I am completely lost.

John Baker wrote:
Alfonso Esteban Gonzalez Sencion wrote:
I have the problem when I consult the table in access, no queries.

Of course with queries I have also the problem.

Tim already mentioned this, but as you did not address it in your reply
I thought it might be worth mentioning it again.

Tim wrote:
>Do the 8i tables have primary keys already defined? Or do you have to
>chose a column/field when you make the table link? If the latter,
>sometimes a poorly chosen field that is not unique will produce weird
>results in a linked table. The field you use for the primary key
>*must* be a unique value.

Nov 13 '05 #7

P: n/a
Alfonso Esteban Gonzalez Sencion wrote:
As far as I know the field is unique. I have done a query to count the
number of instances of each field and there are no repetitions.


Hi Alfonso - from the sounds of it, you don't know for sure if a unique
or primary constraint has been actualy placed on the ID field.

Are you familiar with Oracle? If you are and can get into an Enterprise
Manager session with the Oracle database, check to make sure BALISES has
a primary key constraint on ID. INdeed, if it does not, check to make
sure BALISES has a primary key constraint on another field (ID sounds
like it is a vehicle ID, right? If so, this is not a good field to have
this as a PK).

In a properly set up Oracle table, an Access user utilizing ODBC and
linked tables really does not need to know anything about Oracle.
However, given the problems you're facing which I have never ever seen
with an Access linked table when the Oracle table is, as I mentioned,
properly set up, I think you need to either explore what's happening in
Oracle itself. If you are not super familiar with Oracle, hopefully you
have an Oracle DBA who can answer some of these questions for you.

The conept of primary key is pretty standard throughout database
software, I think. It really sounds as if you're dealing with an Oracle
table not fully set up.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #8

P: n/a
Tim Marshall wrote:

Sorry, I made some typos and I'm assuming Alfonso's mother tongue is not
English. Please let me correct and clarify:
Alfonso Esteban Gonzalez Sencion wrote:

sure BALISES has a primary key constraint on another field (ID sounds
like it is a vehicle ID, right? If so, this is not a good field to have
this as a PK).
Should be "If so, this is not a good field to set up as a primary key."
However, given the problems you're facing which I have never ever seen
with an Access linked table when the Oracle table is, as I mentioned,
properly set up, I think you need to either explore what's happening in
Oracle itself.
Should be "I think you need to either explore what's happening in Oracle
itself."
The conept of primary key is pretty standard throughout database
software, I think.


Should read "concept"
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.