Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: Dynamic Table Create

    All,

    Have the code:
    Code:
    Sub Sav_Tmp()
        Dim DAYstm, DAYstr, EDate, EDTval, FldLen, FldStr, FldTyp, FType, MONstm, MONstr, MonNxt
        Dim RAT_Lvl, RAT_wir, RAT_xir, SDate, SDTval, SQLStm, SQLStr, WHRstr, YERstm, YERstr
        Dim dbs As dao.Database, t As TableDef, fldLoop As Field, prpLoop As Property
        ' Delete existing temp table and rebuild
        TableName = "tmpREPfnr"
        Set dbs = CurrentDb
        On Error Resume Next
        ReplaceTable = True
        s = dbs.TableDefs(TableName).Name 'verify existence
        If Err = 0 Then
            If MsgBox("The table " & TableName & " exists. Replace it?", vbYesNo, "Table exists") = vbNo Then
                Exit Sub
            End If
            DoCmd.DeleteObject acTable, TableName
        End If
        On Error Resume Next
        ' Create Table
        Set t = dbs.CreateTableDef(TableName)
        ' Add autoincrement field as primary key index
        PrimaryFld = "tmp_id"
        Set fld = t.CreateField(PrimaryFld, 4)
            fld.Attributes = 17 'autoincrement
            t.Fields.Append fld
        Dim Idx As INDEX
        Set Idx = t.CreateIndex("PrimaryKey")
            Idx.Fields.Append Idx.CreateField(PrimaryFld)
            Idx.Primary = True
            t.Indexes.Append Idx
        ' Create other fields
        FldLen = "9.2 9.2 9.2 9.2 9.2 9.2  11 150  20  50 255   1   1   0   5   1   0 100 9.2   1  11   1 9.2"
        FldStr = "ahr anl aml bhr bnl bml cdx cnm int nik pno pty stp ted tno typ wdt win wir wit wtx wty xar"
        FldTyp = "num num num num num num num txt txt txt txt txt txt dat txt txt dat txt num txt num txt num"
        
        ' ## HERE ## HERE ## HERE ## HERE ## HERE ## HERE ## HERE ## HERE ## HERE ## HERE ## HERE ##
        
        For n = 1 To words(FldStr)
            If word(FldTyp, n) = "dat" Then FType = "dbDate"
            If word(FldTyp, n) = "num" Then FType = "dbInteger"
            If word(FldTyp, n) = "txt" Then FType = "dbText"
            FldName = "tmp_" & word(FldStr, n)
            With t
                Set fld = .CreateField(FldName, FType)
    '                fld.Properties.Size = word(FldLen, n)
                .Fields.Append fld
            End With
        Next n
        
        dbs.TableDefs.Append t
        
    End Sub
    which put the first field into the table, but does not fill in the rest. Copied this direct from the HOWTOs, so what am I doing wrong?

    Oh yeah the "Word" function is from my rexx module attached.

    DBS4M
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Help

    All,

    Can I get some help?

    Yes I added the "FOR" loop, but other than parsing of the 3 strings the commands are working and no errors produced by the loop.

    Since all the HOWTOs I found always show "dbDate", "dbText", "dbInteger" and "dbMemo" but some of the HELP examples show it with numbers for the field type, which is right?

    The part that is working is using number for field type, but since most tutorials used the text for type, I was following them, but can not find a cross from the text types to the numeric, so do not know what values go in.

    Think this is where it is getting lost.

    Thanks!

    DBS4M

  3. #3
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Partial Fix

    All,

    Knew I was right. Took off the Quotes and worked without the length.

    Current code:
    Code:
            If word(FldTyp, n) = "dat" Then FType = dbDate
            If word(FldTyp, n) = "num" Then FType = dbInteger
            If word(FldTyp, n) = "txt" Then FType = dbText
            FldName = "tmp_" & word(FldStr, n)
            CURlen = word(FldLen, n)
            Set fld = t.CreateField(FldName, FType)
    '        Set fld = t.CreateField(FldName, FType, CURlen)
            t.Fields.Append fld
    Now just need to figure out the part to get the field lengths correct.

    Thanks!

    DBS4M

  4. #4
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Solved

    All,

    This fixed the length:
    Code:
            CURlen = Val(word(FldLen, n))
            Set fld = t.CreateField(FldName, FType, CURlen)
    Thanks!

    DBS4M
    Last edited by dbsupport4me; 07-22-09 at 12:32.

Posting Permissions

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