UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Linking Table, Access 2007    
 
   
shah1419
post Sep 25 2018, 10:55 PM
Post#1



Posts: 64
Joined: 10-January 13



i have followed the following code and applies it to my table. i want to link access table to oracle table.
https://www.techonthenet.com/access/tables/...k_table2007.php

but not records found in oracle table.please advised me how i can solve this issue.
This post has been edited by shah1419: Sep 25 2018, 10:56 PM
Go to the top of the page
 
MadPiet
post Sep 25 2018, 11:24 PM
Post#2



Posts: 3,124
Joined: 27-February 09



Does the Oracle table have a primary key?
Go to the top of the page
 
shah1419
post Sep 25 2018, 11:27 PM
Post#3



Posts: 64
Joined: 10-January 13



no primary key.
Go to the top of the page
 
GroverParkGeorge
post Sep 26 2018, 10:02 AM
Post#4


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, we have no way to see the Oracle database, the way you tried to connect to it, and so on.
In order to trouble-shoot your problem, step-by-step description of what you actually did might be helpful. Can you add that detail?
Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
shah1419
post Sep 27 2018, 02:52 AM
Post#5



Posts: 64
Joined: 10-January 13



Step-1
click on the More button and select ODBC Database from the popup menu.
Step-2
Select the option called "Link to the data source by creating a linked table" and click on the OK button.
Step-3
A "Select Data Source" window should appear. Click on the Machine Data Source tab and select the ODBC connection for your Oracle database. In this example, we have chosen an Oracle database called "XE" and choose driver " Oracle for XE". Click on the OK button.
Step-4
A login window should appear. Enter your user name and password for the Oracle database. Click on the OK button.
Step-5
A link table shown in database after all tables.
When i click on this table . it shows nothing both in access and oracle.

Go to the top of the page
 
JonSmith
post Sep 27 2018, 03:21 AM
Post#6


UtterAccess VIP
Posts: 4,047
Joined: 19-October 10



QUOTE
When i click on this table . it shows nothing both in access and oracle.


Well then it looks like the linking is fine, what you see is consistent. The table is just empty.
Go to the top of the page
 
GroverParkGeorge
post Sep 27 2018, 07:29 AM
Post#7


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


Thanks for the clarification.
So, you see no records in Oracle directly. What do you think you SHOULD see instead?




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
shah1419
post Sep 27 2018, 11:12 PM
Post#8



Posts: 64
Joined: 10-January 13



only empty table(fields) in oracle. and in access the table has 20 records.
Go to the top of the page
 
JonSmith
post Sep 28 2018, 02:59 AM
Post#9


UtterAccess VIP
Posts: 4,047
Joined: 19-October 10



You aren't giving us enough information?

You state that there are records seen in the Access linked table but not in the Oracle one. You don't however tell us what you think the correct behaviour is.

It could be that the oracle database is not returning the records but Access is or vice versa.
The steps you took seem correct. You should obviously check the details in the ODBC, maybe its pointing to a test version of the same database. Who knows?

Help us the help you, provide a detailed description of what is happening and what you think should be happening and the steps you've already taken.
I frequently say this and get single sentence responses, which is to say the least, frustrating. So at the risk of going on about it, key word here is detailed. We don't need an essay but we do need more than what you are giving us. If you struggle with English no worries, do what you can, perhaps even also leave a copy of the issue in your native language and someone can help translate.
Go to the top of the page
 
GroverParkGeorge
post Sep 28 2018, 07:26 AM
Post#10


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


When you say, "... in access the table has 20 records"
Are you saying that the LINKED table in Access -- the one actually stored in Oracle -- shows 20 records?
Or are you saying the ACCESS table -- the one stored in Access -- shows 20 records?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
shah1419
post Sep 28 2018, 09:31 PM
Post#11



Posts: 64
Joined: 10-January 13



the one is located in access showing 20 record. in oracle oracle nothing.
Go to the top of the page
 
JonSmith
post Sep 29 2018, 04:35 AM
Post#12


UtterAccess VIP
Posts: 4,047
Joined: 19-October 10



Yep, you're ignoring me. You are on your own. Best of luck.
Go to the top of the page
 
GroverParkGeorge
post Sep 29 2018, 07:21 AM
Post#13


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


I think we are not communicating very well, and that makes getting to an answer harder.

You say the "Access" table has 20 records in it. Now, I want to know if that "Access" table is, in fact, the linked Oracle table. I think the answer should be "yes", but until we clarify, we're still not sure what the issue is.


Here's another thing to try. Write a Passthru query in Access to retrieve the records from that Oracle table. It should be able to use the same connection information as the linked table.

Can you return 20 records that way?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
shah1419
post Sep 29 2018, 08:30 AM
Post#14



Posts: 64
Joined: 10-January 13



Please Check the attached file

Attached File  Untitled.jpg ( 235.13K )Number of downloads: 5

This post has been edited by shah1419: Sep 29 2018, 08:30 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 29 2018, 08:40 AM
Post#15


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


Thank you.

Your LOCAL ACCESS table has records in it.

The Oracle table, Linked into this same accdb, has no records in it.

Now that is clear. You have TWO DIFFERENT tables.

Do you want to copy those records into the Oracle table?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 29 2018, 08:42 AM
Post#16


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


I asked if you want to copy the records from Access into Oracle, but the screen shot also shows two DIFFERENT tables, with different names and different fields. So, is there a table in the Oracle database that DOES match the one in your screenshot?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
shah1419
post Sep 29 2018, 09:02 AM
Post#17



Posts: 64
Joined: 10-January 13



i don't want to copy records. i want to update the oracle table when a user update access table.please see the table structure:

QUOTE
Checkinout -----table in Access
Userid number ;
checktime text
checktype text
sensorid number

Bil-INOUT -------table in Oracle

stuid number
intime text
otime text
dated date

please advised me.


Go to the top of the page
 
GroverParkGeorge
post Sep 29 2018, 10:45 AM
Post#18


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


There are two possible ways these needs to work.

ADD new records from the Access table to the Oracle table.

CHANGE existing records in the Oracle table when the corresponding records change in the Access table.

The first requires an APPEND query.

The second requires an UPDATE query.

I don't see how this is going to work with those two tables, though.

They have different fields.

Checkinout has a "checktime" but there is no field with the same name in BI-INOUT.

BI-INOUT has a field called "inTime" and a second field called "otime", but there is no field with either name in Checkinout.

What would the change to BI-INOUT look like? Some sample data would be useful here.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 29 2018, 11:40 AM
Post#19


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


Perhaps this will help future discussions.

A) Local Access tables are physically inside the accdb.
b) Linked Access tables are physically inside a different accdb, but linked to the current one.
c) Linked Server tables (Oracle or SQL Server, and others) are physically inside a remote Server-based database, but linked to the current one.

Attached File  locallinkedremote.jpg ( 14.31K )Number of downloads: 2


The little icons next to the table names tell you where the tables are. Linked tables have a small arrow pointing to them. Access tables are light blue squares. Remote server tables are green globes.

So, I asked if the table was LOCAL, or LINKED. You have both. Some tables are LOCAL Access tables. Some are Linked Server tables in Oracle.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Sep 29 2018, 11:52 AM
Post#20


UtterAccess VIP
Posts: 10,756
Joined: 10-February 04
From: South Charleston, WV


But if the oracle table is linked shouldn't it function same as an Access table?

--------------------
Robert Crouser
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 06:07 AM