Table Many to Many Relationship, How to Insert Records using sql

[Maggi]

I am new to this forum and to access. Here I have a challenging situation that I am not able to solve and I know you can solve in a couple of minutes. Here is how I have my access database set up. This is a shipping / packaging database I am working on. I have three tables connected by one to many and many to many relationship as shown here.

Table 1
Sales Order Line--------- Primary Key

Table2
BoxNum, Sales Order Line --------- Primary Key

BoxItemDataTable
itemIDFK - Foreign Key for Table 2
BoxIDFK - Foreign Key for Table 3
SalesOrderLine

Table 3
itemID - AutoNumber, Primary Key

Table 1 and Table 2 – connected by ‘Sales Order Line’ through one to many relationship
Table 2 and Table 3 - connected by BoxItemDataTable for many to many relationship

Here is where I need the help. How can I insert records using sql to both boxDataTable and itemDataTable at the same time. I like to do this so I can open a form based on this query and add additional records to these tables later. I have a number of records to be inserted to BoxDataTable.
Please see attachment to know relationships. I hope someone can help me. I desperately need some help.

 

[ted.martin]

I appreciate you said SQL but have you tried using the DAO.Recordset for adding records.

Typically the code could look like this

Dim db as DAO.Database
Dim rs1 as DAO.Recordset
Dim rs2 as DAO.Recordset

Set db = CurrentDb
Set rs1 = db.openrcordset("Table"1",dbopendynaset)
Set rs2 = db.openrecordset("Table2),dbopendynaset)


' Notes can use With and End With here instead of respecifyng the recordset each time

rs1.AddNew ' or Edit
rs2.AddNew ' or Edit

rs1!Field1 = ??
rs2!Field1 = ??
rs2!Field2 = ??
rs2!Field2 = ??

rs1.Update
rs2.Update

' Finally
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

 

[Maggi]

Thanks a lot. I will try it out now. But, How can I insert multiple records that I have on an array to this recordset?

 

[ted.martin]

Depends what you mean by multiple records.

You can surround the code between the AddNew [Edit] and Update with a Loop e.g. Do Until rs.eof, or use a For ... Next etc.

As regards the underlying table, I suggetsed the whole table name but you can use a SQL SELECT statement and then rs.FindFirst will take you to the appropriate starting point.

I always use the DAO recordset method when the SQL gets too complicated.

Good luck.