Update Query with Oracle Table

[Guest]

Hi there,

I am trying to update an Access Table with information from an Oracle Table.
I have created the update query with a link between the two tables, but
every time I try to execute the query, I receive the "Operation Must Use an
Updateable Query" message.

Is there a way around this? Or is this process even possible when using two
different types of tables? I could update this information with VBA, but
just thought that building a quick update query might be an easier approach.

I've tried exporting the oracle table to an access table just to test the
query and it runs fine, but this isn't a practice which would be widely
accepted every time the query needs to run!

Not sure if this is a Jet issue or what. Could someone shed some light on
this when you have a minute?

Thanks for your help and knowledge!!

 

[Guest]

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

 

[Guest]

Thanks for the help Jerry.

My SQL is as follows:

UPDATE TBL_OUTPUT INNER JOIN TBL_HIERARCHY ON TBL_OUTPUT.CC_NUM =
TBL_HIERARCHY.CC_NUM SET TBL_OUTPUT.AREA = [LVL06_DESC], TBL_OUTPUT.FIELD =
[LVL07_DESC];

On tbl_hierarchy, there is no primary key identified (this is the oracle
table I mentioned before) and there is no relationship between the two tables.

Hopefully that helps!

TT

 

[Guest]

Unless TBL_HIERARCHY.CC_NUM is a primary key or unique constraint, you won't
be able to use your SQL as written. If TBL_HIERARCHY.CC_NUM has the same
value more than once, which one should be used for the update? That's one
reason why it's not updateable.

You probably need to gather the unique records in TBL_HIERARCHY in a
subquery then update TBL_OUTPUT.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks for the help Jerry.

My SQL is as follows:

UPDATE TBL_OUTPUT INNER JOIN TBL_HIERARCHY ON TBL_OUTPUT.CC_NUM =
TBL_HIERARCHY.CC_NUM SET TBL_OUTPUT.AREA = [LVL06_DESC], TBL_OUTPUT.FIELD =
[LVL07_DESC];

On tbl_hierarchy, there is no primary key identified (this is the oracle
table I mentioned before) and there is no relationship between the two tables.

Hopefully that helps!

TT

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.

 

[Guest]

Yeah...this is a strange one to me. The TBL_HIERARCHY.CC_NUM is a unique
field and there is no duplicates but the oracle table wasn't set up with it
as the primary key for some reason. I unfortunately, don't have access to
change the oracle tables so there really isn't anything I can do with regards
to the table structure.

It's also strange that when I dump the table to access, it doesn't create a
primary key either but the query works correctly.

I will try using a sub-query instead of dumping the table out and see if
that works for me.

Thanks for your time in trying to answer my question!

--
TT

Unless TBL_HIERARCHY.CC_NUM is a primary key or unique constraint, you won't
be able to use your SQL as written. If TBL_HIERARCHY.CC_NUM has the same
value more than once, which one should be used for the update? That's one
reason why it's not updateable.

You probably need to gather the unique records in TBL_HIERARCHY in a
subquery then update TBL_OUTPUT.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks for the help Jerry.

My SQL is as follows:

UPDATE TBL_OUTPUT INNER JOIN TBL_HIERARCHY ON TBL_OUTPUT.CC_NUM =
TBL_HIERARCHY.CC_NUM SET TBL_OUTPUT.AREA = [LVL06_DESC], TBL_OUTPUT.FIELD =
[LVL07_DESC];

On tbl_hierarchy, there is no primary key identified (this is the oracle
table I mentioned before) and there is no relationship between the two tables.

Hopefully that helps!

TT

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
Information on primary keys and relationships would be a nice touch too.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi there,

I am trying to update an Access Table with information from an Oracle Table.
I have created the update query with a link between the two tables, but
every time I try to execute the query, I receive the "Operation Must Use an
Updateable Query" message.

Is there a way around this? Or is this process even possible when using two
different types of tables? I could update this information with VBA, but
just thought that building a quick update query might be an easier approach.

I've tried exporting the oracle table to an access table just to test the
query and it runs fine, but this isn't a practice which would be widely
accepted every time the query needs to run!

Not sure if this is a Jet issue or what. Could someone shed some light on
this when you have a minute?

Thanks for your help and knowledge!!