Set “Required=False” in VBScript to MS Access file

Question

I found a really nice example on how to take data from a classic ASP file and spit it out to a new MS Access file. My one problem is I need to set the "Required" property of a column to false so that if no data exists for that column, it won't crash. Googling gets me results with different syntax and it doesn't seem to work. If some can help me set the property with this example, I'd appreciate it.

dim provider : provider = "microsoft.jet.oledb.4.0"
dim db : db = "c:\Documents and Settings\*****\Desktop\foodb.mdb"
dim ds : ds = "provider=" & provider & "; data source=" & db

dim catalog:set catalog=createobject("adox.catalog")
catalog.create ds 'create db file

'const for column types
 const adInteger=3   'integer
 const adVarChar=202   'variable character

 dim new_table:set new_table=createobject("adox.table")
 new_table.Name="customer"
 new_table.columns.append "id", adInteger
 new_table.columns.append "surname", adVarChar

 new_table.keys.append "pk_cust_id", 1, "id" 'primary key

 catalog.Tables.Append new_table   'append table to DB

 'release resources
 set new_table=nothing
 set catalog=nothing

 'By this point, DB is now created

 'populate table

 dim conn: set conn=createobject("adodb.connection") 'create connection
 conn.open ds 'open connection

 sql="insert into customer (id, surname) values (5, 'smith')"
 conn.Execute sql

 'close connection and reclaim resources
  conn.close
 set conn=nothing

Answer 1

This VBScript does what I think you want, but does it more concisely IMO.

Option Explicit
Dim provider: provider = "microsoft.jet.oledb.4.0"
Dim db: db = "C:\Access\webforums\foodb.mdb"
Dim ds: ds = "provider=" & provider & "; data source=" & db
Dim Sql
Dim CreateTable
Dim catalog: Set catalog = CreateObject("adox.catalog")

catalog.create ds 'create db file
Set catalog = Nothing

'By this point, DB is now created
'create and populate table

Dim conn: Set conn = CreateObject("adodb.connection") 'create connection
conn.Open ds 'open connection
CreateTable = "CREATE TABLE customer (" & _
    "id COUNTER CONSTRAINT pk_cust_id PRIMARY KEY, " & _
    "surname TEXT(255));"
conn.Execute CreateTable
Sql = "insert into customer (id, surname) values (5, 'smith')"
conn.Execute Sql

'close connection and reclaim resources
conn.Close
Set conn = Nothing

With this method, the properties for the surname field include Required = No, which is what I think you want.

If you meant you want to insert Null into the id field, that can't happen with id as primary key. The primary key constraint will not allow Null values.