Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

creating key fields in remote tables

In Access 2K, I am creating tables in code.  The tables reside in the 'back end' database.  I have no trouble creating or attaching the tables.  I am having trouble creating the primary indexes.  The error I get is "Can't create index in linked tables."

I am creating the fields using the .createFields construct.  Two questions:  How do I specify an autonumber type (there is no dbAutoNumber constant), and how do I create a two field primary key in a remote db?

Thanks

Rick
0
rtstannard
Asked:
rtstannard
1 Solution
 
PaurthsCommented:
hi rtstannard,

here is an example.
This will create the following table;
lngID --> autonumber
lngOther --> numeric
ATextField --> text
AMemoField --> memo

joinindex --> indexname for these fields: lngID and lngOther, and a two-field-primary key


Dim dbs As DAO.Database
Dim tdf As TableDef
Dim fld As Field
Dim idxTwoFields As Index

 Set dbs = CurrentDb
 Set tdf = dbs.CreateTableDef("Mytable")
 
 Set fld = tdf.CreateField("lngID", dbLong)
 fld.Attributes = fld.Attributes + dbAutoIncrField
 
 tdf.Fields.Append fld
 Set fld = tdf.CreateField("lngOther", dbLong)
 tdf.Fields.Append fld
 Set fld = tdf.CreateField("ATextField", dbText)
 tdf.Fields.Append fld
 Set fld = tdf.CreateField("AMemoField", dbMemo)
 tdf.Fields.Append fld
 
 Set idxTwoFields = tdf.CreateIndex("JoinIndex")

    With idxTwoFields
        .Fields.Append .CreateField("lngID")
        .Fields.Append .CreateField("lngOther")
        .Primary = True
    End With
    tdf.Indexes.Append idxTwoFields

 dbs.TableDefs.Append tdf
 
 Set dbs = Nothing


cheers
Ricky
0
 
rtstannardAuthor Commented:
Spot on, Ricky, good work.

Rick
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now