unique record identifier

[Guest]

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?

 

[Stefan Hoffmann]

hi Raj,

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 <--

 

[Guest]

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]

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 <--

 

[Guest]

Hi Stefan,

Thankyou very much. Your answer is great.

raj