OraFAQ Forum: Forms » How to use forms with a hetrogeneous DB Link

Home » Developer & Programmer » Forms » How to use forms with a hetrogeneous DB Link (Oracle 10G, Windows Xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to use forms with a hetrogeneous DB Link [message #323370]

Wed, 28 May 2008 05:45

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Dear Sir,

I finished creating DB Link from my Oracle database to MS-Access database through Hetrogeneous conectivity.

I could read MS-access tables and also update them form Oracle DB.
SQL> select ........

My database link is access_db
so my command is:
SQL> select * from table1@access_db;

---------------------------------------------
how could I connet with oracle forms and create datablock to MS-access ????

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323405 is a reply to message #323370]

Wed, 28 May 2008 07:10

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Create a view in your schema as

CREATE VIEW v_table1 AS
  SELECT * FROM table1@access_db;

Base form data block on view "v_table1" instead of a table in MS Access database.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323449 is a reply to message #323405]

Wed, 28 May 2008 08:46

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

I already did.

I created view based on ....@access_db

I created form block based on that view.

When running the form nothing displayed.
error appear at th bottom of form FRM-40505
in help means :
The table associated with the current block of the form might not exist, or your username might not have authority to perform the specified action on the table.

When selecting rows from view using sql I find records.
SQL> select .....

What is wrong???

[EDITED by LF: removed [quote] tags]

[Updated on: Wed, 28 May 2008 15:14] by Moderator

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323549 is a reply to message #323449]

Wed, 28 May 2008 15:19

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Go to "Help" menu and select "Display Error"; it might reveal some additional information; please, post it back here.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323621 is a reply to message #323549]

Thu, 29 May 2008 00:51

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

As I explained before:

When running the form nothing displayed.
error appear at the bottom of form FRM-40505
in help means :
The table associated with the current block of the form might not exist, or your username might not have authority to perform the specified action on the table.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323644 is a reply to message #323621]

Thu, 29 May 2008 02:12

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In other words, there's nothing here?

/forum/fa/4362/0/

/forum/fa/4362/0/

Attachment: display_error.PNG
(Size: 1.85KB, Downloaded 2051 times)

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323672 is a reply to message #323644]

Thu, 29 May 2008 03:40

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Thanks for your help in advance.

The attachment shows the error

Attachment: error_frm.gif
(Size: 99.89KB, Downloaded 686 times)

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323680 is a reply to message #323672]

Thu, 29 May 2008 03:54

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is what Oracle says:

Quote:

ORA-02070: database stringstring does not support string in this context

Cause: The remote database does not support the named capability in the context in which it is used.

Action: Simplify the SQL statement.

Could you post "CREATE VIEW" statement and result of the "DESC view_name" result (I'm interested in column datatypes)? Also, you might want to check this Ask Tom discussion.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323691 is a reply to message #323370]

Thu, 29 May 2008 04:36

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

connect sys/sys as sysdba
SQL> select view_name ,owner from all_views where owner = 'EMPLOYMENT'
2 /

VIEW_NAME OWNER
------------------------------ ------------------------------
ORA_ATTENDANCE EMPLOYMENT
VACANCY EMPLOYMENT
INDEX_BY_TYPES EMPLOYMENT

SQL> select text from all_views where view_name = 'ORA_ATTENDANCE';

TEXT
--------------------------------------------------------------------------------
select ID,USERID,CHECKTIME from my_attendance@access_db

-----------------------------------------------------------------
After connect to oracle schema 'employment':
SQL> desc ora_attendance;
Name
---------------------------------------
ID
USERID
CHECKTIME

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323721 is a reply to message #323691]

Thu, 29 May 2008 05:50

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I was expecting something like this (note column datatypes):

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------

 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL>

What is "checktime"? In Oracle, I suppose that DATE datatype would be fine. What is MS Access datatype of this column? Are they compatible?
I guess that both "ID" columns are numbers (are they?).

So, just for testing, try to recreate the view, but this time omit "checktime" column. Form is to be slightly modified - just remove "checktime" item's "Database" property to "No", recompile the form (Ctrl + Shift + K) and run it once again.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323744 is a reply to message #323721]

Thu, 29 May 2008 06:20

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

SQL> desc ora_attendance;
Name Null? Type
----------------------------------------- -------- -------------------------
ID NOT NULL NUMBER(10)
USERID NUMBER(10)
CHECKTIME DATE

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323752 is a reply to message #323744]

Thu, 29 May 2008 06:30

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

I created the view again with ID,USERID only.

Also delete CHKTIME from datablock in form.

Compiled the form.

Same problem during runtim.

----------------
Kindly, note that the problem in ROWID.

data type for CHKTIME in access is Date/Time.
----------------

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323765 is a reply to message #323752]

Thu, 29 May 2008 07:01

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Obviously, I was wrong; I *know* the error mentions ROWID. It might be because MS Access rowid is different from Oracle's one (is it? I don't know). As ROWID represents record's physical location, hm, maybe Forms can't interpret MS Access ROWID correctly.

Unfortunately, I'm out of idea.

Though, two more which are probably useless, but ... until you find a better answer, you might try with these: how about creating a view based on a view? First one is already created, and it select data from MS Access table via database link. Now, create another view as SELECT * FROM the_first_view, and base form data block on this, new view.

Or, would it be possible to create a materialized view instead of a view? And use it as a data source in a form?

I'm sorry, but I can't test it myself; if I could, I'd probably shut up much earlier.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323767 is a reply to message #323765]

Thu, 29 May 2008 07:12

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Anyway thakyou for your help.

I will try to build another view on the first one,
and then buld my datablock on second view.

About materialized view, I know it from my study I never used it.

Could you please give me the command.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323771 is a reply to message #323767]

Thu, 29 May 2008 07:21

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As opposed to an "ordinary" view (which is just a stored SELECT statement, so it doesn't contain data), materialized view contains data. That's why I thought that it *might* do some good as everything would be stored in an Oracle database.

The simplest syntax is

CREATE MATERIALIZED VIEW mv_att AS
  SELECT id, userid, checktime
  FROM ora_attendance;

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323772 is a reply to message #323767]

Thu, 29 May 2008 07:24

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

I tried creating second view ora_atendance2,
built the datablock on it.

Same problem during runtime.
-----
About ROWID in Ms-access DB I don't about it, or where I could find it to know its type.
All what I have in MS-acces displayed in the attachment. /forum/fa/4366/0/

/forum/fa/4366/0/

Attachment: untitled.GIF
(Size: 99.53KB, Downloaded 2160 times)

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323773 is a reply to message #323772]

Thu, 29 May 2008 07:29

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'll try to follow your steps (connect Oracle and MS Access, database link, view, form) and see whether I'll manage to find the solution. Until then, I'm terribly sorry, but I can't help much. Hopefully, someone else will know the answer.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #323783 is a reply to message #323773]

Thu, 29 May 2008 07:50

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Kindly, if you know any material about tis topic, send it to me.

Give me the link for 'How to use forms with a hetrogeneous DB Link'

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324046 is a reply to message #323783]

Fri, 30 May 2008 07:03

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Hm, OK, here it is: I've created a view in Oracle schema based on MS Access table. I used this view as data block source. Set
- Key Mode block property = UPDATEABLE
- Primary key property (ID item) = YES
- Column name property (all items): enclose column names into double quotes ("id", "userid", "checktime")

Run the form!

I *believe* it should work; at least, it did for me.

/forum/fa/4372/0/

/forum/fa/4372/0/

Now, I'm smarter than yesterday, but my problem is that I forget things too soon. Maarten has described the whole walkthrough of connecting MS Access and Oracle in this thread; the solution of your problem is described in step #8. You'll probably see that I was overwhelmed when I saw his post, but - as I've said - I forgot what I saw. Too bad for me.

Sorry for wasting your time yesterday; but OK, that's the price. OraFAQ Forum Guide suggests several ways of finding the answer to your questions, and one of them is Search the board!.

Attachment: access_oracle.PNG
(Size: 27.09KB, Downloaded 2116 times)

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324250 is a reply to message #324046]

Sun, 01 June 2008 01:29

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Thank you Sir,

Really It works on my machine.

After I made chnges you mentioned.

Thank You ..... Thank You ..... Thank You .....

Is Their any way to update that MS-Access table using Oracle Forms??

Best Regards
& Thank you

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324259 is a reply to message #324250]

Sun, 01 June 2008 04:13

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I tried to update a record, but there was an error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][A055] The column <column name> is not updateable.

The solution, according to Metalink note 276284.1, is to enable the option to allow updateable cursors in the ODBC-Driver/Client-Access.

That's theory. But, where can I enable that option? This is the ODBC driver's Advanced Options window:

/forum/fa/4380/0/

/forum/fa/4380/0/

However, there is not "updateable cursors" there. Google wasn't helpful (or, more precisely, I was unable to find the answer). So, if someone knows how to do that, please, share the knowledge.

Attachment: odbc_adv_options.PNG
(Size: 16.30KB, Downloaded 2216 times)

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324261 is a reply to message #324259]

Sun, 01 June 2008 04:32

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

I don't think so.

I could update MS-Access table using Oracle SQL>
SQL> update table .....@access_db

access_db is my database link.
-----------------------------------------
Now, when we go to forms.

I want to connect to user/password@access_db to create a data block on that table not on a view.
This is what I am asking about .
What will be the user name and password??

Or what else??

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324263 is a reply to message #324261]

Sun, 01 June 2008 04:43

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As we can't base data block on a table in MS Access, we have to use a database link and access this table via the link. So, you might try with a SYNONYM (instead of a VIEW, which you use now) and base form data block on this synonym.

I have tried, and - as I've said in my previous post - UPDATE won't work. You may try it yourself; do post your experience.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324269 is a reply to message #324263]

Sun, 01 June 2008 04:57

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Sure, I already created a synonym for MS-access table@access_db

Then, I created my forms datablock on that synonym.

Chnaged the proberties you mentioned before.

Then I ran the form.

It displays data.

When trying to change something I got :
40501 Oracle error.
Unable to reserve record for update or delete .

I think the problem is another proberty should be changed not more.

Best Regards

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324270 is a reply to message #324269]

Sun, 01 June 2008 05:06

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Note, I could insert records using that synonym into Ms-Access.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324288 is a reply to message #324270]

Sun, 01 June 2008 09:41

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SELECT, INSERT and DELETE work fine. It is UPDATE that causes problems. Re-read message #324259.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #324341 is a reply to message #324288]

Mon, 02 June 2008 01:40

esraa90
Messages: 63
Registered: May 2008
Location: EGYPT

Member

Then it's a bug.

How could database update records in access and forms could not???

Also, I,ve same problem in delete.

Report message to a moderator

Re: How to use forms with a hetrogeneous DB Link [message #335379 is a reply to message #324341]

Tue, 22 July 2008 00:46

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Is it working now? What code do you have in 'on-update'?

David

Report message to a moderator

Previous Topic:	frm-92101 when want to open the form
Next Topic:	Date Format Problem Using Oracle Calendar Control

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 21 10:30:04 CDT 2025