Access linked tables to Oracle - Relationships 1

[TimBiesiek]

Hi all,

We have received an Access database from a company to use within our company. It used to have an Access front and back end, but we have moved the back-end into Oracle, to comply with various procedures and compliancy regulations.

Now, the relationships no longer show, and the forms don't seem to show the data correctly. I'm assuming this is due to the subforms not having the correct relationships to be able to retrieve the data.

I have tried adding the relationships in the Access front end, but they are never saved between the Oracle tables...

Anyone ahve any ideas?

Cheers!

 

[dhookom]

Your issue seems to be "the forms don't seem to show the data correctly". We don't have any clue regarding this statement. Relationships are a back-end feature. It shouldn't make any difference what is defined in the front end.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimBiesiek]

Ok...

The main form has a sub form that changes depending on what is selected on the main form. The sub form can then have the details amended and save clicked to update the data, but it shows an error stating that the ID (Normally gained from the main form) cannot be null... I assumed it was because the orignal relationships were non-existent in the new tables.

E.g.
Main form - A person is selected
Sub form - Is meant to show the person's details (First name, surname, DOB, department etc etc).
However, this info isn't shown on the sub form - Fields are blank. If the user tries to update this, when they save a message appears stating 'Run-time error 3155. ODBC -- Insert on a linked table 'tblpersons' failed. Cannot insert null into "tblpersons"."personID

 

[TimBiesiek]

Further to this, the code that is used to update the data is simply "DoCmd.RunCommand acCmdSaveRecord".

I may have to recode this bit to gt it to work....

 

[dhookom]

Is the main form bound to a table? How is "A person is selected"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimBiesiek]

Nope, the main form isn't bound... Neither is the sub form, now that you mention it...

The main form has a tree view control which shows firstly the company, then the asset, then the team, and then the person, all in hierachial order. When a person is clicked on, this populates the sub form...

It may just be that it's a data issue...

 

[PHV]

You said
the code that is used to update the data is simply "DoCmd.RunCommand acCmdSaveRecord"
and now:
the main form isn't bound... Neither is the sub form
Some discrepancy here ...

 

[TimBiesiek]

Hmm, sorry... Sub form IS bound... my mistake... The query for the subform has a where clause:

WHERE ((([tblPersons].[PersonID])=Left(Right([Forms]![frmMainForm]![TreeView].SelectedItem.Key,8),4)));

I'm thinking it will be something to do with this...

 

[dhookom]

Is there a default value in the subform for PersonID?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimBiesiek]

Heh heh... nope. Found it... It's coz the where is using a "left(right" statement, limiting it to only 4 characters, but some of the IDs are more than 4 characters.... Man I love debugging systems that others have designed!!! =P

Thanks anyway! You were helpful in sorting this out!

 

[PHV]

You aren't prompted for a parameter with such WHERE clause in your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886