Home » Developer & Programmer » Forms » How to use forms with a hetrogeneous DB Link (Oracle 10G, Windows Xp)
How to use forms with a hetrogeneous DB Link [message #323370] Wed, 28 May 2008 05:45 Go to next message
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 ????
Re: How to use forms with a hetrogeneous DB Link [message #323405 is a reply to message #323370] Wed, 28 May 2008 07:10 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #323449 is a reply to message #323405] Wed, 28 May 2008 08:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #323621 is a reply to message #323549] Thu, 29 May 2008 00:51 Go to previous messageGo to next message
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.


Re: How to use forms with a hetrogeneous DB Link [message #323644 is a reply to message #323621] Thu, 29 May 2008 02:12 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, there's nothing here?

./fa/4362/0/
Re: How to use forms with a hetrogeneous DB Link [message #323672 is a reply to message #323644] Thu, 29 May 2008 03:40 Go to previous messageGo to next message
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 137 times)
Re: How to use forms with a hetrogeneous DB Link [message #323680 is a reply to message #323672] Thu, 29 May 2008 03:54 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #323691 is a reply to message #323370] Thu, 29 May 2008 04:36 Go to previous messageGo to next message
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

Re: How to use forms with a hetrogeneous DB Link [message #323721 is a reply to message #323691] Thu, 29 May 2008 05:50 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #323744 is a reply to message #323721] Thu, 29 May 2008 06:20 Go to previous messageGo to next message
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
Re: How to use forms with a hetrogeneous DB Link [message #323752 is a reply to message #323744] Thu, 29 May 2008 06:30 Go to previous messageGo to next message
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.
----------------
Re: How to use forms with a hetrogeneous DB Link [message #323765 is a reply to message #323752] Thu, 29 May 2008 07:01 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #323767 is a reply to message #323765] Thu, 29 May 2008 07:12 Go to previous messageGo to next message
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.
Re: How to use forms with a hetrogeneous DB Link [message #323771 is a reply to message #323767] Thu, 29 May 2008 07:21 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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;
Re: How to use forms with a hetrogeneous DB Link [message #323772 is a reply to message #323767] Thu, 29 May 2008 07:24 Go to previous messageGo to next message
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../fa/4366/0/
  • Attachment: untitled.GIF
    (Size: 99.53KB, Downloaded 772 times)
Re: How to use forms with a hetrogeneous DB Link [message #323773 is a reply to message #323772] Thu, 29 May 2008 07:29 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #323783 is a reply to message #323773] Thu, 29 May 2008 07:50 Go to previous messageGo to next message
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'
Re: How to use forms with a hetrogeneous DB Link [message #324046 is a reply to message #323783] Fri, 30 May 2008 07:03 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.

./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!.

Re: How to use forms with a hetrogeneous DB Link [message #324250 is a reply to message #324046] Sun, 01 June 2008 01:29 Go to previous messageGo to next message
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
Re: How to use forms with a hetrogeneous DB Link [message #324259 is a reply to message #324250] Sun, 01 June 2008 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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:

./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.
Re: How to use forms with a hetrogeneous DB Link [message #324261 is a reply to message #324259] Sun, 01 June 2008 04:32 Go to previous messageGo to next message
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??
Re: How to use forms with a hetrogeneous DB Link [message #324263 is a reply to message #324261] Sun, 01 June 2008 04:43 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #324269 is a reply to message #324263] Sun, 01 June 2008 04:57 Go to previous messageGo to next message
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
Re: How to use forms with a hetrogeneous DB Link [message #324270 is a reply to message #324269] Sun, 01 June 2008 05:06 Go to previous messageGo to next message
esraa90
Messages: 63
Registered: May 2008
Location: EGYPT
Member

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

Re: How to use forms with a hetrogeneous DB Link [message #324288 is a reply to message #324270] Sun, 01 June 2008 09:41 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: How to use forms with a hetrogeneous DB Link [message #324341 is a reply to message #324288] Mon, 02 June 2008 01:40 Go to previous messageGo to next message
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.
Re: How to use forms with a hetrogeneous DB Link [message #335379 is a reply to message #324341] Tue, 22 July 2008 00:46 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Is it working now? What code do you have in 'on-update'?

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


Current Time: Tue Dec 06 12:36:00 CST 2016

Total time taken to generate the page: 0.10338 seconds