[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