Unanswered: Creating a New Table based off of Existing Table in VBA (DAO)
I am trying to create a new table ("tblSD") that has columns X, Y, and Z from "tblS" in addition to new columns that I created.
The issue is that I want to ensure that everytime a record is created in "tblS" it is reflected in "tblSD". I think I'm stuck at the part in Navy Blue color.
Here's the code I have so far...can anyone help me with the rest? Thanks!
On Error GoTo Err_CreateNewTblS
Dim dbME As DAO.Database, A As Long
Dim tdfME As DAO.TableDef, idxME As DAO.Index, rstME As DAO.Recordset
Set dbME = CurrentDb
' Create a new table
For Each tdfME In dbME.TableDefs
If tdfME.Name = "tblSD" Then DoCmd.DeleteObject acTable, "tblSD"
Set tdfME = dbME.CreateTableDef("tblSD")
.Fields.Append .CreateField("D_Num", dbText, 50)
.Fields.Append .CreateField("Category", dbText, 50)
.Fields.Append .CreateField("Conflicting", dbText, 50)
.Fields.Append .CreateField("Criteria6", dbText, 50)
.Fields.Append .CreateField("Criteria7", dbText, 50)
.Fields.Append .CreateField("Criteria8", dbText, 50)
Set idxME = tdfME.CreateIndex("PrimaryKey")
.Primary = True
Set idxME = Nothing
Set tdfME = Nothing
' Write data to output table
Set rstME = dbME.OpenRecordset("tblSD")
Set rstME = Nothing
Set dbME = Nothing
Set tdfME = Nothing
I ran your code and I'm a bit confused as to what you are trying to do. From what I see the code deletes "tblSD", creates a new table called "tblSD" and then tries to update the blank record you added:
...which is why it errs out at your highlighted portion: D_Num is the PK, and a PK cannot have a Null value. You need to add some type of value in the D_Num field before trying to update...
.Fields("D_Num") = "value"
But if you're only trying to ensure that a record is duplicated in another table when added to one, why have two tables? Just have one and put Yes/No flags in it to mark different types of records and use that field in any queries to separate out the fields/records that would represent either "tblS" or "tblSD". Does that make sense?
Sorry, I probably didn't explain myself. We have a table - tblS that can't be modified because we need it to remain in syncronization with a database that we have no control over. However, we need to add additional fields to capture additional data that does not exist in the original table.
That is why I was thinking I could link the two tables together so that if tblS ever gets updated, tblSD would as well. However, you can't modify properties of a linked table. So I started to think...is there a way to have the two tables linked but where I could add additional fields to tblSD and whenever tblS is updated (at least for the 3 columns that I am concerned about) then it would update but it would leave the rest of the columns intact.
I am very new at this so any suggestions would be great. Thanks!
In tblS is there a primary key? A unique identifier for each record? If so then you don't need to duplicate the data that is already in tblS. I would just put a foreign key in tblSD and then add the additional columns you need. Then you don't have to recreate tblSD each time, just add a new record that is associated with the new record that was added in tblS.
DCKunkle's suggestion would be best, I think. You could come up with a Left Join query that compares the two tables, but only gives you records that are in tblS and not tblSD, then append those records to tblSD and fill in your extra fields. You'd have to come up with some kind of process that can run this query any time a new record is added to tblS - without knowing your setup, I'm not sure how you would do it.