How to combine 2 tables into 1 1

[FancyPrairie]

I have 2 access tables that I want to combine into 1 new table. There are no duplicate field names that I need to worry about. I know I can create a make table query that will combine the 2 together. The problem with the make table query is that it does not copy the definition of each field, the caption property, nor the lookup properties of each field.

How do I include those?

 

[MajP]

Not sure how to do the lookup properties, I will need to look at that. But for description and caption (and other possible properties) you could add them back in after. You would make your combined table then read the properties from the original table and write to the new destination table.

Field properties like other access properties are really weird. There is not a Description property or Caption property. But when you add a description or property then that property is created as a member of the properties collection and the value is added.

So as you see in the code below. I look at the destination field and first create the Property description or caption. Then add that property to the fields properties collection. Then add the value.

Public Sub SetProperties()
  Dim strCaption As String
  Dim strDescription As String
  Dim db As DAO.Database
  Dim fld As DAO.Field
  Dim tdfRead As DAO.tabledef
  Dim tdfWrite As DAO.tabledef
  Dim prp As DAO.Property
  
  strCaption = "The Caption"
  strDescription = "The Description"
  Set db = CurrentDb
  Set tdfRead = db.TableDefs("Data")
  Set tdfWrite = db.TableDefs("Data2")
  For Each fld In tdfRead.Fields
    For Each prp In fld.Properties
      If prp.Name = "Description" Or prp.Name = "Caption" Then
        Debug.Print prp.Name
        AddPropertyToDestination prp.Name, prp.Value, fld.Name, tdfWrite
      End If
    Next prp
  Next fld
End Sub

Public Sub AddPropertyToDestination(propName As String, propvalue As String, fldName As String, tdf As DAO.tabledef)
  Const ErrPropExists = 3367
  Dim fld As DAO.Field
  Dim prp As DAO.Property
  
  On Error GoTo errLbl
  'Find matching field in destination table
  For Each fld In tdf.Fields
    If fld.Name = fldName Then
       Set fld = tdf.Fields(fldName)
       'assume only creating text properties
       Set prp = fld.CreateProperty(propName, dbText, propvalue)
       fld.Properties.Append prp
       Exit For
     End If
  Next fld
  Exit Sub
errLbl:
  If Err.Number = ErrPropExists Then
    Exit Sub
  Else
    MsgBox Err.Number & Err.Description
  End If
End Sub

I am assuming lookups are similar but more involved.

 

[FancyPrairie]

Very good! Appreciate you taking the time to put that together. My next step is to export the table to a SQL database. Is there a way to update the description property of each field within the SQL table with the description in the Access table?

 

[MajP]

I will play with it some more if I get time. I think I have a strategy to do the lookups. It is basically the same but there are a lot more properties to update related to the lookup. I do not do much work in SQL Server so cannot answer that.

 

[FancyPrairie]

Great. Thanks

 

[FancyPrairie]

MajP,

I've been thinking and I can do the lookups manually. You don't need to spend anymore time on it. I appreciate what you have done.