Field properties for primary key and foreign key

  • Thread starter Thread starter Harold via AccessMonster.com
  • Start date Start date
H

Harold via AccessMonster.com

Do the field properties have to be the same for the primary key and the
foreign key? If I have long Interger on one does the other have to be the
same?
 
Harold said:
Do the field properties have to be the same for the primary key and the
foreign key? If I have long Interger on one does the other have to be the
same?
Click to expand...

Also, If I set the primary key to AutoNumber what should the foreign keys
field be? AutoNumber? Number? Text?

Thanks

Harold
 
The AutoNumber is a Long with the dbAutoIncrField flag set.
The matching foreign key should also be a Long.

Presumably you are creating this programmatically, so you will need
something like this:

Sub CreateTableDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Table1")

With tdf
'AutoNumber: Long with the attribute set.
Set fld = .CreateField("ContractorID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

'Another example: Text field: maximum 30 characters.
Set fld = .CreateField("Surname", dbText, 30)
.Fields.Append fld
End With

db.TableDefs.Append tdf
End Sub
 
Allen said:
The AutoNumber is a Long with the dbAutoIncrField flag set.
Click to expand...

Distinction: Jet's Counter must be incrementing INTEGER. Access's
AutoNumber can incrementing or random and any numeric data type incl
GUID.
 
Distinction: Jet's Counter must be incrementing INTEGER. Access's
AutoNumber can incrementing or random and any numeric data type incl
GUID.
Click to expand...

Not so. As you say, an AutoNumber can be incrementing or random, or it can
be a GUID. However, if it's incrementing or random, it can only be a Long
Integer.
 
Douglas said:
an AutoNumber can be incrementing or random, or it can
be a GUID. However, if it's incrementing or random, it can only be a Long
Integer.
Click to expand...

Hey you may be right :)

I remember creating a random currency automnumber in Access2003 but I
can't check now, they took 2003 away! If anyone has it, could they try
it out

If I remember wrong then sorry!
 
Back
Top