473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Linked tables problem with ODBC Access 2000 and Oracle 8i

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
8 4014
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
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
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
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
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

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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: David Gray | last post by:
Hello all, Having problems connecting to an Oracle 9i database from within SQL/Server 2000 using the Security/Linked Servers feature. Server1 (SQL/Server) ----------- Windows Server 2003,...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
3
by: Ian | last post by:
We are currently experiencing problems with our Access XP database. We are using access as the front-end and are connecting to Oracle 7.3 via ODBC. Our users are running Win 98 and Win Xp. We are...
4
by: anand | last post by:
Hi, I have an Access 2000 database, which contains some native tables, and some linked tables which belong to an ORACLE database, through ODBC. Using VB.NET, I am trying to fetch some data by...
7
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For...
6
by: robin9876 | last post by:
In an Access 97 (and 2000) database that has linked tables to Oracle which is showing some fields as text when they are numbers in Oracle. In Oracle they are specified to a size e.g. Number(38)....
7
by: smd | last post by:
Hello and thanks for taking the time to read this. I've looked all over the web and newsgroups and can't find a solution to my problem. I've posted this question to the Access 2000 group as well -...
2
by: Jill Elaine | last post by:
I am building an Access 2002 frontend with linked tables to an encrypted Paradox 7 database. When I first create these linked tables, I'm asked for the password to the encrypted Paradox database,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.