Create new table for Pervasive database with Visual Basic 6.0

posted on 29 Aug 2008 11:35 by computertips in ProgrammingTips

' Pervasive Database
Private Sub My_CREATETABLE()
    ' ITEMSTOCK
    sSQL = "CREATE TABLE """ & "ITEMSTOCK" & """(" & Chr(13)   ' table name
    sSQL = sSQL & Chr(34) & "DETAILNUM" & Chr(34) & " SMALLINT NOT NULL," & Chr(13)
    sSQL = sSQL & Chr(34) & "DOCTYPE" & Chr(34) & " SMALLINT," & Chr(13)
    sSQL = sSQL & Chr(34) & "DOCNUM" & Chr(34) & " CHAR(22)," & Chr(13)
    sSQL = sSQL & Chr(34) & "ITEMCODE" & Chr(34) & " CHAR(24)," & Chr(13)
    sSQL = sSQL & Chr(34) & "AUDTDATE" & Chr(34) & " DECIMAL(19,0)," & Chr(13)
    sSQL = sSQL & Chr(34) & "DAY" & Chr(34) & " DECIMAL(19,0)," & Chr(13)
    sSQL = sSQL & Chr(34) & "MONTH" & Chr(34) & " DECIMAL(19,0)," & Chr(13)
    sSQL = sSQL & Chr(34) & "YEAR" & Chr(34) & " DECIMAL(19,0)," & Chr(13)
    sSQL = sSQL & Chr(34) & "VENDOR" & Chr(34) & " CHAR(60)," & Chr(13)
    sSQL = sSQL & Chr(34) & "CUSTOMER" & Chr(34) & " CHAR(60)," & Chr(13)
    sSQL = sSQL & Chr(34) & "UNITCOSTIN" & Chr(34) & " DECIMAL(19,4)," & Chr(13)
    sSQL = sSQL & Chr(34) & "UNITCOSTOUT" & Chr(34) & " DECIMAL(19,4)," & Chr(13)
    sSQL = sSQL & Chr(34) & "QUANTITYIN" & Chr(34) & " DECIMAL(19,4)," & Chr(13)
    sSQL = sSQL & Chr(34) & "QUANTITYOUT" & Chr(34) & " DECIMAL(19,4)," & Chr(13)
    sSQL = sSQL & Chr(34) & "QTYBALANCE" & Chr(34) & " DECIMAL(19,4)," & Chr(13)
    sSQL = sSQL & Chr(34) & "QTYOPENINGBALANCE" & Chr(34) & " DECIMAL(19,4)," & Chr(13)
    sSQL = sSQL & Chr(34) & "REMARK" & Chr(34) & " CHAR(60)," & Chr(13)
    sSQL = sSQL & "PRIMARY KEY (" & Chr(34) & "DETAILNUM" & Chr(34) & ")" & Chr(13)
    sSQL = sSQL & ");" & Chr(13)
    sSQL = sSQL & "CREATE UNIQUE NOT MODIFIABLE INDEX " & Chr(34) & "PK_DETAILNUM" & Chr(34) & _
    " ON " & Chr(34) & "ITEMSTOCK" & Chr(34) & "(" & Chr(34) & "DETAILNUM" & Chr(34) & ");"