Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Create New table from single table (2003 SP3)

    This is probably asked and answered, so I may be using incorrect search criteria.

    My current challenge is I have a table (tblField) with a two fields (fieldName, fieldType) and 92 records. I would like to create a New table using each of the 92 records as field names, using the fieldType as the field type in the new table (e.g. Text, Currency, Date) in the new table (tblNew). Is there an easy way to do this in VB instead of creating the table from scratch and copying and pasting from one to the other?

    Thanks in advance for your ideas.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create New table from single table (2003 SP3)

    What kind of field is fieldType? A text field? If so, which values occur in this text field?

    (Side note: a table with 92 fields is rarely a good idea, it usually means that the design hasn't been normalized)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create New table from single table (2003 SP3)

    Hans,

    Thanks for the quick response. WELCOME Back!

    Both fields in the tblField table are text fields.
    Example Records
    FieldName fieldType
    txtName Text
    txtAmount Currency
    txtDOB Date

    You are correct in your observation of not normalized. The project is replacing a Word document. Previously they opened up the Word Document, manually filled in the info and printed it out. Viola'. Since a third of the data already existed in Access, we recreated the Word doc as a form in Access using data from several tables ( sort of normalized) and gave them the other controls to address those not in Access.

    Original spec called for populating the form with data from the existing tables (as read only). Then the user enters data into about 60 fields manually, then prints the form for approval and signature. That worked great. Now they have asked to save a snapshot of the data in case they want to go back and modify the data and reprint.

    My plan was to create a flat table to store the data, since it will not be queried, or analyzed. And add the ability to delete those older than a few weeks.

    I guess was just too lazy to take the time to normalize the new data. However, once I have the flat table created, I can normalize the data. I just don't want to have to create the flat table manually if I can avoid it.

    Does that make sense?

    Thanks for getting back to me.

    Ken

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Create New table from single table (2003 SP3)

    This won't set the field types, but if you export to Excel, Transpose in Excel, then import back to a new table, you will have 92 fields.
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create New table from single table (2003 SP3)

    The following code requires that you have a reference (in Tools | References in the Visual Basic Editor) to the Microsoft DAO 3.6 Object Library.
    I added a field fieldOrder that specifies the order in which the fields have to be added. If you don't use that (or an AutoNumber field), the fields might not be in the order that you intended.
    The code opens a recordset on tblFields, then builds the SQL for a Data Definition query by looping through the records of this recordset, and finally executes it.
    You can add additional data types, and add a field fieldSize to tblFields to specify the length of text fields, for example.

    Sub CreateATable()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    strSQL = "SELECT fieldName, fieldType FROM tblFields ORDER BY fieldOrder"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
    strSQL = "CREATE TABLE tblNew ("
    Do While Not rst.EOF
    strSQL = strSQL & "<!t>[" & rst!FieldName & "]<!/t> "
    Select Case rst!fieldType
    Case "Text"
    strSQL = strSQL & "CHAR, "
    Case "Currency"
    strSQL = strSQL & "MONEY, "
    Case "Date"
    strSQL = strSQL & "DATETIME, "
    Case "Number"
    strSQL = strSQL & "INTEGER, "
    Case Else
    MsgBox "Type " & rst!fieldType & " was not foreseen...", vbInformation
    GoTo ExitHandler
    End Select
    rst.MoveNext
    Loop
    strSQL = Left(strSQL, Len(strSQL) - 2)
    strSQL = strSQL & ")"
    dbs.Execute strSQL, dbFailOnError

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    See attached sample database.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create New table from single table (2003 SP3)

    Hans,

    Thanks. I was not aware of the Transpose function in Excel. That worked fine.

    THANK YOU!

    Ken

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create New table from single table (2003 SP3)

    That was John Hutchison's reply!

Posting Permissions

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