Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: Creating a New Table based off of Existing Table in VBA (DAO)

    HI!

    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!

    ************************************************** *******
    Sub CreateNewTblS()
    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"
    Next tdfME
    Set tdfME = dbME.CreateTableDef("tblSD")
    With tdfME
    .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)
    End With
    dbME.TableDefs.Append tdfME
    Set idxME = tdfME.CreateIndex("PrimaryKey")
    With idxME
    .Fields.Append .CreateField("D_Num")
    .Primary = True
    End With
    tdfME.Indexes.Append idxME
    Set idxME = Nothing
    Set tdfME = Nothing

    ' Write data to output table
    Set rstME = dbME.OpenRecordset("tblSD")
    With rstME
    .AddNew
    .Update
    End With
    Set rstME = Nothing


    Exit_CreateNewTblS:
    Set dbME = Nothing
    Set tdfME = Nothing
    Exit Sub
    Err_CreateNewTblS:
    MsgBox Err.Description
    Resume Exit_CreateNewTblS
    End Sub

  2. #2
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    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:

    .AddNew
    .Update

    ...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?

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    BummerJeff:

    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!

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

  5. #5
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •