MS Access VBA - Passing datatype to CREATEFIELD method via variable

[ironfelix717]

Hi,



Coming across a coding issue i'm having. Not sure if this is possible. Mind you, there is a method to my madness with this approach, which I will explain.

I am using the CreateField method, where I am attempting to pass the field data type via textbox result.


Assume with example below, ftype1.value is a textbox value who's value is "dbText", and where it has been declared as a STRING.

fType = ftype1.Value
        Set f1 = t.CreateField(fname1 & k, fType)

The result is a data type conversion error. How might I pass this variable to the data type in the method?

Thank you for your time.

[ranman256]

There should be no reason that you need to create fields in code.
they should be manually made in design mode long before users need it.

[ironfelix717]

There should be no reason that you need to create fields in code.
they should be manually made in design mode long before users need it.

Gee, that's almost like saying "Excel data should only be entered via the workbook interface"

There are valid reasons to create fields in code. I'll wait for help.

[ssanfu]

You should check out DDL query code examples

[orange]

See this for more info and examples. or this for DDL example.
I would like to hear your rationale for creating a field(s) in code. I realize it can be done, and have created tables on the fly. But the more common method is to design tables through the "Designer interface".

[ironfelix717]

See this for more info and examples. or this for DDL example.
I would like to hear your rationale for creating a field(s) in code. I realize it can be done, and have created tables on the fly. But the more common method is to design tables through the "Designer interface".

To answer the question "why create fields in code" must first be addressed by answering "why create tables in code"

First valid reason would be a temp table. Second valid reason would be I am using a specialized drag module from Peter's software (Drag N Dropper) that's requires tables to be made dynamically to implement this functionality.

So why create fields in code, well because creating a table with DDL is undesireable. It's much easier to do loops on adding fields with createfield method, than at the initial table creation method.

Final reason this is valid is for this particular topic. This particular topic is about a quick table generator I am using to create large tables which will create fields very quickly for me for testing. The designer interface is cumbersome and I can program a sub to do it much faster for me. I don't know about you, but I prefer to not have to type 100 fields in the table generator at a time.

Regardless of any of these reasons, neither of them must be explained. I am hoping to understand how to simply pass this textbox value as a datatype to the createfield method.

Regards.

[orange]

This is code from the Allen Browne link I gave in previous post. It is a little dated, but suitable for datatype info.
You may get some ideas for values for datatype.

Public Function FieldTypeName(fld As DAO.Field)
    'Purpose: Converts the numeric results of DAO fieldtype to text.
    'Note:    fld.Type is Integer, but the constants are Long.
    Dim strReturn As String         'Name to return
    
    Select Case CLng(fld.Type)
        Case dbBoolean: strReturn = "Yes/No"            ' 1
        Case dbByte: strReturn = "Byte"                 ' 2
        Case dbInteger: strReturn = "Integer"           ' 3
        Case dbLong                                     ' 4
            If (fld.Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
        Case dbCurrency: strReturn = "Currency"         ' 5
        Case dbSingle: strReturn = "Single"             ' 6
        Case dbDouble: strReturn = "Double"             ' 7
        Case dbDate: strReturn = "Date/Time"            ' 8
        Case dbBinary: strReturn = "Binary"             ' 9 (no interface)
        Case dbText                                     '10
            If (fld.Attributes And dbFixedField) = 0& Then
                strReturn = "Text"
            Else
                strReturn = "Text (fixed width)"
            End If
        Case dbLongBinary: strReturn = "OLE Object"     '11
        Case dbMemo                                     '12
            If (fld.Attributes And dbHyperlinkField) = 0& Then
                strReturn = "Memo"
            Else
                strReturn = "Hyperlink"
            End If
        Case dbGUID: strReturn = "GUID"                 '15
        
        'Attached tables only: cannot create these in JET.
        Case dbBigInt: strReturn = "Big Integer"        '16
        Case dbVarBinary: strReturn = "VarBinary"       '17
        Case dbChar: strReturn = "Char"                 '18
        Case dbNumeric: strReturn = "Numeric"           '19
        Case dbDecimal: strReturn = "Decimal"           '20
        Case dbFloat: strReturn = "Float"               '21
        Case dbTime: strReturn = "Time"                 '22
        Case dbTimeStamp: strReturn = "Time Stamp"      '23
        
        'Constants for complex types don't work prior to Access 2007.
        Case 101&: strReturn = "Attachment"         'dbAttachment
        Case 102&: strReturn = "Complex Byte"       'dbComplexByte
        Case 103&: strReturn = "Complex Integer"    'dbComplexInteger
        Case 104&: strReturn = "Complex Long"       'dbComplexLong
        Case 105&: strReturn = "Complex Single"     'dbComplexSingle
        Case 106&: strReturn = "Complex Double"     'dbComplexDouble
        Case 107&: strReturn = "Complex GUID"       'dbComplexGUID
        Case 108&: strReturn = "Complex Decimal"    'dbComplexDecimal
        Case 109&: strReturn = "Complex Text"       'dbComplexText
        Case Else: strReturn = "Field type " & fld.Type & " unknown"
    End Select
    
    FieldTypeName = strReturn
End Function

I still think that a DDL ALTER statement may not be so difficult.

 ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

Just shows there is more than 1 way to accomplish adding a field to a table.
And as always, different strokes for different folks.

[ironfelix717]

Orange,

Awesome. I will look at this later on.

To explain more, the DDL language is extremely confusing to create a table using CREATETABLE method.

Let's assume an example where I must create 5 tables with the fields "name1", "name2", "name3".... Up to 20.

Using create table, this DDL string would be increasingly difficult to write, due to the weakest of the DDL nature. The delimters are spaces which is perhaps the worst type of delimeter... Anyway, the easiest method is to create the table and then add the fields in a for loop. The code is easier to read and cleaner.

I can explain more with code examples at my desktop.

Thank you for your resources.

[orange]

Hmmm... I think you have been negativly biased by someone or some thing or perhaps just misunderstanding.

You do not use the CREATETABLE method with the DDL.

You wouldn't build tables with field1, field2... fieldx. And I realize that was just an example.
Depending on what you actually have to build, you could have some template tables.

You can see from the code provided by Allen Browne that you could have a list of data types.

You could also have code along this concept to create a table with a number of fields.
-create variables to suit your needs
-create a base table
(SQL via VBA example --you could have a list of fields and data types and do it in 1 line
CurrentProject.Connection.Execute "CREATE TABLE JPersons (P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255);"

OR create some base code and ADD 1 field at a time
-select the number of fields required
- for each field select the data type and length if it applies
- run the DDL to alter the table ADD column with your parameters

CurrentProject.Connection.Execute "ALTER TABLE JPersons ADD COLUMN ZipCode text(5);"

Anyway just some thoughts to consider.

[ironfelix717]

orange,

thanks for your input.

I was under the impression that the CREATETABLE string of DDL type. Either way, my point here is that the CREATETABLE string is not the line of code where fields should be added. Rather, the "base table" is generated first with the barebone essentials (primary key and maybe 1 or 2 other CONSTANT fields).

The reason why creating fields in the CREATETABLE is undesirable to me is based on the following code:

For i = 1 To tableIt
    If cbKeyType.Value = "Pkey NORMAL" Then
     db.Execute "CREATE TABLE " & tName & i & "(ID TEXT PRIMARY KEY);"
    Else
    db.Execute "CREATE TABLE" & tName & i & "(ID AUTOINCREMENT PRIMARY KEY);"
    End If
    
Next i


For i = 1 To tableIt
    Set t = db.TableDefs(tName & i)
    
        For k = 1 To iter1.Value
        fType = ftype1.Value
        Set f1 = t.CreateField(fname1 & k, fType)
        Set f2 = t.CreateField("ProcVersion" & k, dbText)
        Set f3 = t.CreateField("Rate" & k, dbText)
        Set f4 = t.CreateField("Tooling" & k, dbText)
        Set f5 = t.CreateField("Resources" & k, dbText)
        Set f6 = t.CreateField("Burden" & k, dbText)
        Set f7 = t.CreateField("setup" & k, dbText)
        Set f8 = t.CreateField("Teardown" & k, dbText)
        Set f9 = t.CreateField("Instructions" & k, dbText)
        
        t.Fields.Append f1
        t.Fields.Append f2
        t.Fields.Append f3
        t.Fields.Append f4
        t.Fields.Append f5
        t.Fields.Append f6
        t.Fields.Append f7
        t.Fields.Append f8
        t.Fields.Append f9
        
        Next k
Next i

Trying to "kill two birds with one stone" in the CREATETABLE method just results in code that is difficult to read. Not to mention, I dont even think I could implement i & k integer variables within the string - with my coding skill it would end up in a lot of frustration.

This also applies to the "ALTER TABLE" DDL string. I don't like the language. The delimeters are spaces requiring one long string. Its just not desirable in my scenario where I am looping.

Regards,

[ironfelix717]

EDIT:

Anyway, we are getting off track... The point of this post was specifically, how to pass the datatype in this line:

fType = ftype1.Value
        Set f1 = t.CreateField(fname1 & k, fType)

Where:
fname1 is a textbox I will specify the BASE name of the field to be generated
ftype1 is a textbox I will specify the datatype i want,
k is how many fields I want to create with this name an iterate up to k iterations.
i is the number of tables I want to create and iterate up to i iterations.

To my original problem:

Passing a string "dbText" for variable fType to the CREATEFIELD method results in data type conversion error. So now I will try with just simply "TEXT"... and the result remains "data type conversion error"...

So I am trying to understand what I need to pass from a textbox to establish this field's data type.

Regards


-----------

EDIT:

I solved this by passing the datatype enumerator via integer variable. Setting fType to 10 allows the code to work. Must've just been my misunderstanding of using built-in enumerators.

Thanks

[ssanfu]

It looks like you have solved your problem. I'm going to post the db I came up with... just because.
I wasn't sure what some of your variables/names were, so I created my own.
The table are displayed after you do a C&R.... didn't take the time to have the database window refresh...

Good lick with your project....