• Create New table from single table (2003 SP3)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Create New table from single table (2003 SP3)

    Author
    Topic
    WSkwvh
    AskWoody Lounger
    September 12, 2008 at 9:54 am #453903

    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

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      September 12, 2008 at 10:03 am #1125345

      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)

      Reply | Quote
      • WSkwvh
        AskWoody Lounger
        September 12, 2008 at 10:37 am #1125354

        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

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          September 12, 2008 at 11:21 am #1125359

          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 & “[” & rst!FieldName & “] ”
          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.

          Reply | Quote
    • WSjohnhutchison
      AskWoody Lounger
      September 12, 2008 at 11:02 am #1125358

      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.

      Reply | Quote
      • WSkwvh
        AskWoody Lounger
        September 12, 2008 at 11:22 am #1125360

        Hans,

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

        THANK YOU!

        Ken

        Reply | Quote
    Viewing 1 reply thread
    Reply To: Create New table from single table (2003 SP3)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.