Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / July 2005

Tip: Looking for answers? Try searching our database.

Field properties for primary key and foreign key

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Harold - 18 Jul 2005 04:37 GMT
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 - 18 Jul 2005 05:01 GMT
>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?

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

Thanks

Harold
Allen Browne - 18 Jul 2005 05:23 GMT
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

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>>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
[quoted text clipped - 6 lines]
>
> Harold
peregenem@jetemail.net - 18 Jul 2005 10:33 GMT
> The AutoNumber is a Long with the dbAutoIncrField flag set.

Distinction: Jet's Counter must be incrementing INTEGER. Access's
AutoNumber can incrementing or random and any numeric data type incl
GUID.
Douglas J. Steele - 18 Jul 2005 23:17 GMT
>> The AutoNumber is a Long with the dbAutoIncrField flag set.
>
> Distinction: Jet's Counter must be incrementing INTEGER. Access's
> AutoNumber can incrementing or random and any numeric data type incl
> GUID.

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


peregenem@jetemail.net - 19 Jul 2005 10:42 GMT
> 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.

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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.