create ADOX table

[Shaun]

I have found some source code that enables me to create a
an "ADOX" table in a given desternation folder, but my
problem is this that all of the fields "Required Value" is
set to True. I need the primary key set to "Required" but
no others. Any help please?

'This is the table

Public Function tblCompanyDetails()
On Error GoTo Err_tblCompanyDetails

Dim tbl As New Table
Dim IDx As New ADOX.Index
Dim cat As New ADOX.Catalog

Set cat = New ADOX.Catalog

'Open the catalog.
' Open the Catalog.
cat.ActiveConnection = _
"ProvIDer=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\SalonAccess\Back.mdb;"

Set tbl = New ADOX.Table
With tbl
tbl.Name = "tblCompanyDetails"
Set .ParentCatalog = cat

With .Columns
' Define the table and append it to the catalog

.Append "fldCompanyID", adInteger
.Item("fldCompanyID").Properties
("AutoIncrement") = True
.Append "fldComName", adVarWChar, 50
.Append "fldComAddress1", adWChar, 50
.Append "fldComAddress2", adWChar, 50
.Append "fldComAddress3", adWChar, 50
.Append "fldComTown", adVarWChar, 50
.Append "fldComCounty", adVarWChar, 50
.Append "fldComPostalCode", adVarWChar, 50
.Append "fldComTelephoneNumber", adWChar, 20
.Append "fldComVatNumber", adWChar, 20
.Append "fldComDateCreated", adDate

cat.Tables.Append tbl

End With
End With

'Create Primary Key & Index
With IDx

.Name = "PrimaryKey"
.Columns.Append "fldCompanyID"
.Columns("fldCompanyID").SortOrder = adSortDescending
.PrimaryKey = True


End With
'Stop


' Append the index to the table
tbl.Indexes.Append IDx
Set catDB = Nothing


Exit_tblCompanyDetails:
Exit Function

Err_tblCompanyDetails:

'Error Message vbYesNo
strMsg = "A table called 'tblCompantDetailsDetails'
already exists!"
intStyle = vbYesNo
strTitle = "Set Up"
Response = MsgBox(strMsg, intStyle, strTitle)

If Response = vbYes Then
MsgBox "tblCompanyDetails"
Else
DoCmd.CancelEvent
End If
'End Error Message

Resume Exit_tblCompanyDetails

End Function

Many Thanks Shaun

 

[Tim Ferguson]

I have found some source code that enables me to create a
an "ADOX" table in a given desternation folder,

Why? DDL will do this much more simply, more flexibly and you will be able
to port the code to future platorms. Two lines of code do the entire table
build:-

strSQL = "CREATE TABLE Companies ( " & _
" CompanyID INTEGER IDENTITY(1,1) NOT NULL " & _
" CONSTRAINT pk PRIMARY KEY, " & _
" EnglishName VARCHAR(50) NULL, " & _
" Address0 VARCHAR(50) NULL, " & _
" Address1 VARCHAR(50) NULL, " & _
" Address2 VARCHAR(50) NULL, " & _
" Address3 VARCHAR(50) NULL, " & _
" PostCode VARCHAR(8) NULL, " & _
" Phone VARCHAR(20) NULL, " & _
" VatCode VARCHAR(20) NULL, " & _
" CreateDate DATETIME NOT NULL " & _
" DEFAULT @@SYSTEMTIME(), " & _
" )"

conn.Execute strSQL


HTH


Tim F