Re: Field Order via Oracle ODBC

Brad

With respect, that is a truly a w f u l convention!

To the best of my knowledge, the relational database model says nothing about the order in which field definitions are returned for a table. Thus, different products can return those definitions in any order(s) that they want. Even with a single product, the order might change in the next version. IMO, there is NO "correct order" in which field definitions are GUARANTEED to be returned.

You need to identify the primary key explicitly, using a method appropriate to ach particular database. For example, in Oracle, primary key constraints are identified in various data dictionary views, and you can use those views to determine the primary key of a table without resort to any conventions or subterfuge.

Have a chat with your Oracle DBA.

Cheers,
TC
(No email)

Brad Pybus <brad_pybus_nospam_at_hotmail.com> wrote in article <GgY63.825$VN.6865_at_client.news.psi.net>...
> Here is my situation. I use SQL server to as a back end to MSAccess as a
> front end. When I open tables with MSAccess, the field order directly
> corresponds to the order of the fields in the original SQL server create
> table statement. We had a convention where the first field was ALWAYS
the
> primary key for the table. The code we have in MSAccess works based on
this
> convention. In other words we do things like:
>
> fldPrimaryKey = MyTableDef.Fields(0)
>
> But now that we have ported the database over to Oracle and connected to
> Access via the Oracle ODBC driver, when I open a table in Access, the
field
> order does not correspond to the field order in the original create table
> statement.
>
> Is this the Oracle ODBC driver's fault?? Is there any way to get it to
> transmit the fields in the correct order?
>
> We already have one fix for this problem:
>
> ' Where GetPrimaryKeyField() iterates through the indexes
> ' collection to find the primary key field
> fldPrimaryKey = GetPrimaryKeyField(MyTableDef)
>
> -----------------------------------
> We would rather figure out how to get the ODBC driver to transmit the
fields
> in the correct order. Any suggestions?
>
>
> Thanks,
> Brad
>
> --
> To reply to me, remove the "_nospam" from my address.
>
>
>
Received on Tue Jun 08 1999 - 08:36:15 CDT