Bound form w/Linked Oracle table

[iamareplicant]

Experts,

I have an Access 2003 app that utilizes linked Oracle tables.

I have multiple pass-thru queries, sequences set up, all is well.


At the moment, I am developing unbound forms. A lot more work, but they work and I have complete control of the data as I have set up the app so far as a 2-tier app.

I would like to explore, though, binding the forms to the tables.

The problem:

In an Access form bound to an Access table with Autonumber, using docmd.gotorecord acNewRec is no problem as Access takes care of creating a new record and realizes that, even if there are constraints (NOT NULL in some fields, etc), it will allow you to create a new record and then save it.

But with my Oracle tables, when I try goto asNewRec, I of course get an error because Oracle will not let a new record be created until all of the fields (with constraints) are populated.

So, I cannot use goto acNewRec, period, unless I find a work-around to being able to get the Oracle sequence, insert it into a new record, but not error out because Oracle throws the error that ya cant create a record with all of the NULL values, etc.

Hope I am making sense...

So, what is the work around for a bound 2003 MS Access form that is bound to an Oracle table? I know that I could create a temp table, bind the form to that, then append to and from this table to the Oracle table i am trying to bind to, but this wouldnt really be what i want - a form bound directly to an Oracle table.

TIA,

 

[Pampers]

Not sure, but maybe use default values for the fields with the constraints?

Pampers
Keeping it simple can be complicated

 

[iamareplicant]

Yes, i thought of the same thing. Populate the record right off the bat with temp values, then have them "updated" as the user enters vals.

But, those temps would show up in the controls, etc. And the only way my pea brain could work around THIS is....well, back to an unbound form.

'Preciate the reply....i am still pondering this issues, and i am still coding the app as a 2 tier app.

Any other thoughts on this issue would be cool. I am hoping a reply will come back that, dude, if you are using linked Oracle tables and want to have bound forms, you either have to do a lot of gymnastics to get the forms to work as they would bound to a native Access table, or do what you are doing now (unbound).

Ultimately, I am hoping i am not coding all of the (unbound) code needed to get this app working as it needs for nothing.

If there really is a way to bind a form to an Oracle table AND have it work as it would as though it were bound to a native Access table, it would be great to know now...