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.

 

[MMSantiago]

Generally, when you need to get information from more than one table, you would create a QUERY that lists all the fields from all the tables(or links, in your case). Then you make that query your CONTROL SOURCE within the FORM. The query becomes your single point of contact for the information in the form. If your form contains all the filtering code for getting the specific information you need, you shouldn't have to specify any kind of special instructions in the query itself other than the field names themselves.

If you need to run different queries on the data, you can copy the original query and form and then alter the new query to suit your needs. Hope this helps.

 

[SBendBuckeye]

Also, how you set up the SQL can impact whether the result query is updateable or not. If you use the older style SQL such as Where tbl1.field = tbl2.field, I believe that makes it a read only query, whereas if you use inner and outer join types it is updateable.

I'm not sure this is entirely accurate, but I read something about that somewhere so it may be something to look into if you are getting read only record sets for your queries.

Someone more knowledgeable, please expand on this.

 

[thornmastr]

Consider using unbound forms and defining a class structure mapped to your form. That way, all your I/O and your element manipulation becomes a part of your class and is independent of your application code. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[hasfari]

Thanks for your input.

But as mentioned by Mr. SBendBuckeye, I have to update ORACLE tables from one form. I have to have at least two = sign between two tables and I could not make that query updateable. Since the old Access daatbase use to have one table for most of the system, now it is been normalized into about six tables. I need though to make at least two = sign (join) between these tables. So, is there away.

Thanks
Asfari

 

[MMSantiago]

In terms of the '= sign', you could try creating relationships between the tables within Access, even though they are external tables. I've done something similar with Excel spreadsheets as source data and the final result was not read-only. If not, you may be able to create an Access table with the common fields and link from the external tables to that 'internal' table. Either way, once you are running the form through a query, the fact that your source is ORACLE should only affect data type and not your ability to update.

If neither works, then I would agree that you'd have to work through an unbound form, since that would be the only way to input/update information without creating a read-only situation. I haven't worked with 'class structures', however, so I couldn't advise you on how to accomplish it.