[thelist] MS Access CREATE TABLE

david.landy at somerfield.co.uk david.landy at somerfield.co.uk
Wed Feb 4 03:07:59 CST 2004


Ahhaa... that's better! 

This version adds NULL|NOT NULL and PRIMARY KEY constraints.

See ya later...

David


Function createTableSQL$(tbl$)
    Dim db As Database
    Set db = CurrentDb
    Dim tdf As TableDef
    Set tdf = db.TableDefs(tbl)
    Dim r$
    r = "CREATE TABLE " & tdf.NAME & "("
    Dim fld As Field
    For Each fld In tdf.fields
        r = r & "[" & fld.NAME & "] " & FieldType(fld)
        If FieldType(fld) = "TEXT" Then
            r = r & "(" & fld.Size & ") "
        End If
        If fld.Required Then
            r = r & " NOT NULL"
        Else
            r = r & " NULL"
        End If
        r = r & ", "
    Next
    r = Left$(r, Len(r) - 2)
    
    'Now add the primary key constraint
    If PKFields(tdf) > "" Then
        r = r & ", CONSTRAINT PrimaryKey PRIMARY KEY (" & PKFields(tdf) &
")"
    End If
    
    'Closing parenthesis
    r = r & ")"
    
    'Return value
    createTableSQL = r
End Function

Function FieldType$(fld As Field)
    Dim fldtype&
    fldtype = fld.Type
    Select Case fldtype
    Case dbDate, dbTime, dbTimeStamp
        FieldType = "DATETIME"
    Case dbMemo
        FieldType = "MEMO"
    Case dbByte
        FieldType = "BYTE"
    Case dbInteger
        FieldType = "INT"
    Case dbLong
        FieldType = "LONG"
    Case dbNumeric, dbDecimal, dbFloat
        FieldType = "FLOAT"
    Case dbSingle
        FieldType = "SINGLE"
    Case dbDouble
        FieldType = "DOUBLE"
    Case dbGUID
        FieldType = "GUID"
    Case dbBoolean
        FieldType = "BIT"
    Case dbCurrency
        FieldType = "CURRENCY"
    Case dbText
        FieldType = "TEXT"
    End Select
    If (fld.Attributes And dbAutoIncrField) Then
        FieldType = "AUTOINCREMENT"
    End If
End Function

Function PKFields$(tdf As TableDef)
    Dim idx As Index
    For Each idx In tdf.Indexes
        If idx.Primary Then
            Dim fld As Field
            For Each fld In idx.fields
                PKFields = PKFields & "[" & fld.NAME & "], "
            Next
            PKFields = Left(PKFields, Len(PKFields) - 2)
        End If
    Next
End Function

David Landy, IT Consultant
Business Intelligence
Somerfield/KwikSave
+44 (0) 117-301-8977
david.landy at somerfield.co.uk   


David Landy, IT Consultant
Business Intelligence
Somerfield/KwikSave
+44 (0) 117-301-8977
david.landy at somerfield.co.uk   
  
 
If you are not the intended recipient of this e-mail, please preserve the
confidentiality of it and advise the sender immediately of any error in
transmission. Any disclosure, copying, distribution or action taken, or
omitted to be taken, by an unauthorised recipient in reliance upon the
contents of this e-mail is prohibited. Somerfield cannot accept liability
for any damage which you may sustain as a result of software viruses so
please carry out your own virus checks before opening an attachment. In
replying to this e-mail you are granting the right for that reply to be
forwarded to any other individual within the business and also to be read by
others. Any views expressed by an individual within this message do not
necessarily reflect the views of Somerfield.  Somerfield reserves the right
to intercept, monitor and record communications for lawful business
purposes.


More information about the thelist mailing list