Access Field Creation Problem

[Bernie Hunt]

My script creates a new Access database, table and fields. The problem is
the fields are set for required entry. I need them to not require an entry
when a record is written.

The code currently is;

Dim catalog
Dim newTable

Set catalog = WScript.CreateObject("ADOX.Catalog")
Set newTable = WScript.CreateObject("ADOX.Table")

' create database ...
catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileName

' set database as active ...
catalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
& fileName & ";"

' add table and columns ...
newTable.Name = "DebtData"
With newTable.Columns
.Append "PatientID", adInteger
.Append "LastName", adVarWChar, 25
.Append "FirstName", adVarWChar, 15
.Append "MiddleInitial", adVarWChar, 1
.Append "Birthdate", adDate
End With
catalog.Tables.Append(newTable)

' clean up objects ...
Set catalog = Nothing
Set newTable = Nothing

Can anyone tell me what I need to add to make the fields as no required?

Thanks,
Bernie

[JHP]

Another alternative:

Option Explicit
On Error Resume Next

Dim objDBE, objADO
Const dbVersion10 = 1
Const dbVersion11 = 8
Const dbVersion20 = 16
Const dbVersion30 = 32
Const dbVersion40 = 64

Set objDBE = CreateObject("DAO.DBEngine.36")
objDBE.CreateDatabase "C:\temp.mdb", ";LANGID=0x0409;CP=1252;COUNTRY=0",
dbVersion40
Set objDBE = Nothing

Set objADO = CreateObject("ADODB.Connection")
objADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp.mdb")
objADO.Execute("Create Table Test(col_1 number, col_2 text(10))")
objADO.Close
Set objADO = Nothing

HTH

"Bernie Hunt" <bh...@optonline.net> wrote in message
news:e5G2WcOv...@TK2MSFTNGP15.phx.gbl...

[Bob Barrows [MVP]]

Bernie Hunt wrote:
> My script creates a new Access database, table and fields. The
> problem is the fields are set for required entry. I need them to not
> require an entry when a record is written.
>
> The code currently is;
>
> Dim catalog
> Dim newTable
>
> Set catalog = WScript.CreateObject("ADOX.Catalog")
> Set newTable = WScript.CreateObject("ADOX.Table")
>

Here are the Jet-specific column properties exposed by the Jet OLE DB
provider:
AutoIncrement

Default

Description

Fixed Length

Increment

Nullable

Seed

Jet OLEDB:Allow Zero Length

Jet OLEDB:AutoGenerate

Jet OLEDB:Column Validation Rule

Jet OLEDB:Column Validation Text

Jet OLEDB:Compressed UNICODE Strings

Jet OLEDB:Hyperlink

Jet OLEDB:IISAM Not Last Column

Jet OLEDB:One BLOB per Page

The two you want are Nullable and "Jet OLEDB:Allow Zero Length" (note: "Jet
... Length" is the name of the property - all of the words must be used when
reading/setting this property)

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bernie Hunt]

Bob, Sweet!!!!

Where did you find these? I searched all over the net and came up empty.

I'll try it out today.

Bernie

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:O9ZvTbUv...@TK2MSFTNGP10.phx.gbl...

[Bob Barrows [MVP]]

There used to be an article called "Migrating from DAO to ADO" on
msdn.microsoft.com. They have since removed it, but you can use the Wayback
Machine (www.archive.org)
to get it. Here's a link generated by Wayback:
http://web.archive.org/web/20020811230151/http://msdn.microsoft.com/library/en-us/dndao/html/daotoadoupdate.asp

The information I provided was found in the Appendix.

Bob Barrows

[Bernie Hunt]

I did some searching on the terms you gave me and found this;
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adoproperties.asp

Also, do you know how to set a field as indexed with no duplicates?

Thanks,
Bernie

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message

news:OC3FnVhv...@TK2MSFTNGP10.phx.gbl...