sql - Create foreign key in MS Access VBA -

i have table tbl0 , table tbl1. tbl0 has primary key made field "ticker" created this:

sub creatprimarykey() dim db database set db = currentdb db.execute "create index tickerid on tbl0 (ticker) primary;" db.close end sub 

...which worked fine.

i confirm have primary key using this:

sub getprimarykeyfield() call primkey("tbl0") end sub  public sub primkey(tblname string) 'get primary key of tabel 'how use: call primkey("tbl_datedmodel_2015_0702_0") 'http://bytes.com/topic/access/answers/679509-finding-primary-key-using-vba '******************************************* 'purpose: programatically determine ' table's primary key 'coded by: raskew 'inputs: northwind's debug window: ' call primkey("products") 'output: "productid" '*******************************************  dim db database dim td tabledef dim idxloop index  set db = currentdb set td = db.tabledefs(tblname) each idxloop in td.indexes if idxloop.primary = true debug.print mid(idxloop.fields, 2) exit end if next idxloop  db.close set db = nothing end sub 

the immediate window prints "ticker". i'm not sure happened "tickerid", whatever. pk. try create foreign key relationship between tbl0 , tbl1 doing this:

sub createforeignkey()dim db 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 run above sub error: "invalid field definition "ticker" in definition of index or relationship"

update: makes question different part of issue having needed have same field in both tables when alter table.

your initial sql statement creates index named "tickerid" on field "ticker." why debug statement returns "ticker" rather "tickerid."

your foreign key sql should be:

alter table tbl1 add constraint fk_tbl1_tbl0  foreign key (ticker) references tbl0 (ticker); 

this assumes have field in tbl1 named "ticker" same type tbl0.ticker.

the second line in means foreign key field creating references related key field in other table. read this: foreign key "ticker" in table altering (tbl1) references primary key "ticker" in related table "tbl0".

i use routine, may find helpful. make assumptions: 1) primary key named {table_name} + "id" , 2) foreign key named same thing. (both of these common practice , advisable in opinion).

public function createforeignkey( _         db dao.database, _         byval stable string, _         byval sprimarytable string, _         optional byval sfield string) boolean      dim ssql string     dim ssuffix string      on error goto eh      if sfield = ""         sfield = sprimarytable & "id"     else         ssuffix = "_" & sfield     end if      ssql = "alter table [" & stable & "]" _         & " add constraint fk_" & stable & "_" & sprimarytable & ssuffix _         & " foreign key([" & sfield & "])" _         & " references [" & sprimarytable & "] ([" & sprimarytable & "id]);"      db.execute ssql, dbfailonerror      createforeignkey = true      exit function eh:      msgbox "error " & err.number & vbcrlf _         & " (" & err.source & vbcrlf _         & " (" & err.description _         & ") in procedure createforeignkey of module database"  end function 

references:

• constraint clause (microsoft access sql)
• index clause (microsoft access sql)