adColNullable?

[Mike]

Group

I think i need to set the column properties via. "adColNullable" below
is code i'm using to create a temporary table that is eventually deleted by
the "deactivate" event of a report. Anyway i need to plug some data in and
some of the entries will be "Null" however i found out that when a table is
created in this fashion it is not really 100% ready to use and I need to set
more properties that I don't know how to set. I want the temporary table
there because I can delete it via. code so my database will stay trim. Also
is there a way to make this code also add in the data using maybe the
rst.AddNew comand.

Regards
Mike
S un d m an

moc.1dtc @ namdnus. ekim
backwords and added spaces.


Sub codNewTabletblTemporaryMemoDataForSingleReport()

Dim cat As New ADOX.Catalog
Dim cnn As ADODB.Connection
Dim tbl As New ADOX.Table
Dim idx As ADOX.Index
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn
Set tbl = New ADOX.Table
With tbl
tbl.Name = "tblTemporaryMemoDataForSingleReport"
With .Columns
.Append "strEmpIDNum", adWChar, 3
.Append "strOriginatingDataBase", adWChar, 20
.Append "strPersonCommunicatedWith", adWChar, 50
.Append "strUserNetworkName", adWChar, 20
.Append "strCustomerOrVendor", adWChar, 50
.Append "strShopOrder", adWChar, 4
.Append "strSubject", adWChar, 50
.Append "memCommunicationsMemo", adLongVarWChar
.Append "dtmDate", adDate
.Append "dtmTime", adDate
.Append "dtmTimeMemoStarted", adDate
.Append "bolPending", adBoolean
End With
End With
cat.Tables.Append tbl
Set cat = Nothing
'Jumps to code that inserts the data in the new table.
codAddingDataToMemoDataForSingleReport
End Sub

 

[Tim Ferguson]

With tbl
tbl.Name = "tblTemporaryMemoDataForSingleReport"
With .Columns

Oh Jeez: just use SQL

CREATE TABLE tblTemporaryMemoDataForSingleReport
( strEmpIDNum VARCHAR 3
CONSTRAINT pk PRIMARY KEY,

strOriginatingDataBase VARCHAR(20)
NOT NULL,

strPersonCommunicatedWith VARCHAR(50)
NOT NULL,

strUserNetworkName VARCHAR(20)
CONSTRAINT idxUserUnique UNIQUE INDEX,

strCustomerOrVendor VARCHAR(50),

strShopOrder VARCHAR(4)
NOT NULL
CONSTRAINT fkShops FOREIGN KEY
REFERENCES ShopOrders(ShopOrderCode),

strSubject VARCHAR(50),
memCommunicationsMemo MEMO,
dtmDate DATETIME DEFAULT SYSTEMDATE(),
dtmTime DATETIME DEFAULT SYSTEMTIME(),
dtmTimeMemoStarted DATETIME,
bolPending BIT,
)


and so on. More details in CREATE TABLE in help, and don't forget to look
up the CONSTRAINT and DEFAULT clauses.

Hope that helps.

Tim F