I am trying to link oracle tables from an ODBC database to access in VBA
code. I have a small problem though, some of the tables do not have a primary
key and I keep getting a prompt to "select a Unique Record Identifier". Is
there a way to programmatically select all the fields?

hi Raj,

raj wrote:
> I am trying to link oracle tables from an ODBC database to access in VBA
> code. I have a small problem though, some of the tables do not have a primary
> key and I keep getting a prompt to "select a Unique Record Identifier".
This should never be the case, as it is very poor database design.

> Is there a way to programmatically select all the fields?
Use

With CurrentDb
.TableDefs.Append _
.CreateTableDef(..)
.TableDefs.Requery
End With

to create your linked table without prompting. Then you can use
..TableDefs("yourTable").Fields to enumerate all your fields.


mfG
--> stefan <--

Hi Stefan,

Thanks for your reply.

I tried your suggetion.

With CurrentDb
.TableDefs.Append .CreateTableDef("LOAN", dbAttachSavePWD, .....)
.TableDefs.Refresh
For I = 0 To .TableDefs("LOAN").Fields.count - 1
.TableDefs("LOAN").Fields.Append (.TableDefs("LOAN").Fields)
Next I
end with

But i get error 'argument not optional' for 'Fields'. Please let me know how
to loop through and enumerate all the fields.

Also .tabledefs.requery does not work, i have only 4 options in .tabledefs,
they are
Append, Count, Delete, Refresh.

thanks in advance, raj

"Stefan Hoffmann" wrote:

> hi Raj,
>
> raj wrote:
> > I am trying to link oracle tables from an ODBC database to access in VBA
> > code. I have a small problem though, some of the tables do not have a primary
> > key and I keep getting a prompt to "select a Unique Record Identifier".
> This should never be the case, as it is very poor database design.
>
> > Is there a way to programmatically select all the fields?
> Use
>
> With CurrentDb
> .TableDefs.Append _
> .CreateTableDef(..)
> .TableDefs.Requery
> End With
>
> to create your linked table without prompting. Then you can use
> ..TableDefs("yourTable").Fields to enumerate all your fields.
>
>
> mfG
> --> stefan <--
>

hi Raj,

Dim Fields As String
> With CurrentDb
> .TableDefs.Append .CreateTableDef("LOAN", dbAttachSavePWD, .....)
> .TableDefs.Refresh
Fields = ""
> For I = 0 To .TableDefs("LOAN").Fields.count - 1
Fields = Fields & .TableDefs("LOAN").Fields.Item(I).Name & ",
"
> Next I
Execute "CREATE INDEX pk_LOAN ON LOAN(" & Fields & _
") WITH PRIMARY;"
> end with


mfG
--> stefan <--

Hi Stefan,

Thankyou very much. Your answer is great.

raj

"Stefan Hoffmann" wrote:

> hi Raj,
>
> Dim Fields As String
> > With CurrentDb
> > .TableDefs.Append .CreateTableDef("LOAN", dbAttachSavePWD, .....)
> > .TableDefs.Refresh
> Fields = ""
> > For I = 0 To .TableDefs("LOAN").Fields.count - 1
> Fields = Fields & .TableDefs("LOAN").Fields.Item(I).Name & ",
> "
> > Next I
> Execute "CREATE INDEX pk_LOAN ON LOAN(" & Fields & _
> ") WITH PRIMARY;"
> > end with
>
>
> mfG
> --> stefan <--
>