INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to combine 2 tables into 1

How to combine 2 tables into 1

(OP)
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?

RE: How to combine 2 tables into 1

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.

CODE -->

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.

RE: How to combine 2 tables into 1

(OP)
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?

RE: How to combine 2 tables into 1

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.

RE: How to combine 2 tables into 1

(OP)
Great. Thanks

RE: How to combine 2 tables into 1

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close