Create foreign key in MS Access VBA

how to create a foreign key in access 2016
alter table add foreign key oracle
alter table add column with foreign key
ms access foreign key constraint
primary and foreign keys access
vba access create table
how to create relationships in access
ms access vba sql join

I have table tbl0 and table tbl1. tbl0 has a primary key made from field "Ticker" that I created like this:

Sub CreatPrimaryKey()
Dim db As Database
Set db = CurrentDb
db.Execute "CREATE INDEX TickerID ON tbl0 (Ticker) WITH PRIMARY;"
db.Close
End Sub

...which worked fine.

I confirm that I have the primary key by using this:

Sub GetPrimaryKeyField()
Call PrimKey("tbl0")
End Sub

Public Sub PrimKey(tblName As String)
'get primary key of tabel
'how to use: Call PrimKey("tbl_DatedModel_2015_0702_0")
'http://bytes.com/topic/access/answers/679509-finding-primary-key-using-vba
'*******************************************
'Purpose: Programatically determine a
' table's primary key
'Coded by: raskew
'Inputs: from Northwind's debug window:
' Call PrimKey("Products")
'Output: "ProductID"
'*******************************************

Dim db As Database
Dim td As TableDef
Dim idxLoop As Index

Set db = CurrentDb
Set td = db.TableDefs(tblName)
For Each idxLoop In td.Indexes
If idxLoop.Primary = True Then
Debug.Print Mid(idxLoop.Fields, 2)
Exit For
End If
Next idxLoop

db.Close
Set db = Nothing
End Sub

The immediate window prints "Ticker". I'm not sure what happened to "TickerID", but whatever. I get a PK. I then try to create a foreign key relationship between tbl0 and tbl1 by doing this:

Sub CreateForeignKey()Dim db As Database
Set db = CurrentDb

db.Execute "ALTER TABLE tbl1 " _
        & "ADD CONSTRAINT fk_tbl1_tbl0 " _
        & "FOREIGN KEY (Ticker) REFERENCES tbl0 (Ticker);"

    db.Close
End Sub

When I run the above sub I get error: "Invalid field definition "Ticker" in definition of index or relationship"

UPDATE: What makes this question different is part of the issue I was having was that I needed to have the same field in both tables when I AlTER TABLE.

Define relationships between tables using Access SQL, Like primary keys, you can define foreign keys in the table declaration by using When defining the relationships between tables, you must make the Have questions or feedback about Office VBA or this documentation? Like primary keys, you can define foreign keys in the table declaration by using the CONSTRAINT clause. There are essentially three types of relationships: One-to-one For every record in the primary table, there is one and only one record in the foreign table.

I think your sql should instead be this:

 ALTER TABLE tbl1 ADD CONSTRAINT fk_tbl1_tbl0 
FOREIGN KEY (TickerId) REFERENCES tbl0 (Ticker);

The parentId is tickerID and the child is ticker.

Relation object (DAO), The Microsoft Access database engine automatically creates an index with the Foreign property set to act as the foreign key in the referencing table. To create VB Copy. Sub RelationX() Dim dbsNorthwind As Database Dim� A relationship of two tables occurs through a primary key of a table. This primary key becomes the foreign key of another table. XI.2. What Is Primary Key. Primary Key is a key that you set on a field of the table to prohibit the duplicate value in the table. It also has the role to create the relationship between the tables.

Maybe old but I found this syntax to work You want to specify the FK before the PK, although I dont remember why its good pratice to include FKs in PK.

Sub CreateTableJours()
On Error Resume Next
    Application.CurrentDb.Execute "Drop Table [Jours];"
'---------------'---------------'
On Error GoTo onError
    Dim con As ADODB.Connection
    Set con = CurrentProject.Connection
    con.Execute "" _
    & "CREATE TABLE [Jours](" _
    & " [jcode]             COUNTER" _
    & ",[jdate]             DATETIME" _
    & ",[tid]               INTEGER" _
    & ",[cid]               INTEGER" _
    & ",[jdepense]          MONEY" _
    & ",[jjustificatif]     CHAR" _
    & ",CONSTRAINT [FK_Taches] FOREIGN KEY ([tid]) REFERENCES Personnes" _
    & ",CONSTRAINT [FK_Chantiers] FOREIGN KEY ([cid]) REFERENCES Chantiers" _
    & ",CONSTRAINT [PK_Jours] PRIMARY KEY ([jcode])" _
    & ");"
    Set con = Nothing
Exit Sub
'---------------'---------------'
onFail:
    myErrorLvl = 250
    Set con = Nothing
Exit Sub
'---------------'---------------'
onError:
    MsgBox Err.Description, , "Erreur(" & myErrorLvl & ") in " & "INIT.CreateTableJours" & "#" & Err.Number
    If myErrorLvl < 1 Then Resume onFail
End Sub

VBA SQL - FOREIGN KEY, Foreign keys create relationships between tables and help to ensure referential integrity. Referential integrity ensures that every record in a� There is an easy way to do this on a data entry form that doesn't require a subform at all. 1. Create a combo box or list box (both are essentially the same thing) that is bound to the Client_ID in the Invoice table (Foreign Key). 2.

Creating Primary Keys and Foreign Keys in Microsoft Access, This AutoNumber field will automatically create a unique number for each record as it is created, so that those records can be identified from the� Your Foreign Key SQL should be: ALTER TABLE tbl1 ADD CONSTRAINT fk_tbl1_tbl0 FOREIGN KEY (Ticker) REFERENCES tbl0 (Ticker); This assumes you have a field in tbl1 named "Ticker" that is the same type as tbl0.Ticker. The second line in this means the Foreign Key field you are creating references the related key field in the other table.

FOREIGN KEY Constraint - MS-Access Tutorial, This enforces referential integrity since a foreign key value in one table cannot exist if it does not already exist as a primary key in another table. In the Create� Need to create foreign key in MS Access If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

UtterAccess Forums > Create Foreign Key From A Vba Module?, Is it possible to create foreign keys from a VBA module just as you do able to create two tables with primary keys from a module in access and� Create a foreign key relationship in Table Designer Using SQL Server Management Studio In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design .

Comments
  • possible duplicate of ACCESS VBA - cannot create relations
  • Except that I am doing exactly what HansUp suggests in his answer (at least it seems like I am). I have seen that question/answer already. What am I missing?
  • Have you confirmed tbl1 includes a field named Ticker?
  • Hello HansUp! Don J. just touched on that. I'm sorting out how to add it.
  • I get "Invalid field definition "TickerID" in definition of index or relationship. I also tried "Ticker" instead of "TickerID" and got a similar error.
  • Take a look now. This explains what the SQL statement is doing and what parts are what. Decipher the SQL statement and apply using your actual field names. They may both be "Ticker"
  • Humm. So your saying I have to add the field "Ticker" to tbl1 before using your SQL, right? I'll try to figure out how to do that and come back here.... <head spinning>
  • Yes. A Foreign Key constraint is always created on existing fields. You have at least one field in the foreign table (the one the constraint is in) and at least one key in the related table (the one the primary key is in).
  • Out of curiosity, why are you doing this all in code? Is this something that is going to have be repeated and dynamic? If not, you might do well to use the GUI to create these. It will also help you understand them better.
  • I get "invalid field definition "TickerID" in definition of index or relationthip" when I try that.