Create relationship for table created on the fly

[Tadynn]

Hi all,


Access 2K

I've written the sub below to create a temporary table when I open a form.
Although I create the table okay, I haven't figure out how to also programatically create a one to many relationship from a permanent table (tbl_Manifest) to this temp table (tmp_ManifestDetail). I want to do this because I want to automatically insert the Manifest Number from the table tbl_Manifest into each new line of data that is keyed into tmp_ManifestDetail.

To add. I will also need to delete the relationship once the temp table is dropped, this I will part I will add as part of my error handling.


Below is my code that I use to create the table and to execute the code:

Sub CreateTable(TableName)
On Error GoTo Err_CreatTable_Click

Dim DB As DAO.Database, T As DAO.TableDef, F As DAO.Field
Set DB = CurrentDb
Set T = DB.CreateTableDef(TableName)

Set F = T.CreateField("ManifestNo", dbLong)
T.Fields.Append F
Set F = T.CreateField("LineNo", dbLong)
F.Attributes = dbAutoIncrField
T.Fields.Append F
Set F = T.CreateField("ProductCode", dbLong)
T.Fields.Append F
Set F = T.CreateField("ProductName", dbText)
T.Fields.Append F
Set F = T.CreateField("Qty", dbLong)
T.Fields.Append F
Set F = T.CreateField("CreateDate", dbDate)
T.Fields.Append F
Set F = T.CreateField("ConfirmDate", dbDate)
T.Fields.Append F

DB.TableDefs.Append T

Exit_CreatTable_Click:
Exit Sub

Err_CreatTable_Click:

Select Case Err.Number

Case "3010" 'if table exists, delete it and start again
DoCmd.DeleteObject acTable, "tmp_ManifestDetail"

Form_Load

Case Else

MsgBox Err.Number & " " & Err.Description
Resume Exit_CreatTable_Click

End Select

End Sub
------------------------------------------------------------
Private Sub Form_Load()

CreateTable ("tmp_ManifestDetail") 'Run CreateTable Sub

End Sub
------------------------------------------------------------

Thanks in advance, tadynn

 

[willir]

Need a bit more code. Snippets only...

Dim R as Relation

'assuming related table ProductTbl and key is ProductCode
'setup the relation
set r = db.CreateRelation ("MyRelation","Tablename","ProductTbl")

'set the type of relation you want
Set R.Attributes = dbRelationUpdateCascade
'reference table key
Set F = R.CreateField("ProductCode")
    
'then foreign key
F.ForeignName = "ProductCode"
    
'append to field collection
R.Fields.Append F
    
'append to relation collection
DB.Relations.Append R

Verify with the relation graphical tool view. Your coding is pretty good so I suspect you can take it from here.

Richard

 

[Tadynn]

Hi Willir

Thanks for your input. I've actually just tried another way of doing it and it worked.

What I did was I created another main and subform with the relationship intact and then deleted the relationship in the relationship window. Because I created the forms while the relationship was intact, the master and child linking was created. Then when I took the link away, the linking remained. Which so far works well.

Rgrds, tadynn

 

[willir]

That is what I love about programming -- more than one way to get the job done. And as we get better, we find better ways of getting it done.