How to Map Many tables to One Access Form?

[hasfari]

Hi,

I am working with an exisitng system using many tables in ORACLE database. I am trying to keep our client MS-forms design not touched but have oracle tables (links) been accessed by forms.

Doing so would require having existing Access form fields mapped to more than one ORACLE table! However, behind the form there is only one control source for the form from where fields would drive their values from. Any idea how to keep such design (currently a form uses one Access table) so that I can upadte/insert/query ORACLE tables to one Access form. In other words, having fields comming from more than one ORACLE table displayed on one form. I am not been asked to create subform and neither I would look into this until I find no way out this problem. Access cannot update/insert to a query that joins more than one table.

 

[Quehay]

If you've got joins then you'd be asking for an ODBC update of parent and child from the same form--it's a lot to ask, and it sounds like ODBC won't do it.

If you want to write the ADO code in an unbound form, then you could do it but this requires a LOT of coding & testing.

 

[BSman]

Does it matter if you use subforms? You could create subform(s) on your form that would not look to the user like subforms, but would allow the direct updating of the Oracle tables. This assumes that there is only one matching record in each table when you perform the join. Of course, if that's the case, then the question is why you need multiple tables.

I'm taking a guess here, but if you have one table that contains the data to be updated and you are performing the join only to get the lookup values for code fields in that one table, then have the values looked up when the data is displayed. That way you only need one source table for the form.

 

[blarson0]

well, if you absolutely refuse to use subforms, then here is a work-around:

combine all the tables into one table using a make-table query. This way you can still update the table in the form, but you will update the new table and not oracle. To update oracle have an event like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
dim sqlst as string
sqlst = "INSERT INTO table1 (column1, column2...) VALUES ('" & [label1].value & "', '" & [label2].value & "'...);"
End Sub

sorry I threw that together at work, so if it doesn't make sence then just ask and I'll try to describe it a little better.

HTH!

-Brad

 

[blarson0]

heh you can tell I was in a hurry, I forgot the doCmd.runSQL statement... sorry about that.

Here is the code, revision 0.01 alpha ;-)

Private Sub Form_BeforeUpdate(Cancel As Integer)
dim sqlst as string
sqlst = "INSERT INTO table1 (column1, column2...) VALUES ('" & [label1].value & "', '" & [label2].value & "'...);"
doCmd.runSQL (sqlst)
End Sub

HTH!

-Brad

 

[hasfari]

Thanks floks,

I think all of the above are of great help. I will try looking into my Access DB relations, then if I still cannot update tables, I might have to combine ORACLE tables into on big Access table and follow blarson0's suggestion above.