'''
a script that illustrates how to create a SQL Express database/table via ADOX
-- see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscadoxfundamentals.asp
'''

import os
import win32com.client

catalog   = win32com.client.Dispatch('ADOX.Catalog') 
database  = 'foobar'
host      = os.environ['COMPUTERNAME']
server    = '%s%sSQLEXPRESS' % (host,os.sep)
entry     = 'Provider=SQLNCLI;Server=%s;Database=%s;Trusted_Connection=yes;' % (server, database)
try:
   catalog.Create(entry)
   table                = win32com.client.Dispatch('ADOX.Table') 
   table.Name           = 'spam'
   column               = win32com.client.Dispatch('ADOX.Column')
   column.Name          = 'CustName'
   column.Type          = win32com.client.constants.adVarChar
   column.DefinedSize   = 75 # bytes
   column.Attributes    = False # column may not contain null values
   column.ParentCatalog = catalog
   table.Columns.Append(column)
   column               = win32com.client.Dispatch('ADOX.Column')
   column.Name          = 'CustId'
   column.Type          = win32com.client.constants.adInteger
   column.Attributes    = False # column may not contain null values
   column.ParentCatalog = catalog # must proceed the following Properties assignment -- I have no idea why
   column.Properties('Autoincrement').Value = win32com.client.pywintypes.TRUE
   table.Columns.Append(column)
   catalog.Tables.Append(table)
   key      = win32com.client.Dispatch('ADOX.Key')
   key.Name = '%s_primary_key' % column.Name
   key.Type = win32com.client.constants.adKeyPrimary
   key.RelatedTable = table.Name
   key.Columns.Append(column.Name)
   ######key.Columns.Append(column)
   table.Keys.Append(key)
finally:
   del catalog