unique record identifier

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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".
Click to expand...
This should never be the case, as it is very poor database design.
Is there a way to programmatically select all the fields?
Click to expand...
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
 
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
Click to expand...
Fields = Fields & .TableDefs("LOAN").Fields.Item(I).Name & ",
"
Next I
Click to expand...
Execute "CREATE INDEX pk_LOAN ON LOAN(" & Fields & _
") WITH PRIMARY;"
end with
Click to expand...


mfG
--> stefan <--
 
Back
Top